Audit Triggers: Use the INSERTED or DELETED System Tables

Question

The topic of discussion of audit tables has recently arisen in our discussions ... therefore, I like your opinion on how best to approach this. Our database has a combination of both approaches (which is not very good), since each previous database administrator did what he thought was right. Therefore, we must change them in order to follow any one model.

CREATE TABLE dbo.Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
)

CREATE TABLE dbo.Audit_Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
Audit_Type VARCHAR(1)  NOT NULL
Audited_Created_On DATETIME
Audit_Created_By VARCHAR(50)
)

1: , / ( DELETED). , UPDATE DELETE "Audit_Type" "w" ( UPDATE) "D" ( DELETE)

. . .

: , : , .

Appraoch 2: , ( INSERTED).

, INSERTED/UPDATED/DELETED . , , . ( INSERTED) . ( DELETED) .

: , .

, ?

Answer 1

:

Appraoch 2: , ( INSERTED).

- , ? , .

(, X-), , - :

• ( ), , .
• ( ) ( , )

Appraoch 1: , ( ), , , .

Answer 2

, , , "", "" .

, "name", "name_old" "name_new" .

INSERT "name_old" / , "name_new" INSERTED. UPDATE "name_old" DELETED "name_new" INSERTED DELETE "name_old" DELETED "new_name" /.

( FULL join )

VARCHAR , INTEGER, DATETIME .. , .

.. 5 7, :

quantity_old  quantity_new
           5             7

, 2 .

, "" - ...