SQL Server uses triggers to implement table history

/Files/bgming/sqlhistory.rar

In many applications, you need to save the historical records of a data table for future query. in SQL Server 2000

.

In the trigger, the SQL server's inserted and deleted virtual tables are used to execute the SQL
 
Command, the two virtual tables respectively record the following content:
 
SQL command deleted inserted
---------------------------------------------------
Insert [unavailable] newly inserted records
Updated records before update
Delete deleted records [unavailable]
---------------------------------------------------

The following example demonstrates the specific implementation method.

For example:
 
Currently, there is a data table Table1 with three fields: field1, field2, and field3.
 
Now we need to record each change when Table 1 Changes (insert, modify, delete.
 
This can be achieved through the following two steps:
 
 
1) create a new table table1_log to record the historical data of table1.
 
Table ‑ log has the following fields:

Field1, field2, field3, sqlcomm (varchar 10), exectime (datetime)
 
This is more than Table1 in sqlcomm and exectime fields, respectively recording the SQL statement insert,

Update, delete command and command execution time)
 
 

2) Add a trigger to Table 1 to automatically write data to table audit logs when Table 1 Changes.
 
 
-- Add a trigger named trtable1_ I for the INSERT command:

-------------------------------- Trtable1_ I ----------------------------
If exists (select * From DBO. sysobjects
Where id = object_id (n' [trtable1_ I] ')
And objectproperty (ID, n' istrigger') = 1)
Drop trigger [trtablepolici]
Go

Create trigger trtable1_ I
On Table1
After insert
As

If @ rowcount = 0 -- if the number of affected rows is 0, the trigger is stopped to avoid resource occupation.
Return

Insert into table1_log (field1, field2, field3, sqlcomm, exectime)
Select field1, field2, field3, 'insert', {fn now ()} from inserted

Go
-------------------------------- End trtable1_ I -----------------------


-- For the update command, add a trigger named trtable1_u:

-------------------------------- Trtable1_u ----------------------------
If exists (select * From DBO. sysobjects
Where id = object_id (n' [trtable1_u] ')
And objectproperty (ID, n' istrigger') = 1)
Drop trigger [trtable1_u]
Go

Create trigger trtable1_u
On Table1
After update
As

If @ rowcount = 0 -- if the number of affected rows is 0, the trigger is stopped to avoid resource occupation.
Return

Insert into table1_log (field1, field2, field3, sqlcomm, exectime)
Select field1, field2, field3, 'update', {fn now ()} from inserted

Go
-------------------------------- End trtable1_u -----------------------


-- Add a trigger named trtablemongod for the DELETE command:

-------------------------------- Trtable1_d ----------------------------
If exists (select * From DBO. sysobjects
Where id = object_id (n' [trtable1_d] ')
And objectproperty (ID, n' istrigger') = 1)
Drop trigger [trtable1_d]
Go

Create trigger trtable1_d
On Table1
After Delete
As

If @ rowcount = 0 -- if the number of affected rows is 0, the trigger is stopped to avoid resource occupation.
Return

Insert into table1_log (field1, field2, field3, sqlcomm, exectime)
Select field1, field2, field3, 'delete', {fn now ()} from deleted

Go
-------------------------------- End trtable1_d -----------------------



Run the above Code in the query analyzer. Later, changes to Table 1 will be recorded in the tablew.log table.
 
(I have just started to contact the "Trigger". please correct me for any errors !)