March 19, 2009 at 10:42 am
Hi,
I create a trigger AFTER DELETION, which will copy the records deleted to another table along with some other information such as UserID of the person who deleted it and also the SQL Code which ran.
My SQL was: DELETE from tblInvitationLog WHERE userID=99999
In the tblInvitationLog, I have 3 records belong to user 99999
The trigger did work, however it failed for the 2 following things:
1> Number of records deleted copied to the other table. It only copy the first deleted records instead of all deleted records (3). Why ? Is not supposed that the trigger "be called" on every single row deleted ?
2> It also failed to retrieve the SQL statement that I wrote to delete the records.
*** Extra for 1> : I also added FOR EACH ROW after ALTER Trigger trg ON TABLE tblInvitationlog AFTER DELETION ON EACH ROW. But it failed to run.
Here is my trigger script, please help me to fix it. Thanks.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*
Subject: DDL Triggers
Posted by: Thang Nguyen
Posted on: Thursday, March 12, 2009 at 1:21PM
My aproach was to log the time, and the user who deleted the record in tblInvitationLog every time the trigger detects changes to the database.
I also make a copy of record deleted into the temp table tblInvitation_DELETE to be able to keep the OwnerID.
This trigger will be running temporarily in the DB until we found the problem to fix it.
*/
ALTER TRIGGER [trgDeleteInvitationLog_Thang] ON [dbo].[tblInvitationlog]
AFTER DELETE
--FOR EACH ROW
AS
-------------------------- INSERT DELETED RECORD into TABLE tblInvitationLog_DELETE
DECLARE @user-id int;
DECLARE @PublicationID int;
DECLARE @IssueID int;
DECLARE @InviteDat datetime;
DECLARE @InvitedBy int;
DECLARE @DeleteDat datetime;
DECLARE @Host varchar(100)
DECLARE @DBName varchar(50)
DECLARE @Obj varchar(100)
DECLARE @ByUser varchar(10)
DECLARE @SPID varchar(50)
DECLARE @sql varchar(800)
DECLARE @raisedEventData XML
SET @raisedEventData = eventdata()
SET @sql = LEFT(cast(@raisedEventData.query ('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS varchar(max)), 800)
--- This @sql did not capture anything
SELECT @Host = Host_name from sys.dm_exec_sessions
WHERE session_id = cast(cast(@raisedEventData.query ('data(/EVENT_INSTANCE/SPID)') AS varchar(100)) AS int)
SET @DBName=cast(@raisedEventData.query ('data(/EVENT_INSTANCE/DatabaseName)') AS varchar(50))
SET @Obj=cast(@raisedEventData.query ('data(/EVENT_INSTANCE/ObjectName)') AS varchar(100))
SET @ByUser=CURRENT_USER
SET @SPID=cast(@raisedEventData.query ('data(/EVENT_INSTANCE/SPID)') AS varchar(50))
SELECT @user-id=d.UserID from DELETED d;
SELECT @PublicationID=d.PublicationID from DELETED d;
SELECT @IssueID=d.IssueID from DELETED d;
SELECT @InviteDat=d.InviteDat from DELETED d;
SELECT @InvitedBy=d.InvitedBy from DELETED d;
INSERT INTO tblInvitationLog_DELETE(UserID, PublicationID, IssueID, InviteDat, InvitedBy, DeleteDat, DBName, Obj, ByUser, SPID,SQLCode)
values(@UserID, @PublicationID, @IssueID, @InviteDat, @InvitedBy,getdate(),
@DBName, @Obj, @ByUser, @SPID,@SQL);
SELECT UserID, PublicationID, IssueID, InvitedDat, InvitedBy, getDate(), @DBName, @Obj, @ByUser, @SPID, @sql
INTO tblInvitationLog_DELETE
FROM tblInvitationLog
WHERE UserID=@UserID
--PRINT ('DELETE RECORDS RETRIEVED')
March 19, 2009 at 10:50 am
the reason for this is because there is only one insert statement which is using the values key word.
If you delete 3 rows from the table in one statement the trigger will only fire once.
Have you tried this for your insert
INSERT INTO tblInvitationLog_DELETE(UserID, PublicationID, IssueID, InviteDat, InvitedBy, DeleteDat, DBName, Obj, ByUser, SPID,SQLCode)
SELECT UserID, PublicationID, IssueID, InviteDat, InvitedBy, GETDATE(), @DBName, @Obj, @ByUser, @SPID,@SQL
FROM DELETED
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 10:53 am
Also, FOR EACH ROW is used in Oracle, not in Microsoft SQL Server.
March 19, 2009 at 2:54 pm
Christopher, your code worked wonderful.
My question is, why the SQL code was not retrieved by my code ?
DECLARE @raisedEventData XML
SET @raisedEventData = eventdata()
SET @sql = LEFT(cast(@raisedEventData.query ('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS varchar(max)), 800)
--- This @sql did not capture anything
March 20, 2009 at 1:14 am
EventData is used in DDL and logon triggers, not DML triggers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2009 at 11:04 am
Dear,
How do I write my trigger to be able to retrieve the SQL statement that used to delete the records ? This is very crucial for me in order to find out why my invitation records mysteriously disappeared sometime just one day after I sent them out. Any other advice of find out who/which process did it and/or from which computer/server will be welcome. Thanks a lot.
I did check the Application Log and Security Log, but could not find any helpful events.
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply