SQL Server Performance

If clause in SQL Server trigger?

Discussion in 'General Developer Questions' started by Bill the Great, Jul 14, 2005.

  1. Bill the Great New Member

    Hi.. Just wondering if this is possible, and if so, the syntax..<br /><br />Here is the beginning of the existing trigger:<br /><br /><pre id="code"><font face="courier" size="2" id="code">ALTER trigger [TAN_TAB_upd_trg] on [TAN_TAB] instead of update<br />as<br />begin<br />etc....</font id="code"></pre id="code"><br /><br />Basically, I need to take a value in one of the fields of the incoming update statement that's getting intercepted, and if the field is a certain value, run the trigger... If not, then don't execute the trigger.<br /><br />Hope the above was clear.. hehe. Is this possible to do?<br /><br />I'm thinking something along the lines of:<br /><br /><pre id="code"><font face="courier" size="2" id="code">if [inserted].[NAME_FIELD] = 'WHATEVER VALUE'<br />ALTER trigger [TAN_TAB_upd_trg] on [TAN_TAB] instead of update<br />as<br />begin<br />etc...</font id="code"></pre id="code"><br /><br />Am I barking up the wrong tree??<br /><br />Many thanks for your help. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Bill
  2. benwilson New Member

    i think it should be something like this:

    ALTER trigger [TAN_TAB_upd_trg] on [TAN_TAB] instead of update
    as
    begin
    if [inserted].[NAME_FIELD] = 'WHATEVER VALUE'
    BEGIN
    Do something
    END

    ELSE
    BEGIN
    Do something else
    END


    Even then i think you may have to deal with the issue of rows being inserted in sets (correct me if i am wring!)- not sure what would happen in that situation! May need to use a cursor to go through each inserted row, or find a set based way to check which of the inserted rows meet your criteria (cant think how at this time of morning!).

    'I reject your reality and substitute my own' - Adam Savage
  3. silicon_master New Member

    Hi

    Well can you tell us why you want to alter the trigger everytime if the value of the name is matched..

    May be you need it but under what condition of the database execution as normally trigger is only altered when you want to change some logic in it & is executed on ant insert/del/up statements.

    Regards
    Samarth

    Silicon Master
  4. Bill the Great New Member

    Thanks for your help with this, guys... I'll try something along the lines of what you suggested, Ben.. it should work. I basically just wanted to get the proper syntax down.<br /><br />As to why I want to alter the trigger, I need to intercept every update statement sent to the table, update a field in it, then insert another record with the same values but with one field altered. So I wind up with 2 records in the table, but with one field having a different value.<br /><br />That part works fine... But now, someone else wants to update records in the table separately from my process, and they aren't interested in having duplicate records eating up table space. So I figure I need to insert an if statement to ensure the trigger is fired only if my process is doing the updating.. Which, conveniently, can be determined by the value of one of the fields in the incoming update statement.<br /><br />Once again, thank you for your assistance. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Bill
  5. Adriaan New Member

    The proper syntax is:

    IF EXISTS (SELECT * FROM inserted WHERE inserted.NAME_FIELD = 'WHATEVER VALUE')
    BEGIN
    END

    Make sure your trigger anticipates the fact that the "inserted" and "deleted" tables may contain multiple rows.

Share This Page