Unanswered: insert into table select inside trigger
I am struggling with the syntax (or probably logic, I am not sure) of writing triggers in Informix 9.3.
Here is what I am trying to do - I have two tables tableA and tableB. Both are kind of similar and for this matter you may consider them to be exactly the same.
I want to put a trigger on AFTER insert into tableA that would be inserting records into tableB if two fields (which are common to both tables) of tableA do not match with any records into tableB. Here is what I have:
CREATE TRIGGER mytrigger ON INSERT tableA AFTER
REFERENCING NEW as newrecord
insert into tableB (column1, column2)
select column1, column2 from tableA
where NOT EXISTS
select column1, column2 from tableB WHERE
Here column1 and column2 are indentical columns in tableA and tableB. And I only want go ahead with an insert of data (being inserted in tableA) that is not there in tableB. You may for now consider that these two tables are completely identical and have same structure. How would I go ahead with this (syntax is important for me)? Currently this gives syntax error - sometimes saying that a view cannot be updated... (I don't remember the exact error message at the moment.. can update you on that tomorrow). Or is there any easier (alternative, better/efficient) way to go about? I also tried putting a left join on two tables but even that ended in a syntax error.
Another thing I was wondering about - Is it possible to handle a insert failure in a trigger silently so that it does not throw an error back to the event that called the trigger?
I have really gone crazy with informix - any help would be greatly appreciated.