insert into table select inside trigger

[exterminator]

Hi,

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
tableB.column1!=newrecord.column1
AND
tableB.column2!=newrecord.column2
)
)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,
Exterminator

[ITGURU]

Dear Abhishekh,

Please change your trigger code as per following logic (please check the syntax because I have written code as per SQL Server)

Code:
CREATE TRIGGER mytrigger ON INSERT tableA AFTER
REFERENCING NEW as newrecord
(
IF (NOT EXISTS(select column1, column2 from tableB WHERE
tableB.column1 = newrecord.column1
AND
tableB.column2 = newrecord.column2))
BEGIN
insert into tableB (column1, column2)
SELECT column1, column2 FROM newrecord
END
)

Hope this will solve u r problem.

[exterminator]

Thanks for your prompt reply, Gurdarshan. I sorted the issue out.
Please change your trigger code as per following logic (please check the syntax because I have written code as per SQL Server)
It was an Informix specific issue - they don't allow exception handling inside triggers (or if they somehow do - they are exceptionally poor) and you cannot use IF NOT EXISTS in informix... that is an invalid statement.

I put the logic into a stored procedure instead.. and used INSERT INTO ... SELECT * from ... WHERE NOT EXISTS (<sub query>). It worked fine. Thanks again for your reply. Regards.

[veasnamuch]

Is there any posibility to do this way with SQL Server 2000?

Best regards,

Veasna

[Krzemo]

Currently this gives syntax error - sometimes saying that a view cannot be updated...

Informix .... it was so long ago....
But i remember that this error is connected to that U cannot modify and select the same table in the same time....
One thing is that U don't use "for each row" so newrecord is a table rather than a row.

Another thing is that U are not using newrecord in a proper way.. maybe I am wrong but shouldn't it looks like:
insert into tableB (column1, column2)
select column1, column2 from newrecord
where NOT EXISTS
(
select column1, column2 from tableB WHERE
tableB.column1!=newrecord.column1
AND
tableB.column2!=newrecord.column2
)

If it still generates an error (from the reason I stated before) U can try to select results to temporary table, and than insert as select from that temp (or maybe U find other way to select that rows without hitting tableB).

Hope that it helps.
Krzemo.

[mmetzger]

Is there any posibility to do this way with SQL Server 2000?

Best regards,

Veasna

Yes, there are AFTER triggers in SQL 2000. You just need to be careful about making sure not to add a row in the same table or you get endless recursive behavior. Just look up TRIGGERS in the SQL BOL (Books online)

[exterminator]

SQL Server only has AFTER triggers... (and istead of triggers - thanks mmetzger for correcting me :) )

Krzemo... aah.. I dont know how this thread got revived because I had actually solved this issue by using a temp table and a stored procedure...

Informix is really poor is many stuff - this is my 3-4 months of experience with it speaking..:D I know I may be wrong.. but this is my personal experience as of now.. or may be I have been working with SQL Server since beginning that I am finding the syntaxes aweful... I struggle with them a lot.. but things are getting better...

Another demerit is the poor documentation by IBM.. they don't show good samples... It's even tough to find out using google. All comes up on results is eSQL specific code samples and libraries.. anyway, that is how it has been going .. best of luck to me for the time that I will have to be stuck with it... :) :wave: