February 14, 2008 at 9:28 am
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;
February 14, 2008 at 12:23 pm
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?
triggers are distinct pieces of sql and can't really be shared between multiple tables. you could write a stored proc or some other routine to generate the trigger DDL to make auditing additional tables simpler.
you might also consider using seperate audit tables that are extended versions of the source table. for example, Users' audit table would be Users_log which would include all the columns of Users followed by actionType, userId, actionDate. its primary key would be same key as Users + actionDate. the audit trigger would then be
if (@@rowcount > 0)
begin
declare @action varchar(20), @inserted int, @deleted int
select @inserted = count(*) from inserted
select @deleted = count(*) from deleted
select @action = case when @inserted = @deleted then 'update'
when @inserted > 0 then 'insert' else 'delete' end
insert into Users_log
select *, @action, user_name(), getdate()
from inserted where @deleted = 0
union
select *, @action, user_name(), getdate()
from deleted
end
this approach preserves the entire row making it helpful if users are able to 'screw up' data. also, most audits preserve the original values (from deleted) when data is updated since the revised values are available in the table itself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply