Data compare on insert problem

[lpedgin2]

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!!

[LKBrwn_DBA]

You may need two statements:

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

[lpedgin2]

How can I do this with a trigger?

[PAVB]

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.