From: "hagar" <hagar@gcn.net.tw>
Newsgroups: comp.databases.oracle
Subject: About trigger
Date: Sat, 7 Jul 2001 09:32:03 +0800
Organization: DCI HiNet
Lines: 32
Message-ID: <9i5oo7$m9p@netnews.hinet.net>
NNTP-Posting-Host: 211.22.3.181
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-Mimeole: Produced By Microsoft MimeOLE V5.50.4522.1200


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:
¡@
    CREATE OR REPLACE TRIGGER trig_test
    AFTER INSERT ON TABLE2
    DECLARE
    ¡@x_Field1 VARCHAR2(3);
    ¡@x_Field2 VARCHAR2(6);
    BEGIN
    ¡@SELECT Field1, Field2 FROM TABLE2 INTO x_Field1, x_Field2;
    ¡@SELECT Field1, Field2 FROM TABLE1 WHERE Field1 = x_Field1;
      UPDATE TABLE1 SET Field2 = x_Field2 WHERE Field1 = x_Field2;
    EXCEPTION
    ¡@WHEN NO_DATA_FOUND
    ¡@¡@INSERT INTO TABLE1 VALUES(x_Field1, x_Field2);
    END;
    /
¡@
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.