Why would a DELETE trigger cause an UPDATE trigger to fire? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Why would a DELETE trigger cause an UPDATE trigger to fire?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-19 : 08:42:29

Gordon writes "I am using SQL Server 2000 with SP3 and ASP.NET 2003.

I am trying to create an audit trail using the following triggers. But when I try to delete a record in the datagrid control, the UPDATE trigger would fire right after the DELETE trigger for all the records in the table. For example, if I have 10 records and I am deleting one, the audit table would show one DELETED record and nine UPDATED records. Am I missing something on these triggers? Any suggestions on solving this issue would be greatly appreciated.

********************************
CREATE TRIGGER [delete_education_level] ON education_level
FOR DELETE
AS
INSERT audit_education_level (education_level_id, seq_number, description,
label, action_date, action_taken, users_id, modifier)
SELECT education_level_id, seq_number, description,
label, GETDATE(),
'DELETE', user_id, users.username
FROM deleted
INNER JOIN users ON users.users_id = user_id
********************************

CREATE TRIGGER [update_education_level] ON education_level
FOR UPDATE
AS
INSERT audit_education_level (education_level_id, seq_number, description,
label, action_date, action_taken, users_id, modifier)
SELECT education_level_id, seq_number, description,
label, GETDATE(),
'UPDATE', user_id, users.username
FROM inserted
INNER JOIN users ON users.users_id = user_id
********************************

CREATE TRIGGER [insert_education_level] ON education_level
FOR INSERT
AS
INSERT audit_education_level (education_level_id, seq_number, description,
label, action_date, action_taken, users_id, modifier)
SELECT education_level_id, seq_number, description,
label, GETDATE(),
'INSERT', user_id, users.username
FROM inserted
INNER JOIN users ON users.users_id = user_id
********************************"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-19 : 09:27:38

I take it you are using a bound control (yuk)
Try using the profiler to see what the control is sending - sounds like it is sending an update after the delete.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources