Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Triggers (SQL Enterprise)

    Hi all,

    I am having a bit of trouble with my trigger (see below), is there a way of identifing any column update i notice
    that * does not work at the point 'IF UPDATE (*) but I have many columns and don't know the best way to write in multiple columns

    Thanks

    CREATE TRIGGER trg_Update_tbl_Discharge ON [dbo].[tbl_Discharge]
    WITH ENCRYPTION
    FOR UPDATE
    AS
    IF UPDATE (CRN)
    BEGIN
    UPDATE tbl_Discharge SET Last_Update_Date = Current_Timestamp
    END
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Triggers (SQL Enterprise)

    If you want to run the statement when any column is updated, you can use

    IF COLUMNS_UPDATED() > 0

    COLUMNS_UPDATED() returns a number where the first column contributes 1 if modified, the second column 2, the third 4, the fourth 8 etc. So if ANY column has been modified, COLUMNS_UPDATED() will be > 0.

    If you want to check whether any of the first, third and fourth columns have been modified, you'd use

    IF (COLUMNS_UPDATED() & 13) > 0

    (since 13 = 1 + 4 + 8)

    If you want to check whether the first, third and fourth columns have all been modified, you'd use

    IF (COLUMNS_UPDATED() & 13) = 13

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Triggers (SQL Enterprise)

    Thats Great Hans Thanks,

    Is it also possible to put use an UPDATE and INERT trigger in one trigger. i.e. If INSERT THEN do X else if Update then do Y

    Kind Regards
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Triggers (SQL Enterprise)

    As far as I know, you can't specify different actions for UPDATE and INSERT in a single trigger, so you'd have to create a separate trigger (with a different name) to handle insertions.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='HansV' post='760665' date='Feb 23 2009, 10:36 AM']As far as I know, you can't specify different actions for UPDATE and INSERT in a single trigger, so you'd have to create a separate trigger (with a different name) to handle insertions.[/quote]

    That's my understanding as well. If however the purpose of the INSERT trigger is to set a timestamp as is being done with the UPDATE trigger, you can set a default value for that column of GetDate().
    Wendell

Posting Permissions

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