May 13, 2010 at 1:34 am
Hi,
How can i save the details of who deleted data from Test table to an audit table, including the Code parameter passed when executing a stored proc?
CREATE procedure sp_DeleteTest
(
@Code varchar(20)
)
as
DELETE FROM dbo.Test
WHERE Code = @Code
COMMIT
May 13, 2010 at 1:49 am
You have to make use of Trigger for auditing purpose:
A sample trigger code:
Create an audit Table:
CREATE TABLE dbo.Audit
(
ID INT,
[Login Name] VARCHAR(1000),
[SESSION USER] VARCHAR(1000),
[User Name] VARCHAR(1000),
[APP_NAM] VARCHAR(1000),
[PROC ID] INT
)
Write a Trigger code over the table you wish to audit:
CREATE TRIGGER TRG_AUDT_DELETE
ON dbo.<YourTableName>
AFTER DELETE
AS
INSERT INTO dbo.Audit (
ID
,[Login Name]
,[SESSION USER]
,[User Name]
,[APP_NAM]
,[PROC ID]
)
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name'
, SESSION_USER AS 'SESSION USER' ,USER AS 'User Name' , APP_NAME() AS APP_NAM
, OBJECT_NAME(@@PROCID) 'PROC ID'
GO
To capture the deleted rows, u have to create another table and inside the Trigger , utilize "DELETED" tables to get the deleted rows' information!
Hope this helps!
Cheers!
May 13, 2010 at 7:51 am
you can also use the output clause if the data is accessed through stored procedures. It would have to be added to each proc where the data is modified and wouldn't protect you from direct changes to the table, but it is an alternative
declare @t table (field1 int)
declare @Audit table (field1 int, SessionUser varchar(30))
insert into @t
output Inserted.field1, suser_name() into @Audit
values (1)
insert into @t
output Inserted.field1, suser_name() into @Audit
values (2)
delete from @t
output deleted.field1, suser_name() into @Audit
where field1 = 1
select * from @t
select * from @Audit
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 13, 2010 at 11:43 pm
Thank you very much. The advices worked.
May 31, 2011 at 10:10 am
Thanks a lot... it works for us!! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply