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.
Thanks and regards,
P.S. - I am sorry to have posted so many threads here - that happened upon multiple post-backs when I got a screen for dbforums database error. I wonder if the duplicates can be delete - there is no option for that - I hope the moderators could help me with this. Again, I apologize. Regards.
This is not work because, your database have logging and until now you not close the transaction with commit work.
I agree with Artemka, create one store procedure, then first check select condition, then insert or not.
Thanks a lot for your responses. I finally got this working with a stored procedure. There were problems with both the syntax and logic (as you rightly pointed out). But I have to admit that Informix really is terrible. I am facing so lot of difficulties with it that I could do easily with Sybase or MS SQL Server or even Oracle.. I mean.. it doesn't even allow an IF NOT EXISTS (<select query>) statement and exception handling in triggers.