Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    38

    Unanswered: insert into table select inside trigger

    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:
    Code:
    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

    **EDIT**
    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.
    Last edited by exterminator; 01-30-06 at 16:54.

  2. #2
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    Have the trigger call a stored procedure that will do what you are trying to do. You can also do error handling inside stored proc

    Cheers

  3. #3
    Join Date
    Jan 2006
    Posts
    38
    Quote Originally Posted by artemka
    Have the trigger call a stored procedure that will do what you are trying to do. You can also do error handling inside stored proc
    So, is it not do-able in a trigger?

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi Exterminator,

    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.

    Gustavo.

  5. #5
    Join Date
    Jan 2006
    Posts
    38
    Hi Gurey and Artemka,

    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.

    Thanks again for your replies. Best regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •