Trigger to log inserted/updated/deleted values SQL Server 2012

Question

I'm using SQL Server 2012 Express and since I'm really used to PL/SQL it's a little hard to find some answers to my T-SQL questions.

What I have: about 7 tables with distinct columns and an additional one for logging inserted/updated/deleted values from the other 7.

Question: how can I create one trigger per table so that it stores the modified data on the Log table, considering I can't used Change Data Capture because I'm using the SQL Server Express edition?

Additional info: there is only two columns in the Logs table that I need help filling; the altered data from all the columns merged, example below:

CREATE TABLE USER_DATA
(
    ID INT IDENTITY(1,1) NOT NULL, 
    NAME NVARCHAR2(25) NOT NULL,
    PROFILE INT NOT NULL,
    DATE_ADDED DATETIME2 NOT NULL
)
GO

CREATE TABLE AUDIT_LOG
(
    ID INT IDENTITY(1,1) NOT NULL,
    USER_ALTZ NVARCHAR(30) NOT NULL,
    MACHINE SYSNAME NOT NULL,
    DATE_ALTERERED DATETIME2 NOT NULL,
    DATA_INSERTED  XML,
    DATA_DELETED XML
)
GO

The columns I need help filling are the last two (DATA_INSERTED and DATA_DELETED). I'm not even sure if the data type should be XML, but when someone either

INSERTS or UPDATES (new values only), all data inserted/updated on the all columns of USER_DATA should be merged somehow on the DATA_INSERTED.

DELETES or UPDATES (old values only), all data deleted/updated on the all columns of USER_DATA should be merged somehow on the DATA_DELETED.

Is it possible?

I would prefer to have an audit for each table instead of one generic one. It makes looking at the history a LOT simpler. If you store everything as XML you have to shred all that XML to look at the history. — Sean Lange, Nov 04 '16 at 18:23
@SeanLange Hey, Thanks! I did not known about "audit". I google-searched about it and found the solution that I wanted (kind of, will have to modify a procedure for my needs) on the first link from [this answer](http://stackoverflow.com/a/17546860/2970272). Please post it as an answer so I can accept it. Cheers! — mrbTT, Nov 04 '16 at 18:57
The post you linked described pretty much what I was talking about. I don't care about the points and I don't want to rewrite that just to get some points. Glad you found a workable solution. — Sean Lange, Nov 04 '16 at 19:19
Glad you wrote a comment making be aware of what an "audit" was! — mrbTT, Nov 04 '16 at 20:08

score 0 · Answer 1 · answered Nov 04 '16 at 19:01

Use the inserted and deleted Tables

DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX. In DML triggers, the inserted and deleted tables are primarily used to perform the following: Extend referential integrity between tables. Insert or update data in base tables underlying a view. Test for errors and take action based on the error. Find the difference between the state of a table before and after a data modification and take actions based on that difference.

And

OUTPUT Clause (Transact-SQL)

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

score 0 · Accepted Answer · edited May 23 '17 at 12:19

Just posting because this is what solved my problem. As user @SeanLange said in the comments to my post, he said to me to use an "audit", which I didn't know it existed.

Googling it, led me to this Stackoverflow answer where the first link there is a procedure that creates triggers and "shadow" tables doing sort of what I needed (it didn't merge all values into one column, but it fits the job).

Trigger to log inserted/updated/deleted values SQL Server 2012

2 Answers2