Search code examples
sql-serversql-server-2012-express

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

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?

Solution

  • 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).