CREATE TRIGGER Trg_UsersChangeAudit
ON Users
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
-- First make sure rows were actually affected
IF (@@ROWCOUNT > 0)
BEGIN
SET NOCOUNT ON;
DECLARE @inserted_count INT;
DECLARE @deleted_count INT;
SELECT @inserted_count = COUNT(*)
FROM inserted;
SELECT @deleted_count = COUNT(*)
FROM deleted;
-- First scenario: 1 or more rows inserted and
-- no deletes = INSERT statement
IF (@inserted_count > 0) AND (@deleted_count = 0)
BEGIN
INSERT INTO ActionLog (TableName,ActionType,ActionDML,UserID,ActionDate)
SELECT N'Users',
N'INSERT',
N'INSERT INTO Users (User_fname, User_address) ' +
N'VALUES (N''' + REPLACE(User_fname, N'''', N'''''') + N''', N''' +
REPLACE(User_address, N'''', N'''''') + N''');',
USER_NAME(),
CURRENT_TIMESTAMP
FROM inserted;
END
-- Second scenario: no inserted rows and
-- 1 or more rows deleted = DELETE statement
ELSE IF (@inserted_count = 0) AND (@deleted_count > 0)
BEGIN
INSERT INTO ActionLog (TableName,
ActionType,
ActionDML,
UserID,
ActionDate)
SELECT N'Users',
N'DELETE',
N'DELETE FROM Users ' +
N'WHERE User_fname = N''' + REPLACE(User_fname, N'''', N'''''') + N''';',
USER_NAME(),
CURRENT_TIMESTAMP
FROM deleted;
END
-- Third scenario: 1 or more inserted rows and
-- 1 or more deleted rows = UPDATE statement
ELSE IF (@inserted_count > 0) AND (@deleted_count > 0)
BEGIN
INSERT INTO ActionLog (TableName,
ActionType,
ActionDML,
UserID,
ActionDate)
SELECT N'Users',
N'UPDATE',
N'UPDATE Users ' +
N'User_fname = N''' + REPLACE(User_fname, N'''', N'''''') + N''', ' +
N'User_address = ''' + REPLACE(User_address, N'''', N'''''') +
N''' ' + N'WHERE User_application_id = ' +
CAST(User_application_id AS NVARCHAR(10)) + N';',
USER_NAME(),
CURRENT_TIMESTAMP
FROM inserted;
END
END
END;
GO