First, you need to create a test data table, one for inserting data: test3, and the other for backup: test3_bak
The following is a reference clip:
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 following is a reference clip:
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 following is a reference clip:
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 following is a reference clip:
Select * From test3
Select * From test3_bak