Archives of the TeradataForum

Message Posted: Mon, 18 Oct 2004 @ 10:16:05 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Deleting Triggers

From:		David Clough

I'm considering using Cascade Delete for a number of related Order tables. What I want to do it to delete a number (probably in the low thousands) on Parent entries and for all its dependents to also get deleted - hence Cascade Delete.

My question is, has anyone experienced any significant down side to applying Cascade Delete, such as a significant slow down in Inserts, etc?

Dieter replied (and I'm grateful to Dieter)

You mean a Foreign Key with "on delete cascade"? You can't specify that in Teradata, there's only the default "on delete restrict". The only way to achieve this is a Trigger and that won't affect Inserts...

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.

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)?

By the way, I did start out using Cascading Triggers but then thought that the approach above was much simpler.

Dave Clough
Database Designer
Database Design Group


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference