set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [Trg_UsersChangeAudit]
ON [dbo].[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_name, User_fname, User_lname, User_gender, User_address, User_hp_no, User_home_no,
User_email, User_last_activity, User_password ) ' +
N'VALUES (N''' + REPLACE(User_name, N'''', N'''''') + N''', N''' +
REPLACE(User_fname, N'''', N'''''') + N''', N''' +
REPLACE(User_lname, N'''', N'''''') + N''', N''' +
REPLACE(User_gender, N'''', N'''''') + N''', N''' +
REPLACE(User_address, N'''', N'''''') + N''', N''' +
REPLACE(User_hp_no, N'''', N'''''') + N''', N''' +
REPLACE(User_home_no, N'''', N'''''') + N''', N''' +
REPLACE(User_email, N'''', N'''''') + N''', N''' +
REPLACE(User_last_activity, N'''', N'''''') + N''', N''' +
REPLACE(User_password, 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 VARCHAR(100)) + N';',
USER_NAME(),
CURRENT_TIMESTAMP
FROM inserted;
END
END
END;