Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to audit other tables using trigger Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2008 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2008 5:01 AM
Points: 12, Visits: 43
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;

Post #455008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse