Data compare on insert problem

I have data I am importing from one table into another table. Before I do the inserts, I want to validate certain rules (does record already exist, etc) and send that message back to the table I am inserting from. In the example below, I am inserting from table A to table B and recording failures in table A

Table A consists of 3 columns:
recordID, recordtag, modifieddate, processed

Table B consists of 3 columns:
recordID, recordtag, modifieddate

if a record from table a already exists in table b, I want to send a message of "not processed: already exists" to table A's Processed column.

There are multiple rules I want to process prior to insert. How do I do this?

thanks in advance!!

You may need two statements:

Code:

---

UPDATE tablea a
  SET processed = 'Not processed: already exists'
 WHERE EXISTS (SELECT '?'
                FROM tableb b
                WHERE b.recordid = a.recordid);

INSERT INTO tableb
  SELECT a.recordid, a.recordtag, SYSDATE
    FROM tablea a
    WHERE NOT EXISTS (SELECT '?'
                        FROM tableb b
                      WHERE b.recordid = a.recordid);

---

:rolleyes:

How can I do this with a trigger?

Could you please better describe the process like...
... where are you planning to put the trigger?
... what is going to trigger the trigger?

I think you are pretty close of finding yourself facing mutating table syndrome, just a guess.