Archives of the TeradataForum
Message Posted: Mon, 18 Oct 2004 @ 11:44:10 GMT
| Subj: | | Re: Deleting Triggers |
| |
| From: | | Dieter Noeth |
David Clough wrote:
| | However, when I tried this on our development box, using this sort of syntax: | |
REPLACE TRIGGER DEVCS_T.BOOKINGORDER_TR1 AFTER DELETE ON
DEVCS_T.BOOKINGORDER
REFERENCING OLD AS o
FOR EACH ROW
(
DELETE FROM DEVCS_T.ORDERCONDETAIL ch1 WHERE ch1.ORD_SRKY_ID=o.ORD_SRKY_ID;
DELETE FROM DEVCS_T.ORDERCONDETOPT ch2 WHERE ch2.ORD_SRKY_ID=o.ORD_SRKY_ID;
);
| | it virtually brought the box to its knees (its V2R5). | |
| | It was trying to delete around 100k parent and its associated children. | |
Never use a Row Trigger if you can achieve the same with a Statement Trigger: Row Triggers are fired once for each *row* whereas Statement
Triggers are fired (just compare the explains) once per *statement*.
In your case it's comparable to:
bt;
delete from BOOKINGORDER;
.repeat 100000
DELETE FROM DEVCS_T.ORDERCONDETAIL ch1
WHERE ch1.ORD_SRKY_ID= :ORD_SRKY_ID
;DELETE FROM DEVCS_T.ORDERCONDETOPT ch2
WHERE ch2.ORD_SRKY_ID= :ORD_SRKY_ID;
et;
| | Has anyone used Delete Triggers for mass deletes in this way with any success? And if so, how did you code it (although I can't
imagine there's too many variations)? | |
Statement Triggers are usually more complicated to write, because you have to deal with sets of data, but your's so simple, one minor varition
is probably enough ;-)
Just modify OLD -> OLD_TABLE and FOR EACH ROW -> FOR EACH STATEMENT
REPLACE TRIGGER BOOKINGORDER_TR1 AFTER DELETE ON
BOOKINGORDER
REFERENCING OLD_TABLE AS o
FOR EACH STATEMENT
(
DELETE FROM ORDERCONDETAIL ch1 WHERE ch1.ORD_SRKY_ID=o.ORD_SRKY_ID;
DELETE FROM ORDERCONDETOPT ch2 WHERE ch2.ORD_SRKY_ID=o.ORD_SRKY_ID;
);
| | By the way, I did start out using Cascading Triggers but then thought that the approach above was much simpler. | |
As long as there are just a few triggers in your datamodel...
But the explain should be the same.
Dieter
| 
