March 29, 2011 at 1:07 pm
In SQL Server 2008, I am noticing that when a row is being updated somehow the After Delete trigger triggers. Is this normal behavior in Database? or is this a bug in SQL Server? It seems like whenever the update occurs, SQL Server deletes the row and inserts a new row???
Secondly, When the bulk rows are getting updated the INSERTED and DELETED table have multiple rows in it. My assumption is they should have only one row in them. Is this the bug as well?
Any help would be greatly apprecaited.
Thanks,
Vincy
March 29, 2011 at 1:19 pm
After Delete triggers won't run for an update command, unless there's another trigger that's doing an explicit delete action and the cascading triggers option is turned on.
The inserted and deleted tables will have ALL affected rows, by design. Oracle does it a different way, but SQL Server does it this way by design, so that set-based operations can be performed on all rows affected by the triggering transaction.
I just tested the "After Delete" trigger issue this way:
CREATE TABLE TriggerTest (
ID INT PRIMARY KEY);
GO
CREATE TRIGGER Test ON TriggerTest
AFTER DELETE
AS
PRINT 'Trigger executed';
GO
INSERT INTO TriggerTest (ID)
VALUES (1);
GO
UPDATE TriggerTest
SET ID = 2;
The trigger did not fire. When I explicitly deleted data from the table, the trigger fired as expected.
Check for more than one trigger on that table, or if the trigger fires for both updates and deletes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2011 at 1:20 pm
Vincy-856784 (3/29/2011)
In SQL Server 2008, I am noticing that when a row is being updated somehow the After Delete trigger triggers. Is this normal behavior in Database? or is this a bug in SQL Server? It seems like whenever the update occurs, SQL Server deletes the row and inserts a new row???
No, that's not normal. Are you absolutely sure that it's just a delete trigger? Not an AFTER DELETE, UPDATE?
Can you post the table, the trigger code and the update that fires it?
Secondly, When the bulk rows are getting updated the INSERTED and DELETED table have multiple rows in it. My assumption is they should have only one row in them. Is this the bug as well?
No, that's an incorrect assumption on your part. Triggers fire once per operation, not once per row.
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 29, 2011 at 1:35 pm
Okay, thank you for your responses.
I found this article after I posted my question here.
Take a look at this article and i was dead right... But its not a bug but its by DESIGN by Microsoft 🙂
http://msdn.microsoft.com/en-us/library/ms191300.aspx
Thanks,
Vincy
March 29, 2011 at 2:00 pm
Please, explain why the responses to your post are wrong.
I have read the article you reference and find nothing to support your arbitrary statement.
March 29, 2011 at 2:02 pm
That article completely agrees with what both Gail and I posted. In what way are "[we] all wrong"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2011 at 5:02 pm
i apologize. I edited my post.
What it turns out to be is you have to use separate triggers then only it won't trigger for After Delete but it moves the row to DELETED during update.
So, if you have trigger with AFTER UPDATE, DELETE and if you're depending on INSERTED and DELETED then you're screwed.
Thanks,
March 29, 2011 at 11:54 pm
Vincy-856784 (3/29/2011)
What it turns out to be is you have to use separate triggers then only it won't trigger for After Delete but it moves the row to DELETED during update.
Yes, it does. By Design. Old values in deleted, new values in Inserted. I'm pretty sure that's clearly documented (Books Online). And you don't have to use separate triggers.
So, if you have trigger with AFTER UPDATE, DELETE and if you're depending on INSERTED and DELETED then you're screwed.
Not at all. I write triggers FOR INSERT, UPDATE, DELETE and I can use inserted and deleted to see what happened.
Rows in Inserted only: Insert
Rows in Inserted and Deleted: Update
Rows in Deleted only: Delete.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply