复制代码 代码如下:

create table test3(id int primary key not null

identity(1,1),uname varchar(20),uage int);

create table test3_bak(id int primary key not

null identity(1,1),bid int,uname varchar(20),

uage int,active char(1));

第二步，编写备份用的触发器，只有更新或者是插入的时候才触发

复制代码 代码如下:

alter trigger test3_bak_insert_update

on test3

for insert,update

as

declare @id int

declare @uname varchar(20)

declare @uage int

begin

select @id=id,@uname=uname,@uage=uage from inserted

if @id<>0

begin

update test3_bak set active='0' where bid=@id

insert into test3_bak(bid,uname,uage,active)

values(@id,@uname,@uage,'1')

end

end

第三步，测试数据：

复制代码 代码如下:

insert into test3(uname,uage) values('FLB',20)

insert into test3(uname,uage) values('FLB1',21)

insert into test3(uname,uage) values('FLB2',22)

update test3 set uage=100 where id=27

delete from test3 where id=20

最后，你可自己采用下面方法查询跟踪两个表的数据变化：

复制代码 代码如下:

select * from test3

select * from test3_bak