how to audit other tables using trigger

• ejoeyz_85

  SSC Enthusiast

  Points: 154

  More actions

  February 13, 2008 at 6:36 am

  #179193

  i have a problem with my trigger.. I have create my trigger to audit Users Table. The problem is, i want use the same trigger to audit other tables... what shud i do? below is my trigger to audit users table. I want to audit another table that is role table.. role table have role_application_id, role_name and role_description.. so any suggestion how to add the command inside the trigger below?

  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;