sql server 触发器

/*在student表上创建触发器，
在用户插入、修改和删除记录时，都会自动显示表中的内容：*/

use test
go

create trigger trig_1 on student
after insert,delete,update
as 
begin
    set nocount on
    select * from student
end

insert student(sno) values(5)

delete student where sno=5


exec sp_helptext trig_1    --查看触发器内容 
exec sp_helptrigger student   --查看表上的触发器的属性
select * from sysobjects where xtype='TR'  --查看数据库中已有的触发器


drop trigger trig_1

inserted表和deleted表

/*例：下例说明inserted表和deleted表的作用*/

if exists(select name from sysobjects where name='trig_2' and type='TR')
    drop trigger trig_2
go

create trigger trig_2 
on student
after update                         --update触发器
as 
    print 'inserted表'
    select * from inserted
    print 'deleted表'
    select * from deleted
go
set nocount on
update student set sname='关二' where sno=2
--drop trigger trig_2


create trigger trig_3 
on student
after insert                         --insert触发器
as 
    print 'inserted表'
    select * from inserted
    print 'deleted表'
    select * from deleted
go
insert student values(100,'刘一百','男',25)
drop trigger trig_3


create trigger trig_4 
on student
after delete                         --delete触发器
as 
    print 'inserted表'
    select * from inserted
    print 'deleted表'
    select * from deleted
go
delete student where sno=100
drop trigger trig_4

/*DDL触发器*/

/*在test数据库上创建一个DDL触发器safe，
用来防止数据库中的任一表被修改或删除。*/

create trigger safetest
on database                         --数据库DDL触发器
after drop_table,alter_table
as
begin
    raiserror('不能修改表结构',16,2)
    rollback
end
go
 
--执行以下程序，观察结果
alter table student add nation char(10)

disable trigger safetest on database
drop trigger safetest on database
---------------------------------------------------------


/*在服务器上创建一个DDL触发器tablecreat，
用来防止在服务器上创建数据库*/

create trigger trig_last
on all server
after create_database
as
begin
    raiserror('不能创建新的数据库',16,2)
    rollback
end
go

--执行以下程序，观察结果
create database test_trig

disable trigger trig_last on all server
drop trigger trig_last on all server

删除触发器

DROP TRIGGER { trigger } [ , …n ]

触发器禁用和启用

例如：

/*触发器禁用和启用*/

/*禁用sc表上的触发器trig_g。*/
alter table sc disable trigger trig_g
disable trigger trig_g on sc
go

/*启用sc表上的触发器trig_g。*/
alter table sc enable trigger trig_g
enable trigger trig_g on sc
go

--执行以下程序，观察结果
insert into sc values(1,100,1,-50)
select * from sc

触发器具体应用

例如：

/*具体应用*/

/*创建触发器trig3，
当删除student表中的学生记录时，
应该同时删除sc表中对应的记录*/

create trigger trig_5
on student
for delete
as 
 delete sc
 where sc.sno in(select sno from deleted)
go

select * from student
select * from sc
go

/*exec sp_help score   --查看其中外键

alter table score         --删除外键
drop CONSTRAINT FK_score_course

alter table score
drop CONSTRAINT FK_score_student*/


delete student where sno=3
go

select * from student
select * from sc 
-------------------------------------------------------------


create trigger trig_g 
on sc
after insert,update
as
begin
    declare @g int
    select @g=grade from inserted
    if @g<0
    begin
         select '成绩必须>=0'
         rollback
    end
end
go

--执行以下程序，观察结果
insert into sc values(1,10,1,50)
select * from sc


/*例: 建立一个修改触发器trigno，
该触发器防止用户修改表student的学号*/

create trigger trigno
on student
after update
as
if update(sno)
    begin
        raiserror('不能修改学号',16,2)
        rollback
    end
go

--执行以下程序，观察结果
update student set sno='2' where sno='1'
select * from student
------------------------------------------------------


/*INSTEAD OF触发器*/

/*例：在student表上创建一个INSTEAD OF触发器trig_6，
当用户插入数据时注意观察触发器的执行。*/

create trigger trig_6
on student
instead of insert
as
    select * from student
go

--执行以下程序，观察结果
insert into student(sno,sname) values('300','白扯')

最后

以上就是小巧哑铃为你收集整理的sql server 触发器的全部内容，希望文章能够帮你解决sql server 触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。