触发器学习

触发器的类型

（1）DML触发器。当数据库中发生数据操纵语言（DML）事件时将调用DML触发器。一般情况下，DML事件包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因而DML触发器也可分为三种类型：INSERT、UPDATE和DELETE。

（2）DDL触发器。DDL触发器也是由相应的事件触发的，但DDL触发器触发的事件是数据定义语句（DDL）。这些语句主要是以CREATE、ALTER、DROP等关键字开头的语句。DDL触发器的主要作用是执行管理操作，例如审核系统、控制数据库的操作等。

DML触发器

CREATE TRIGGER [ schema_name . ]trigger_name 
	ON { table | view } 		/*指定操作对象*/
		[ WITH  ENCRYPTION ]	/*说明是否采用加密方式*/
	{ FOR |AFTER | INSTEAD OF } 
		{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
	[ WITH APPEND ] 
	[ NOT FOR REPLICATION ] 		/*说明该触发器不用于复制*/
AS {  sql_statement  [ ; ] [ ...n ]
	| EXTERNAL NAME asse MBly_name.class_name.method_name 
	}

说明：
（1）trigger_name：用于指定触发器名，触发器名必须符合标识符规则，并且在数据库中必须唯一。
（2）table | view：指在其上执行触发器的表或视图，有时称为触发器表或触发器视图。
（3）AFTER：用于说明触发器在指定操作都成功执行后触发，如AFTER INSERT表示向表中插入数据时激活触发器。
（4）INSTEAD OF：指定用DML触发器中的操作代替触发语句的操作。
（5）{[DELETE] [,] [INSERT] [,] [UPDATE]}：指定激活触发器的语句的类型，必须至少指定一个选项。
（6）WITH APPEND：指定应该再添加一个现有类型的触发器。
（7）sql_statement：触发器的T-SQL语句，可以有一条或多条语句，指定DML触发器触发后将要执行的动作。

触发器有下列几点说明：

1）触发器中使用的特殊表。执行触发器时，系统创建了两个特殊的临时表inserted表和deleted表。

inserted表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。

deleted表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted表中。

2）创建DML触发器的说明：

（1） CREATE TRIGGER 语句必须是批处理中的第一条语句，并且只能应用到一个表中。

（2） DML触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象。

（3）创建DML触发器的权限默认分配给表的所有者。

（4）在同一CREATE TRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。

（5）不能对临时表或系统表创建DML触发器。

（6）对于含有DELETE或UPDATE操作定义的外键表，不能使用INSTEAD OF DELETE和INSTEAD OF UPDATE触发器。

（7）TRUNCATE TABLE语句虽然能够删除表中记录，但它不会触发DELETE触发器。

（8）在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。

（9） DML触发器最大的用途是返回行级数据的完整性，而不是返回结果，所以应当尽量避免返回任何结果集。

（10） DML触发器中不能包含以下语句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。

创建INSERT触发器。INSERT触发器是当对触发器表执行INSERT语句时就会激活的触发器。INSERT触发器可以用来修改，甚至拒绝接收正在插入的记录。

--建表
create table table1
(
a int
)
--每次插入操作时，将变量@str的值设为“TRIGGER IS WORKING”并显示
go
create trigger table1_insert on table1 after insert
as
begin
declare @str char
set @str='TRIGGER IS WORKING'
print @str
end

insert into table1 values(1)
select * from table1

create table XSB
(
xh char(6) primary key,
zxf int
)
create table KCB
(
kch char(6) primary key,
xf int
)
create table CJB
(
xh char(6),
kch char(6),
cj int,
primary key(xh,kch),
)
insert into XSB values('1',0)
insert into KCB values('1',10)
select * from CJB
--创建触发器，当向CJB表中插入一个学生的成绩时，将XSB表中该学生的总学分加上添加的课程的学分。
go
create trigger cjb_insert on CJB after insert
as
begin
declare @num char,@kc_num char
declare @xf int
select @num=xh,@kc_num=kch from inserted
select @xf=xf from KCB where kch=@kc_num
update XSB set zxf=zxf+@xf where xh=@num
print 'vectory'
end

insert into CJB values('1','1',666)
select * from CJB
select * from XSB

创建UPDATE触发器。UPDATE触发器在对触发器表执行UPDATE语句后触发。在执行UPDATE触发器时，将触发器表的原记录保存到deleted临时表中，将修改后的记录保存到inserted临时表中。

--创建触发器，当修改XSB表中的学号时，同时也要将CJB表中的学号修改成相应的学号
--（假设XSB表和CJB表之间没有定义外键约束）。
go
create trigger xsb_update on XSB after update
as 
begin
declare @old_num char,@new_num char
select @old_num=xh from deleted
select @new_num=xh from inserted
update CJB set xh=@new_num where xh=@old_num
end


update XSB
set xh='2' where xh='1'
select * from XSB
select * from CJB

创建DELETE触发器。

--在删除XSB表中的一条学生记录时将CJB表中该学生的相应记录也删除。
go
create trigger xsb_delete on XSB after delete
as
begin
delete from CJB where xh in(select xh from deleted)
end

delete from XSB where xh='2'

创建INSTEAD OF触发器。AFTER触发器是在触发语句执行后触发的，与AFTER触发器不同的是，INSTEAD OF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。一个表或视图中只能有一个INSTEAD OF触发器。

--创建表table2，值包含一列a，
--在表中创建INSTEAD OF INSERT触发器，当向表中插入记录时显示相应消息。

create table table2(a int)

go
create trigger table2_insert on table2 instead of insert
as
print 'instead of trigger is working'

insert into table2 values(1)
select * from table2

DDL 触发器

CREATE TRIGGER trigger_name 
	ON { ALL SERVER | DATABASE } 
	[ WITH ENCRYPTION ]
	{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS {	  sql_statement  [ ; ] [ ...n ]
	 | EXTERNAL NAME  asse MBly_name.class_name.method_name
   }

说明：

（1）ALL SERVER | DATABASE：ALL SERVER关键字是指将当前DDL触发器的作用域应用于当前服务器。

（2）event_type：执行之后将导致触发DDL触发器的T-SQL语句事件的名称。

（3）event_group：预定义的T-SQL语句事件分组的名称。

--创建pass数据库作用域的DDL触发器，
--当删除一个表时，提示禁止该操作，然后回滚删除表的操作。
use pass
go
create trigger safety on database
after drop_table
as
print '不能删除该表'
rollback transaction

drop table table1;

未完待续（极大可能就鸽了）……