May 15, 2002 at 11:32 pm
I can't understand: in my SQL7sp3 trigger fires for each row, updated by a multirow UPDATE statement and each time "inserted" and "deleted" tables contains 1 row. This behavior differs from documented in BOL! Can I determine how should trigger fire? (Analogue in Oracle - "trigger" and "trigger for each row")
May 16, 2002 at 4:55 am
I am not sure I follow are you saying that your trigger fires and does not handle all the updates in a multirow UPDATE or does, can you explain better? Also post the trigger code as it may help us in understanding why you get the results you do.
As for trigger it fires per event not row. So a multirow update where it was part of 1 update query will (or should) fire once only. However you can write a trigger that handles all without worry.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 16, 2002 at 5:13 am
Excuse me my English - it not native for me.
Table PodrTree - hierarchical structure with a fields ID, ParentID and ILevel, where ILevel is number of hierarchical level of node. Trigger updates all ILevel of children when ILevel of theirs parent.
Alter Trigger dbo.PodrTree_TriggerOnUpdateILevel
On dbo.PodrTree
For Update
As
declare @children int
if update(ILevel)
begin
set @children=(select count(*) from PodrTree, inserted where PodrTree.ParentID=inserted.ID)
if @children=0 return
UPDATE PtCh
SET PtCh.ILevel=I.ILevel+1
FROM Inserted AS I JOIN PodrTree AS PtCh
ON PtCh.ParentID = I.ID
end
nested triggers set on server and recursive triggers set on database.
All work fine. But I cannot understand - UPDATE inside the trigger is multirow, but trigger fires for the each child in the tree, that is all works fine. But shouldn't?
May 16, 2002 at 5:52 pm
Ok I am still not fully sure I understand what is happening. Can you post the details of the PtCh table and give me and example of what is happening and what you expect to happen?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 16, 2002 at 7:07 pm
In SQL, the trigger fires once per transacitons. So if you have a multi-row update, you get one trigger firing. The inserted and deleted contain all the affected rows.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply