Copy Code code as follows:
CREATE TABLE test3 (ID int primary key NOT NULL
Identity (1,1), uname varchar (), 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));
The second step is to write a backup trigger, which is only triggered when it is updated or inserted.
Copy Code code as follows:
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
The third step is to test the data:
Copy Code code as follows:
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
Finally, you can use the following method to track data changes in two tables:
Copy Code code as follows:
SELECT * FROM Test3
SELECT * FROM Test3_bak