Strange Delete Trigger Issue

Question

Post reply

Strange Delete Trigger Issue

Simon-220850

SSCrazy

Points: 2789
More actions
February 20, 2006 at 2:53 am

#365331

Hi,
I have a strange Delete Issue. I had an issue with records disappearing from a table, so I set up a trigger to capture basic user, pk and table audit information to another table. This appears on the face of it to work. Records are appearing in the audit table.
However I have found examples of records disappearing from this table and not appearing in the audit table
The trigger is :
CREATE TRIGGER member_role_delete ON [dbo].member_role
FOR DELETE AS
DECLARE @role_no as INTEGER
SET @role_no = (SELECT DELETED.role_no FROM DELETED )
IF @role_no IS NOT NULL
BEGIN
INSERT INTO tbl_Audit
VALUES ('member_role',@role_no,current_user,getdate())
END
Are there certain conditions that exist where the delete trigger is not run that could cause this
This is running on a SQL Server 7 Server.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Simon,

by passing the value of deleted.role_no from deleted into a variable you will only ever capture one value at a time.

As such, if a delete statement against member_role deletes more than one record (say 10) you'll only ever insert 1 record into the audit table and lose 9 others.

Try this instead:

CREATE TRIGGER member_role_delete ON [dbo].member_role

FOR DELETE AS

insert into tbl_audit

select 'member_role', deleted.role_no, current_user, getdate()

from deleted

GO

Hope that helps,

Karl

Karl
source control for SQL Server

Balmukund SSCertifiable Points: 6648 More actions · Answer 2

Perfect grambowk

Appending code to grambowk

CREATE TRIGGER member_role_delete ON [dbo].member_role

FOR DELETE AS

insert into tbl_audit

select 'member_role', deleted.role_no, current_user, getdate()

from deleted where deleted.role_no is NOT NULL.

GO

Simon-220850 SSCrazy Points: 2789 More actions · Answer 3

Doh! where's that hand slapping forehead emoticon. Well spotted guys - this could be the problem

Thanks

Simon