I want to insert rows into the audit table whenever an insert, update or delete takes place in the master table "Table1" - doesn't matter which column is changed/inserted. I also want to add I, U or D on insert, update or delete. For insert and delete I am checking if rows exist in the inserted and deleted table. What is the best way to approach update.
My code for insert and delete is :
CREATE TRIGGER [dbo].[tr_Table1_InsertUpdate_Table1History_Insert]
ON [dbo].[Table1]
FOR INSERT, DELETE, UPDATE
AS
BEGIN
IF EXISTS(SELECT * FROM Inserted)
BEGIN
INSERT INTO Table1History(...., Modification开发者_如何学编程Type)
SELECT ..., 'I'
FROM Inserted
END
IF EXISTS(SELECT * FROM Deleted)
BEGIN
INSERT INTO Table1History(..., ModificationType)
SELECT ..., 'D'
FROM Deleted
END
END
GO
Kindly help!
For updates, the original values for the row will be added to the deleted table, and the new values for the row will be added to the inserted table. So, to identify inserts, deletes and updates you would do the following
- Inserts - get the rows from inserted that are not in deleted
- Deletes - get the rows from deleted that are not in inserted.
- Updates - get the rows that are in both inserted and deleted
Below is an example of a trigger generated by ApexSQL Audit
It’s not a cheap tool but you can probably use it in trial mode to get the job done.
Notice the INSERT INTO dbo.AUDIT_LOG_DATA part and repeat it for every column you want to audit.
There are two tables in the background for storing the data and several stored procedures as well but this will get you going in the right direction.
CREATE TRIGGER [dbo].[tr_d_AUDIT_TableName]
ON [dbo].[TableName]
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
DECLARE
@IDENTITY_SAVE varchar(50),
@AUDIT_LOG_TRANSACTION_ID Int,
@PRIM_KEY nvarchar(4000),
--@TABLE_NAME nvarchar(4000),
@ROWS_COUNT int
SET NOCOUNT ON
Select @ROWS_COUNT=count(*) from deleted
Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))
INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
HOST_NAME,
APP_NAME,
MODIFIED_BY,
MODIFIED_DATE,
AFFECTED_ROWS,
[DATABASE]
)
values(
'TableName',
'dbo',
3, -- ACTION ID For DELETE
CASE
WHEN LEN(HOST_NAME()) < 1 THEN ' '
ELSE HOST_NAME()
END,
CASE
WHEN LEN(APP_NAME()) < 1 THEN ' '
ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
@ROWS_COUNT,
'DatabaseName'
)
Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()
INSERT
INTO dbo.AUDIT_LOG_DATA
(
AUDIT_LOG_TRANSACTION_ID,
PRIMARY_KEY_DATA,
COL_NAME,
OLD_VALUE_LONG,
DATA_TYPE
, KEY1
)
SELECT
@AUDIT_LOG_TRANSACTION_ID,
convert(nvarchar(1500), IsNull('[Order_ID]='+CONVERT(nvarchar(4000), OLD.[Order_ID], 0), '[Order_ID] Is Null')),
'Order_ID',
CONVERT(nvarchar(4000), OLD.[Order_ID], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[Order_ID], 0))
FROM deleted OLD
WHERE
OLD.[Order_ID] Is Not Null
END
Have you consider using AutoAudit?
AutoAudit is a SQL Server (2005, 2008) Code-Gen utility that creates Audit Trail Triggers with:
- Created, CreatedBy, Modified, ModifiedBy, and RowVersion (incrementing INT) columns to table
- Insert event logged to Audit table
- Updates old and new values logged to Audit table
- Delete logs all final values to the Audit tbale
- view to reconstruct deleted rows
- UDF to reconstruct Row History
- Schema Audit Trigger to track schema changes
- Re-code-gens triggers when Alter Table changes the table
精彩评论