Re: About trigger

"hagar" <hagar_at_gcn.net.tw> wrote in message news:<9i5oo7$m9p_at_netnews.hinet.net>...
> Dear all:
>
> I have 2 table2 named table1 and table2. Both tables are the same.
> I add a trigger which are fired after Insert on table2. The function
> of trigger is: Where a record is inserted into table2, it lookup table1
> to see if table1 has the same record. If yes, it uses the values that
> is inserted into table2 to update the same record in table1. If no,
> it uses the values that is inserted into table2 to insert into table1.
> My trigger writee below:
> ¡_at_
> CREATE OR REPLACE TRIGGER trig_test
> AFTER INSERT ON TABLE2
> DECLARE
> ¡_at_x_Field1 VARCHAR2(3);
> ¡_at_x_Field2 VARCHAR2(6);
> BEGIN
> ¡_at_SELECT Field1, Field2 FROM TABLE2 INTO x_Field1, x_Field2;
> ¡_at_SELECT Field1, Field2 FROM TABLE1 WHERE Field1 = x_Field1;
> UPDATE TABLE1 SET Field2 = x_Field2 WHERE Field1 = x_Field2;
> EXCEPTION
> ¡_at_WHEN NO_DATA_FOUND
> ¡_at_¡@INSERT INTO TABLE1 VALUES(x_Field1, x_Field2);
> END;
> /
> ¡_at_
> Is it right? My problem is how to descirble the condition
> if the result of "select Field1, Field2 From Table1" is empty?
> Thanks all.
>
> hagar.

You do not need 'SELECT Field1, Field2 FROM TABLE2 INTO x_Field1, x_Field2;'.
The new values of Field1 and Field2 that are being inserted in the table TABLE2
shall be available to you as new.Field1 and new.Field2.

I could not understand what are you trying to say in 'My problem is how to descirble the condition if the result of "select Field1, Field2 From Table1" is empty?'. I think your code updates, in case the select is success or inserts in
case the record is not found..which looks fine, doesn't it?

Regards
Bhooshan Received on Sun Jul 08 2001 - 06:28:47 CEST