DELETE ROW ON INSERT TRIGGER help required 1

[Delphiwhat]

Hi all

I have a table (table X) which holds order numbers.

When a row is inserted into table X I go and find data in another table Y using the order number.

I need a trigger that....

If the data is found in table Y then nothing happens

else

If data is not found in table Y then the row (that was just inserted) in table x should be deleted.

Could anyone point me in the right directection on how to creat this trigger.

Thanks in advance

steve

 

[hoinz]

Hi steve,

I wonder whether you will need this kind of trigger at all.
Did you consider a foreign key constraint? This might better meet your needs.

regards

 

[LKBrwnDBA]

And if you do need it, maybe your design is bad.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Delphiwhat]

An external application is populating Table x

I am basically looking for the order number that has just been populated in table x for existance in table y. If it exists do nothing otherwise delete the row that is applicable to the current order no.

with respect to design I am working with tables which cannot be changed. They were not designed by me nor do i have access or previledge to change them unfortunately. This includes adding keys etc

 

[LKBrwnDBA]

OK, you need an BEFORE INSERT trigger kinda like this:

Create Or Replace Trigger X_Bir
Before Insert On X For Each Row
Declare
If_Exist Char(1):='F';
Begin
  Select 'T' Into If_Exist
    From Y Where Order_Num = :New.Order_Num;
  Raise_Application_Error(-20001,'Order Number '||:New.Order_Num||' Exists!');
Exception
  When No_Data_Found Then
    Null;
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb