Using SQL Server trigger to record the trace of historical modification of the table

In many application development, the history or modification traces of some data tables need to be recorded so that data troubleshooting can be carried out in the future when data errors occur. For this kind of business requirement, we can easily realize the history function through the trigger of the database.

This article takes the trigger in the SQL Server 2005 database as an example (because the project in hand uses this database)

Let's briefly describe the SQL Server trigger.

Inserted and deleted of SQL Server trigger

SQL Server creates two dedicated virtual tables for each trigger: the inserted table and the deleted table. These two tables are maintained by the system, and they exist in memory, not in the database. The structure of these two tables is always the same as the table structure that is acted on by the trigger. After the trigger is executed, the two tables associated with the trigger are deleted (that is, destroyed in memory).

The inserted table holds all the rows that you want to insert when you execute an insert or update statement; that is, the value after insertion or update.

The deleted table holds all rows that are deleted from the table by the delete or update statement; that is, delete or update the value of the money.

SQL operation inserted table deleted table addition (insert) record storage new record [unavailable] modified (update) record storage updated record storage record deletion (delete) record [unavailable] storage deleted record

Instead of and after of SQL Server trigger

SQL Server provides two types of triggers: instead of and after triggers. The difference between these two triggers is that they are activated at different times:

Instead of triggers are used instead of sql statements executed by reference triggers. In addition to tables, instead of triggers can also be used for views, which can be used to extend views to support update operations. After triggers execute after an inserted, update, or delete statement, and constraint checking occurs before the after trigger is activated. After triggers can only be used in data tables.

We say (copy) so much because the function we want to implement requires the use of inserted virtual tables, deleted virtual tables, and after triggers.

Realization method

An example is given to demonstrate the specific implementation method.

Suppose you currently have a table: product table (product) with fields of "product name (name)", "product description (description)", "unit price (unit_cost)", and "date of generation (pub_time)".

CREATE TABLE product (name VARCHAR 50, description VARCHAR 200, unit_cost MONEY,pub_time DATETIME)

GO

Now the requirement of our "God" is to record every operation change when there are data changes (add, delete, change) in the product table.

1. Create a log table

You need to create a product log table (product_log) to record each data change. Here, I directly add two fields to the structure of the original data table (in the actual development environment, you can set the table structure of the log table according to your requirements). The codes for sqlcomm and exectime; are as follows:

CREATE TABLE product_log (name VARCHAR (50), description VARCHAR (200), unit_cost MONEY,pub_time DATETIME,sqlcomm varchar (10), exectime datetime) GO

Two new fields, sqlcomm and exectime, record the execution of commands (insert, update and delete) and execution time, respectively

two。 Add trigger

The purpose of adding triggers to the product table is to record it in product_log when the data of the table changes.

For the insert operation, add a trigger named tr_product_i:

CREATE TRIGGER tr_product_iON productAFTER INSERTASif @ @ rowcount = 0-to avoid resource consumption, end trigger returninsert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'insert',getdate () from insertedGO when the number of affected rows is 0

For update (update) operations, add a trigger named tr_product_u:

CREATE TRIGGER tr_product_uON productAFTER UPDATEASif @ @ rowcount = 0-to avoid resource consumption, when the number of affected rows is 0 End trigger return/* before update * / insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'update',getdate () from deleted/* update * / insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'update',getdate () from insertedGO

For the delete operation, add a trigger named tr_product_d:

CREATE TRIGGER tr_product_dON productAFTER DELETEASif @ @ rowcount = 0-to avoid resource consumption, end trigger returninsert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'delete',getdate () from deletedGO when the number of affected rows is 0

3. Test trigger

Insert test

INSERT INTO product (name,description,unit_cost,pub_time) VALUES ('comma', 'this is a comma test data', 200.5) GOSELECT * FROM productSELECT * FROM product_logGO

Update (update) test

UPDATE product SET unit_cost=250.0 WHERE name=' teases' GOSELECT * FROM productSELECT * FROM product_logGO

Delete (delete) Test

DELETE FROM product WHERE name=' teases' GOSELECT * FROM productSELECT * FROM product_logGO

All right, that's all for this article. Friends who need it can refer to it.