7. 触发器

触发器作用：
当对某一张表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server 就会自动执行触发器所定义的SQL语句。

---

触发器的分类

1. DML触发器（分为：AFTER触发器、INSTEAD OF触发器）

是当在数据库服务器中，发生数据操作语言(DML)事件时会自动执行的存储过程。
DML事件包括：在指定表或视图中修改数据的INSERT、UPDATE或DELETE语句。

系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误，则整个事务自动回滚。

分为：
(1)AFTER触发器(也就是for触发器)：这类触发器是在记录已经改变完之后，才会被激活执行，它主要是用于记录变更后的处理或检查。

(2)INSTEAD OF触发器：与AFTER触发器不同，这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（UPDATE、INSERT、DELETE），而去执行触发器本身所定义的操作。

2. DDL 触发器（了解 ）

在响应数据定义语言（DDL，create、alter和drop）语句时触发。
添加、删除或修改数据库的对象，一旦误操作，可能导致大麻烦。
如果想能够跟踪这种问题和定位问题的根源，就可以利用DDL触发器来记录类似“用户建立表”这种变化的操作，这样可以大大减轻跟踪和定位数据库模式的变化的繁琐程度。

---

创建触发器需要注意的问题

---

创建触发器

语法格式：

1. DML触发器

1.1关于for、after的使用

例子1：为学生表创建一个简单DML触发器， 在插入和修改数据时，都会自动显示提示信息。

CREATE TRIGGER reminder ON student
FOR  INSERT , UPDATE  
AS print '你在插入或修改学生表的数据'


触发语句: 
UPDATE student SET sname='刘小梅' WHERE sname='刘梅'

---

例子2：在学生表上创建一个DELETE类型的触发器，删除数据时，显示删除学生的个数。

CREATE TRIGGER del_count ON student
FOR DELETE
AS
  DECLARE @count varchar(50)
  SET @count=STR(@@ROWCOUNT) + ' 个学生被删除'  
  SELECT @count
RETURN


触发语句: 
	DELETE FROM student WHERE specialty='计算机'

---

例子3：含插入表:Inserted，删除表:Deleted。

SQL Server 为每个DML触发器都定义了两个特殊的表，一个是插入表:Inserted，一个是删除表:Deleted。

• 不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。
• 在触发器的执行过程中，SQL Server建立和管理这两个临时表。
  这两个表的结构与触发器所在数据表的结构是完全一致的，其中包含了在激发触发器的操作中插入或删除的所有记录。
• 当触发器的工作完成后，这两个表也将会从内存中删除。

两个临时表的作用：

update触发器

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                         
as 
	print 'inserted表'
	select * from inserted
	print 'deleted表'
	select * from deleted
go



update student set sname='关二' where sno='2009001'

insert触发器

create trigger trig_3 
on student
after insert                       
as 
	print 'inserted表'
	select * from inserted
	print 'deleted表'
	select * from deleted
go
insert student values('200908','刘一百',85,'male','CS','china')
drop trigger trig_3
go

delete触发器

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

---

例子4：Inserted和Deleted具体应用

创建触发器trig5，删除student表中的学生记录时，同时删除sc表中对应的记录

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


delete student where sno='3'
select * from student
select * from sc 
go

---

其他不重要例子

触发器实现表上约束
create trigger trig_g_1 
on sc
after insert
as
begin
	declare @s int
	select @s=score from inserted
	if @s<0
	begin
	     select '成绩必须>=0'
	     rollback
	end
end
go

--执行以下程序，观察结果
insert into sc values('1','5',-50)
select * from sc
--drop trigger trig_g_1  先留着后面会用
--------------------------------------------------------------



create trigger trig_g_2 
on sc
instead of insert
as
begin
	declare @s_no char(6),@c_no char(4),@s int
	select @s_no=sno,@c_no=cno,@s=score from inserted
	if @s<0
	begin
	     select '成绩必须>=0'
	     insert sc values(@s_no,@c_no,0)
	end
end
go

--执行以下程序，观察结果
insert into sc values('1','5',-50)
select * from sc
delete from sc where sno='1' and cno='5'
drop trigger trig_g_2
------------------------------------------------------------------------------




建立一个修改触发器trigno，该触发器防止用户修改表student的学号
create trigger trigno
on student
after update
as
if update(sno)
	begin
		raiserror('不能修改学号',16,2)  --raiserror()用于抛出一个异常或错误
		rollback
	end
go

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

---

1.2 关于instead of的使用

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

--执行以下程序，观察结果
insert into student values('200908','刘一百',85,'male','CS','china')
drop trigger trig_6
go

---

2. DDL触发器

例子1：使用DDL触发器limited来防止数据库中的任一表被修改或删除(自定义错误提示)。

CREATE TRIGGER limited
ON database
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT '触发器不允许您执行对表的修改或删除操作!' 
   ROLLBACK 

触发语句: ALTER TABLE student  ADD birth datetime

---

3. 查看触发器内容 、删除触发器、触发器禁用和启用（不重要）

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


drop trigger trig_1


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

/*禁用sc表上的触发器trig_g。有如下两种方法：*/
alter table sc disable trigger trig_g_1
disable trigger trig_g_1 on sc
go

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

--执行以下程序，观察结果
insert into sc values('2','5',-500)
select * from sc
--delete from sc where sno='2' and cno='5'
drop trigger trig_g_1