How to organize updated row logging

Question

Hello, I have some tables in Sql Server. I want to log row, if row is updated or deleted. how can I organize this process?

Sample table structure:

    [uniqueGUID] [uniqueidentifier] NOT NULL,
    *[kod_a] [nchar](5) NOT NULL,
    *[kod_b] [nchar](5) NOT NULL,
    *[kod_c] [nchar](2) NOT NULL,
    *[kod_d] [nchar](4) NOT NULL,
    [name] [nvarchar](25) NULL,
    [sname] [nvarchar](25) NULL,
    [address] [nvarchar](25) NULL,
    [payment] money NULL

Note: marked columns (kod_a, kod_b, kod_c, kod_d) are unique constaint together. Another tables also in same structure, columns count may be different.

some idea , please.

thanks.

Answer 1

The old data can be exposed in a trigger using the Deleted table. I'll stub out a quick example to get you started.

create trigger YourTriggerName on YourTable
after update, delete
as
    /* Deleted table contains the "old" values before the update/delete operation */
    insert into YourLoggingTable
        (UniqueGUID, kod_a, ..., payment, DateModified)
        select UniqueGUID, kod_a, ..., payment, getdate()
            from Deleted 
go

Answer 2

If all your updates are going through a single stored procedure you could create and execute an audit stored procedure when you update or delete data in this table.

Triggers are great, but they can sometimes cause pain. If possible avoid using them.