SQL Server--触发器

十三、触发器

1. DML触发器
（1）创建DML触发器
create trigger 触发器名 on [with encryption加密] as SQL语句
在数据库test中建立一个表table20，创建一个触发器trigtest，在table20表中插入、修改和删除记录时，自动显示表中的所有记录。并用相关数据进行显示。

注意：create trigger语句必须是批处理中的第一个语句

use test
go
create table table20	--创建表table20
(	c1 int,
	c2 char(30)
)
go
create trigger trigtest	--创建触发器trigtest
	on table20 after insert,update,delete
as
	select * from table20
go

--insert
use test
go
insert table20 values(1,'Name1')
go

--update
use test
go
update table20 set c2='Name2' where c1=1
go

--delete
use test
go
delete table20 where c1=1
go

（2）删除DML触发器
drop trigger 触发器名
删除school数据库student表上的trigop触发器。

use school
go
drop trigger trigop
go

（3）禁用DML触发器
disable trigger 触发器名 on 表名
禁用test数据库中table20表上的trigtest触发器。

use test
go
disable trigger trigtest on table20
go

（4）启用DML触发器
enable trigger 触发器名 on 表名
启用test数据库中table20表上的trigtest触发器。

use test
go
enable trigger trigtest on table20
go

（5）insert、update和delete触发器
insert：
建立一个触发器trigname，当向student表中插入数据时，如果出现重名的情况，则回滚该事务。

use school
go
create trigger trigname
	on student after insert
as
begin
	declare @name char(10)--inserted的name
	select @name=inserted.姓名 from inserted
	if exisits(select 姓名 from student where 姓名=@name)
	begin
		raiserror('姓名重复,不能插入',16,1)
		rollback
	end
end

建立一个触发器trigsex，当向student表中插入数据时，如果出现性别不正确的情况，不回滚该事务，只提示错误信息。

use test
go
create trigger trigsex
	on student after insert
as
	declare @s1 char(1)
	select @s1=性别 from inserted
	if @s1<>'男' or @s1<>'女'
		raiserror('性别只能取男或女',16,1)
go

---

update触发器
两步：捕获数据前像的delete语句和捕获数据后像的insert语句。
使用if update语句定义一个监视指定列的数据更新的触发器。
创建一个更新触发器trigno，该触发器防止用户修改表student的学号。

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

--提示修改记录时出错，也并没有修改student表中学号为101的记录。
use school
go
update student set 学号='301' where 学号='101'
go

建立一个触发器trigcopy，将student表中所有被修改的数据保存到stbak表中作为历史记录。

use school
go
if object_id('stbak','U') is not null
	drop table stbak
create table stbak
(	rq datetime,	--修改时间
	sno char(10),	--学号
	sname char(10), --姓名
	ssex char(2),	--性别
	sbirthday datetime,--出生日期
	sclass char(10)	--班号
)
go
create trigger trigcopy
	on student after update
as
	--将当前日期和修改后的记录插入stbak表中
	insert into stbak(rq,sno,sname,ssex,sbirthday,sclass)
		select getdate(),inserted.学号,inserted.姓名,
		       inserted.性别,inserted.出生日期,inserted.班号
		from student,inserted
		where student.学号=inserted.学号
go

---

delete触发器:
建立一个删除触发器trigclass，防止用户删除表student中所有1001班的学生记录。

use school
go
create trigger trigclass
	on student after delete
as
	if exists(select * from deleted where 班号='1001')
	begin
		raiserror('不能删除1001班的学生记录',16,2)
		rollback
	end
go

（6）instead of触发器
在score表上创建一个instead of insert 触发器trigscore，当用户插入成绩记录时检查学号是否在student表中。

use school
go
create trigger trigscore on score
instead of insert
as
	if not exists(select * from student
	  where 学号=(select 学号 from inserted))
	begin
		rollback transaction
		print '要处理记录的学号不存在！'
	end
else
	begin
		insert into score select * from inserted
		print '已经成功处理记录！'
	end

2. DDL触发器
（1）创建DDL触发器
create trigger 触发器名称 on as SQL语句
在school数据库上创建一个DDL触发器safe，用来防止该数据库中的任一表被修改或删除。

use school
go
create trigger safe
	on database after drop_table,alter_table
as
	begin
		raiserror('不能修改表结构',16,2)
		rollback
	end
go

（2）登录触发器
在master数据库上创建一个登录触发器triglogin，用来防止建立新的登录账号。

use master
go
create trigger triglogin on all server
for create_login
as
	print '不允许建立登录账号'
	rollback
go

（3）使用系统存储过程查看触发器
sp_help查看触发器的一般信息：exec sp_help ‘触发器名称’。
sp_helptext查看触发器的正文信息：exec sp_helptext ‘触发器名称’。
sp_depends查看指定触发器所引用的表或者指定的表涉及的所有触发器：exec sp_depends ‘触发器名称’。

使用系统存储过程查看student表上触发器trigno的相关信息。

use school
go
exec sp_help 'trigno'
exec sp_helptext 'trigno'

（4）修改触发器
alter trigger 触发器名称 on as SQL语句