触发器 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