First, you need to create a test data table, one for inserting data: test3, and the other for backup: test3_bak
First, you need to create a test data table, one for inserting data: test3, and the other for backup: test3_bak
The Code is as follows:
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 ));
Step 2: Compile the backup trigger, which is triggered only when the backup is updated or inserted.
The Code is 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
Step 3: Test Data:
The Code is 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 query and track data changes in two tables:
The Code is as follows:
Select * from test3
Select * from test3_bak
