Post reply

update and insert trigger

  • January 21, 2009 at 5:18 pm

    #128163

    Hi i have 2 tables: tempo1 and tempo2, i need to move data from tempo1 to tempo2, if a row with some id already exist then it must be update, else it must be inserted, i once saw a trigger that did this by using try catch or a @@ variable, yet i cannot recall or find out how was this done, has anyone done or seen this before?

  • January 21, 2009 at 5:33 pm

    #930803

    you don't even need to check, really

    you simply do two steps in the trigger

    you update first, and if it exists, it gets updated. if it didn't exist, nothing gets touched.

    then you insert by left outer joining where the common key in the second table is NULL.

    --ie

    CREATE TRIGGER TR_WHATEVER

    FOR INSERT,UPDATE

    AS

    UPDATE TABLE2

    SET TABLE2.Value = INSERTED.Value

    FROM INSERTED

    WHERE INSERTED.ID = TABLE2.ID

    --then

    INSERT INTO TABLE2

    SELECT INSERTED.* FROM INSERTED

    LEFT OUTER JOIN TABLE2 ON INSERTED.ID = TABLE2.ID

    WHERE TABLE2.ID IS NULL

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 22, 2009 at 8:23 am

    #931202

    thanks a lot mate, that looks even better 😀

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply