Can this Trigger be done?

  • I've worked with INSERT, UPDATE and DELETE triggers a little, but am not sure if the following can be done:

    In an AFTER INSERT trigger,

    I need to test if a record exists

    If it does, UPDATE it

    If it doesn't exist, INSERT it

    I'm thinking first I need to do a comparison between the two tables, but am not sure if that would be a SELECT involving a record count test or what ?

    All advice would be much appreciated.

    Beth L

  • Try something like the following...

    IF EXISTS(SELECT * FROM...)

    BEGIN

    put your update here

    END

    ELSE

    BEGIN

    put your insert here

    END

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • Can multi records be involved? If multi then some records may exist and some may not exist in the second table

  • You need to write the trigger as if multiple records exist, so do the check as a join between the "inserted" table and the other table and perform the update the same wya. DO NOT USE variables to capture data since they will not handle multiple records.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • Thanx for the responses.

    I will need to check for multiple records, so I figure I'll have to use a loop somewhere. I'm used to VB/VBA syntax, but should be able to use the Books On-Line to figure that part out. If not, I'll be back 🙂

    Beth

Viewing 5 posts - 1 through 4 (of 4 total)

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