sqlserver触发器

Love The Way You Lie 2022-07-14 10:12 208阅读 0赞

触发器是一种特殊类型的存储过程。触发器可包含复杂的T-SQL语句。触发器不能通过名称被直接调用，也不允许设置参数。它是建立在触发事件上的。

触发器可以强制执行一定的业务规则，以保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加功能。

触发器的分类： DML、 DDL、 登录触发器

创建触发器需要指定的选项：

1.触发器的名称。

2.在其上定义触发器的表。

3.触发器将何时激发。

4.激活触发器的数据修改语句。

5.执行触发操作的编程语句。

CREATE TRIGGER语句基本语法格式如下：

CREATE TRIGGER     触发器名称

ON \{表名 | 视图名\}

\[with encryption\]

\{ \{FOR | AFTER | INSTEAD OF\}

\{\[DELETE\] \[,\]\[INSERT\] \[,\] \[UPDATE\]\}

sql\_statement \[…n \]

其中：

AFTER

指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，则 AFTER 是默认设置。

INSTEAD OF

指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。

例如：

![复制代码][copycode.gif]

1 /*在student表上创建触发器，
     2 在用户插入、修改和删除记录时，都会自动显示表中的内容：*/
     3 
     4 use test
     5 go
     6 
     7 create trigger trig_1 on student
     8 after insert,delete,update
     9 as 
    10 begin
    11     set nocount on
    12     select * from student
    13 end
    14 
    15 insert student(sno) values(5)
    16 
    17 delete student where sno=5
    18 
    19 
    20 exec sp_helptext trig_1    --查看触发器内容 
    21 exec sp_helptrigger student   --查看表上的触发器的属性
    22 select * from sysobjects where xtype='TR'  --查看数据库中已有的触发器
    23 
    24 
    25 drop trigger trig_1

![复制代码][copycode.gif]

inserted表和deleted表

触发器执行的时候，产生两个临时表：inserted表和deleted表。它们的结构和所在的表的结构相同，可使用这两个表测试某些数据修改的效果和设置触发器操作的条件，但不能对表中的数据进行更改。

deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。

inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在插入和更新时，新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

在对具有触发器的表（触发器表）进行操作时，有：

执行INSERT操作，插入到触发器表中的新行被插入到inserted表中。

执行DELETE操作，从触发器表中删除的行被插入到deleted表中。

执行UPDATE操作，先从触发器表中删除旧行，然后再插入新行。删除的旧行插入到deleted表中；更改后的新行被插入到inserted 表中。

使用DML触发器

1. INSERT和UPDATE触发器

当向表中插入或者更新记录时，INSERT或者UPDATE触发器被激活。一般情况下，这两种触发器常用来检查插入或者修改后的数据是否满足要求。

INSERT触发器被触发时，新的记录增加到触发器的对应表中，并且同时也添加到一个inserted表中。

修改一个记录等于插入了一个新的记录并且删除一个旧的记录。当在一个有UPDATE触发器的表中修改记录时，表中原来的记录被移动到deleted表中，修改过的记录插入到了插入表中，触发器可以参考deleted表和inserted表以及被修改的表，以确定如何完成数据库操作。

2. DELETE触发器

DELETE触发器通常用于下面的情况：

防止那些确实要删除，但是可能会引起数据一致性问题的情况，一般是用于那些用作其他表的外部键记录。

用于级联删除操作。

例如：

![复制代码][copycode.gif]

1 /*例：下例说明inserted表和deleted表的作用*/
     2 
     3 if exists(select name from sysobjects where name='trig_2' and type='TR')
     4     drop trigger trig_2
     5 go
     6 
     7 create trigger trig_2 
     8 on student
     9 after update                         --update触发器
    10 as 
    11     print 'inserted表'
    12     select * from inserted
    13     print 'deleted表'
    14     select * from deleted
    15 go
    16 set nocount on
    17 update student set sname='关二' where sno=2
    18 --drop trigger trig_2
    19 
    20 
    21 create trigger trig_3 
    22 on student
    23 after insert                         --insert触发器
    24 as 
    25     print 'inserted表'
    26     select * from inserted
    27     print 'deleted表'
    28     select * from deleted
    29 go
    30 insert student values(100,'刘一百','男',25)
    31 drop trigger trig_3
    32 
    33 
    34 create trigger trig_4 
    35 on student
    36 after delete                         --delete触发器
    37 as 
    38     print 'inserted表'
    39     select * from inserted
    40     print 'deleted表'
    41     select * from deleted
    42 go
    43 delete student where sno=100
    44 drop trigger trig_4

![复制代码][copycode.gif]

修改触发器

语法格式：

ALTER TRIGGER trigger\_name

ON ( table | view )

\{ ( FOR | AFTER | INSTEAD OF ) \}

\{ \[ DELETE \] \[ , \] \[ INSERT \] \[ , \] \[ UPDATE \] \}

sql\_statement \[…n \]

DDL触发器使用

例如：

![复制代码][copycode.gif]

1 /*DDL触发器*/
     2 
     3 /*在test数据库上创建一个DDL触发器safe，
     4 用来防止数据库中的任一表被修改或删除。*/
     5 
     6 create trigger safetest
     7 on database                         --数据库DDL触发器
     8 after drop_table,alter_table
     9 as
    10 begin
    11     raiserror('不能修改表结构',16,2)
    12     rollback
    13 end
    14 go
    15  
    16 --执行以下程序，观察结果
    17 alter table student add nation char(10)
    18 
    19 disable trigger safetest on database
    20 drop trigger safetest on database
    21 ---------------------------------------------------------
    22 
    23 
    24 /*在服务器上创建一个DDL触发器tablecreat，
    25 用来防止在服务器上创建数据库*/
    26 
    27 create trigger trig_last
    28 on all server
    29 after create_database
    30 as
    31 begin
    32     raiserror('不能创建新的数据库',16,2)
    33     rollback
    34 end
    35 go
    36 
    37 --执行以下程序，观察结果
    38 create database test_trig
    39 
    40 disable trigger trig_last on all server
    41 drop trigger trig_last on all server

![复制代码][copycode.gif]

删除触发器

使用SQL Server Management Studio删除触发器

使用DROP TRIGGER语句来删除触发器。其语法格式如下：

DROP TRIGGER \{ trigger \} \[ , …n \]

触发器禁用和启用

例如：

![复制代码][copycode.gif]

1 /*触发器禁用和启用*/
     2 
     3 /*禁用sc表上的触发器trig_g。*/
     4 alter table sc disable trigger trig_g
     5 disable trigger trig_g on sc
     6 go
     7 
     8 /*启用sc表上的触发器trig_g。*/
     9 alter table sc enable trigger trig_g
    10 enable trigger trig_g on sc
    11 go
    12 
    13 --执行以下程序，观察结果
    14 insert into sc values(1,100,1,-50)
    15 select * from sc

![复制代码][copycode.gif]

触发器具体应用

例如：

![复制代码][copycode.gif]

1 /*具体应用*/
     2 
     3 /*创建触发器trig3，
     4 当删除student表中的学生记录时，
     5 应该同时删除sc表中对应的记录*/
     6 
     7 create trigger trig_5
     8 on student
     9 for delete
    10 as 
    11  delete sc
    12  where sc.sno in(select sno from deleted)
    13 go
    14 
    15 select * from student
    16 select * from sc
    17 go
    18 
    19 /*exec sp_help score   --查看其中外键
    20 
    21 alter table score         --删除外键
    22 drop CONSTRAINT FK_score_course
    23 
    24 alter table score
    25 drop CONSTRAINT FK_score_student*/
    26 
    27 
    28 delete student where sno=3
    29 go
    30 
    31 select * from student
    32 select * from sc 
    33 -------------------------------------------------------------
    34 
    35 
    36 create trigger trig_g 
    37 on sc
    38 after insert,update
    39 as
    40 begin
    41     declare @g int
    42     select @g=grade from inserted
    43     if @g<0
    44     begin
    45          select '成绩必须>=0'
    46          rollback
    47     end
    48 end
    49 go
    50 
    51 --执行以下程序，观察结果
    52 insert into sc values(1,10,1,50)
    53 select * from sc
    54 
    55 
    56 /*例: 建立一个修改触发器trigno，
    57 该触发器防止用户修改表student的学号*/
    58 
    59 create trigger trigno
    60 on student
    61 after update
    62 as
    63 if update(sno)
    64     begin
    65         raiserror('不能修改学号',16,2)
    66         rollback
    67     end
    68 go
    69 
    70 --执行以下程序，观察结果
    71 update student set sno='2' where sno='1'
    72 select * from student
    73 ------------------------------------------------------
    74 
    75 
    76 /*INSTEAD OF触发器*/
    77 
    78 /*例：在student表上创建一个INSTEAD OF触发器trig_6，
    79 当用户插入数据时注意观察触发器的执行。*/
    80 
    81 create trigger trig_6
    82 on student
    83 instead of insert
    84 as
    85     select * from student
    86 go
    87 
    88 --执行以下程序，观察结果
    89 insert into student(sno,sname) values('300','白扯')

![复制代码][copycode.gif]

转自：

http://www.cnblogs.com/z941030/p/5240446.html

参考：http://www.cnblogs.com/selene/p/4493311.html

[copycode.gif]: /images/20220714/249a83905475420b954cc04a1617c1f7.png