sqlserver触发器

发布时间：2020-12-12 13:51:24 所属栏目：MsSql教程来源：网络整理

导读：触发器是一种特殊类型的存储过程。触发器可包含复杂的T-SQL语句。触发器不能通过名称被直接调用，也不允许设置参数。它是建立在触发事件上的。 ? 触发器可以强制执行一定的业务规则，以保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加功能。

触发器是一种特殊类型的存储过程。触发器可包含复杂的T-SQL语句。触发器不能通过名称被直接调用，也不允许设置参数。它是建立在触发事件上的。 ? 触发器可以强制执行一定的业务规则，以保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加功能。 ? 触发器的分类：?DML、?DDL、?登录触发器 ? ? 创建触发器需要指定的选项： ? ?1.触发器的名称。 ?2.在其上定义触发器的表。 ?3.触发器将何时激发。 ?4.激活触发器的数据修改语句。 ?5.执行触发操作的编程语句。 ? ? CREATE TRIGGER语句基本语法格式如下： ?

CREATE TRIGGER ? ? 触发器名称

ON {表名 | 视图名}

[with encryption]

{

?? { {FOR | AFTER | INSTEAD OF}

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

?? AS

?? sql_statement […n ]

其中：

AFTER

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

INSTEAD OF

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

例如：

复制代码

 1 /*在student表上创建触发器，
 2 在用户插入、修改和删除记录时，都会自动显示表中的内容：*/
 3 
 4 use test
 5 go
 6 
 7 create trigger trig_1 on student
 8 after insert,delete,255); line-height:1.5!important">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 from sysobjects where xtype='TR'  查看数据库中已有的触发器
23 
24 
25 drop trigger trig_1

复制代码

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触发器通常用于下面的情况：防止那些确实要删除，但是可能会引起数据一致性问题的情况，一般是用于那些用作其他表的外部键记录。用于级联删除操作。 ? 例如：

复制代码

例：下例说明inserted表和deleted表的作用 2 
 3 if exists(select name where nametrig_2' and type')
 4     trigger trig_2
trigger trig_2 
 8  9 after update                         update触发器
print inserted表'
from inserted
13     deleted表14     from deleted
16 update student set sname关二' 2
18 drop trigger trig_2
20 
trigger trig_3 
23 after insert                         insert触发器
24 25     26     27     28     29 30 insert student 100,刘一百',0); line-height:1.5!important">男25)
31 trigger trig_3
32 
33 
34 trigger trig_4 
35 36 after delete                         delete触发器
37 38     39     40     41     42 43 100
44 trigger trig_4

复制代码

? ? 修改触发器 ? 语法格式：

ALTER TRIGGER trigger_name

ON ( table | view )

{

? { ( FOR | AFTER | INSTEAD OF ) }

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

? sql_statement […n ]

? }? ?

DDL触发器使用

例如：

DDL触发器在test数据库上创建一个DDL触发器safe，用来防止数据库中的任一表被修改或删除。 5 6 trigger safetest on database 数据库DDL触发器 8 after drop_table,alter_table as raiserror(不能修改表结构16,0); line-height:1.5!important">2) rollback 14 15 执行以下程序，观察结果 alter table student add nation char(10) 19 disable trigger safetest database ------------------------------------------------------- 22 在服务器上创建一个DDL触发器tablecreat，用来防止在服务器上创建数据库26 27 trigger trig_last 28 on all server 29 after create_database 32 不能创建新的数据库33 36 38 database test_trig 39 40 disable trigger trig_last 41 all server

复制代码

删除触发器

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

DROP TRIGGER { trigger } [,…n ]

触发器禁用和启用

例如：

触发器禁用和启用禁用sc表上的触发器trig_g。table sc disable trigger trig_g 5 disable trigger trig_g on sc 7 启用sc表上的触发器trig_g。table sc enable 10 enable 11 12 insert into sc 1,128); line-height:1.5!important">-50) from sc

复制代码

触发器具体应用

例如：

具体应用创建触发器trig3，当删除student表中的学生记录时，应该同时删除sc表中对应的记录trigger trig_5 for delete 11 delete sc 12 where sc.sno in(select sno from deleted) from sc 19 exec sp_help score --查看其中外键 alter table score --删除外键 drop CONSTRAINT FK_score_course alter table score drop CONSTRAINT FK_score_student27 3 30 32 from sc 33 ----------------------------------------------------------- 34 35 36 trigger trig_g 38 after 39 40 declare @g int 42 select @g=grade 43 if <0 44 45 select 成绩必须>=046 47 48 49 50 51 52 10,128); line-height:1.5!important">53 54 55 56 例: 建立一个修改触发器trigno， 57 该触发器防止用户修改表student的学号58 59 trigger trigno 60 61 after 62 63 update(sno) 64 65 不能修改学号66 67 68 69 70 71 set sno2172 73 ---------------------------------------------------- 74 75 76 INSTEAD OF触发器77 78 例：在student表上创建一个INSTEAD OF触发器trig_6， 79 当用户插入数据时注意观察触发器的执行。80 81 trigger trig_6 82 83 instead of insert 84 85 86 87 88 89 into student(sno,sname) values(300白扯')

复制代码

转自：?

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!