SQL历史数据相关触发器示例

　　/*创建员工表，员工编号，员工名称，员工工资，员工所在的部门号
　　部门表，部门号，部门名称
　　1.设计存储过程实现员工调动
　　2.设计存储过程管理员工工资
　　要求：
　　能够跟踪员工工资的变化
　　能够跟踪员工所在变化
　　对应的被审计表，每表增加字段录入时间，生效时间，失效时间，最后更新时间（要求系统自动更新）
　　*/
　　
　　create table dept
　　(
　　did int primary key,
　　dname varchar(20),
　　dnew datetime default getdate(),
　　dstart datetime,
　　dend datetime,
　　dlast datetime
　　)
　　--部门表
　　create table emp
　　(
　　eid int primary key,
　　ename varchar(10),
　　esal money,
　　edid int references dept(did),
　　enew datetime default getdate(),
　　estart datetime,
　　eend datetime,
　　elast datetime
　　)
　　--drop table dept
　　--drop table emp
　　insert into dept (did,dname,dstart,dend,dlast) values (1001,'人事部','2001-11-08','2005-11-21',getdate())
　　insert into dept (did,dname,dstart,dend,dlast) values (1002,'财务部','2003-11-09','2005-11-21',getdate())
　　insert into dept (did,dname,dstart,dend,dlast) values (1003,'行政部','1999-08-01','2005-12-12',getdate())
　　insert into dept (did,dname,dstart,dend,dlast) values (1004,'技术部','2001-01-29','2006-01-11',getdate())
　　insert into dept (did,dname,dstart,dend,dlast) values (1005,'公关部','2002-04-21','2008-09-12',getdate())
　　--select * from dept
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2001,'lecky',500,1001,'1996-05-16','2009-09-21',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2002,'twain',1200,1001,'2004-07-10','2010-06-15',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2003,'usher',12000,1001,'2003-12-14','2006-12-15',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2004,'garden',1700,1001,'2005-09-01','2005-12-23',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2005,'christina',2900,1002,'2005-11-19','2005-11-30',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2006,'avril',100,1002,'2003-11-02','2008-08-08',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2007,'eminem',5200,1002,'2002-12-31','2050-10-01',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2008,'hamasaki',4300,1003,'1990-12-19','2006-05-01',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2009,'se7en',6000,1003,'1982-05-19','2100-09-18',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2010,'vitas',800,1004,'2000-01-01','2005-11-28',getdate())
　　insert into emp (eid,ename,esal,edid,estart,eend,elast) values (2011,'sato',800,1004,'2000-01-01','2005-11-22',getdate())
　　--select * from emp
　　
　　/*设计存储过程实现员工调动*/
　　create proc p1 @eid int,@did int
　　--@eid调动的员工号，@did调入的部门
　　as
　　begin
　　
　　 update emp set edid=@did where eid=@eid
　　end
　　--drop proc p1
　　/*员工调动相应的触发器*/
　　create trigger update_p1
　　on emp for update,delete
　　as
　　begin
　　
　　 if(update(enew))
　　 begin
　　 print'该列不允许更新！'
　　 rollback
　　 --录入时间字段不允许更新
　　 end
　　 if(update(edid))
　　 begin
　　 declare @eid int
　　 declare @check1 int,@check2 int
　　
　　 if (not exists (select * from sysobjects where name='new'))
　　 begin
　　 select * into new from emp where 1>2
　　 end
　　
　　 if (not exists (select * from sysobjects where name='old'))
　　 begin
　　 select * into old from emp where 1>2
　　 end
　　
　　 --如果此触发器是第一次执行，则new与old应不存在，即
　　 --系统表sysobjects中不存在name为new与old的记录，
　　 --用not exists判定后，如为真
　　 --则建立此表并复制基表结构，若触发器已经执行过，
　　 --则该表已经存在，并已经记录历史数据，系统表相关
　　 --记录存在，直接执行注释下面语句即可
　　 insert into old select * from deleted
　　 insert into new select * from inserted
　　 --将两个逻辑表中的内容插入两个关系表
　　 declare cr1 cursor scroll for select eid from new
　　 open cr1
　　 fetch last from cr1 into @eid
　　 --使用游标定位last最后一行，即刚刚插入该表的行
　　 --也即update基表的行
　　 --@eid记录update语句影响记录的员工号
　　 declare cr2 cursor scroll for select edid from old
　　 open cr2
　　 fetch last from cr2 into @check1
　　 --@check1记录update语句更新前的员工所属部门
　　 declare cr3 cursor scroll for select edid from new
　　 open cr3
　　 fetch last from cr3 into @check2
　　 --@check2记录update语句更新后的员工所属部门
　　 --print @check
　　 --print @eid
　　
　　 if @check1=@check2
　　 begin
　　 print'调入的部门与原部门相同！'
　　 rollback
　　 --如果员工转入的部门与原部门相同，回退
　　 end
　　
　　 close cr1
　　 deallocate cr1
　　 close cr2
　　 deallocate cr2
　　 close cr3
　　 deallocate cr3
　　 --关闭以及删除游标
　　 end
　　 update emp set elast=getdate() where eid=@eid
　　 declare @date datetime
　　 declare cr4 cursor scroll for select eend from old
　　 open cr4
　　 fetch last from cr4 into @date
　　 if(@date　　 begin
　　 print'该记录已失效！'
　　 rollback
　　 --失效时间已过，不可更新
　　
　　 end
　　 close cr4
　　 deallocate cr4
　　
　　
　　 --将emp表中的最后更新时间字段更新为当前时间
　　
　　end
　　--drop trigger update_p1
　　--select * from new
　　--select * from old
　　--select * from emp
　　exec p1 2001,1003
　　exec p1 2011,1003
　　--2011号记录已经失效
　　update emp set enew='2005-12-20'
　　--执行该语句触发器会输出'该列不允许更新！'
　　
　　/*设计存储过程管理员工工资*/
　　create proc p2 @eid int,@sal int
　　--@eid修改工资的员工号，@sal改变后的工资
　　as
　　begin
　　
　　 update emp set esal=@sal where eid=@eid
　　end
　　--drop proc p2
　　/*工资管理相应的触发器*/
　　create trigger update_p2
　　on emp for update
　　as
　　begin
　　
　　
　　 if(update(enew))
　　 begin
　　 print'该列不允许更新！'
　　 rollback
　　 --录入时间字段不允许更新
　　 end
　　 if(update(esal))
　　 begin
　　 declare @eid int
　　 declare @check1 int,@check2 int
　　
　　
　　 if (not exists (select * from sysobjects where name='new1'))
　　
　　 begin
　　 select * into new1 from emp where 1>2
　　 end
　　
　　 if (not exists (select * from sysobjects where name='old1'))
　　
　　 begin
　　 select * into old1 from emp where 1>2
　　
　　 end
　　
　　 --如果此触发器是第一次执行，则new与old应不存在，即
　　 --系统表sysobjects中不存在name为new与old的记录，
　　 --用not exists判定后，如为真
　　 --则建立此表并复制基表结构，若触发器已经执行过，
　　 --则该表已经存在，并已经记录历史数据，系统表相关
　　 --记录存在，直接执行注释下面语句即可
　　 insert into old1 select * from deleted
　　 insert into new1 select * from inserted
　　 --将两个逻辑表中的内容插入两个关系表
　　 declare cr1 cursor scroll for select eid from new1
　　 open cr1
　　 fetch last from cr1 into @eid
　　 --使用游标定位last最后一行，即刚刚插入该表的行
　　 --也即update基表的行
　　 --@eid记录update语句影响记录的员工号
　　 declare cr2 cursor scroll for select esal from old1
　　 open cr2
　　 fetch last from cr2 into @check1
　　 --@check1记录update语句更新前的员工工资
　　 declare cr3 cursor scroll for selec