主题：关于数据库触发器学习的资料

触发器 1 创建 create trigger ins on student for insert //delete update as print '添加记录一条' 可通过此语句观察 insert into student(number) values('200')

还可以指定列: create trigger ins1 on student for update as if update(name) print '添加姓名一个'

可通过此语句观察 update student set name='Tom' where number='200' update student set height=2.01 where number='200'

注意：1）企业管理器中只有打开表设计器方可编辑触发器 2）触发器的察看可以利用master的sysobjects，或sp_helptext ins1,或右击表“管理触发器”

2 INSERTED,DELETED 表格 每次插入记录，都会首先插入到INSERTED， 每次删除记录，都会首先插入到DELETED， 每次更新记录，都会将旧记录首先插入到DELETED，再把新记录放入INSERTED

如 create trigger t1 on dbo.Customers for delete as insert into customers select * from deleted

delete from customers

再如 CREATE trigger t2 on dbo.Customers for update as select * from inserted select * from deleted

update customers set Address=''

例1：察看插入记录 create trigger t1 on student for insert as print 'insert' select * from inserted

insert into student(number,name) values('200','Tom') 例2：防止删除全部记录和三条以上记录 create trigger t1 on student for delete as declare @delcount int declare @total int select @delcount=count(*) from deleted select @total=count(*) from student if @total=0 or @delcount>3 begin print '撤销' rollback transaction end 可通过此语句观察 delete from student

例3：删除前备份全部删除记录 create trigger t2 on student for delete as insert into student1 select * from deleted 可通过此语句观察 delete from student

例4：利用触发器，更新生日时自动更新相关年龄字段 create table stu1( number char(4) not null, birthday datetime)

create table stu2( number char(4) not null, age int)

create trigger tri1 on stu1 for update as if update(birthday) declare @num char(4) declare @birth datetime select @num=number from deleted select @birth=birthday from inserted update stu2 set age=year(getdate())-year(@birth) where number=@num

自动插入更新 create trigger tri2 on stu1 for insert as declare @num char(4) declare @birth datetime select @num=number from inserted select @birth=birthday from inserted insert into stu2 values(@num,year(getdate())-year(@birth))

自动删除更新 create trigger tri3 on stu1 for delete as declare @num char(4) select @num=number from deleted delete from stu2 where number=@num