Update trigger help.

  • I want to send an email using a trigger whenever a Customer has 1 of 3 fields updated. The table is not updated that frequently so I don't mind using a trigger. I don't think I understand the syntax in BOL. Can someone help me please? I know how to send an email when a new Customer is added to the table, but the syntax for the Update is escaping me.

    Edit: I almost forgot, I want to put the old and new values into the email so that it would read something like this: "Customer AAA status has changed from (old value) to (new value)."

  • Hope this will helpful for you to find a better solution.

    Use COLUMNS_UPDATED() to check whether perticular column has updated or not.

    You can use ‘deleted’ virtual table to capture old values and ‘inserted’ table to capture new values.

    Looks for BOL for more details.

    Susantha

  • I was using BOL as a basis for my syntax, but it's not clicking for me. I don't understand what BOL is trying to say.

  • Lets assume if you want to check whether any of the columns 2,3,4 or all the columns have been updated or not. Then use the following IF condition

    IF (COLUMNS_UPDATED() & 14) > 0

    14 is the bitmask for all three columns.

    for col 2, (this is col id you can see in syscolumns table) bitmask is power(2,(2-1))=2

    for col 3, bitmask is power(2,(3-1))=4

    for col 4, bitmask is power(2,(4-1))=8

    In above ex, since your checking all there columns bitmask is the sum of all. That comes to 14.

    Let me know if you need more help.

    Susantha

  • I do the trigger part like this. Not sure about the email.

    CREATE trigger [dbo].[update_trigger] on [dbo].[MyTable] for UPDATE

    as

    if

    UPDATE

    (Field1) or

    update

    (Field2)

    begin

    insert into trigger_audit_table

    (

    Field1,

    Field2,

    Field3,

    Field4,

    Field5,

    Field6,

    Field7,

    Field1_Before,

    Field2_Before,

    AUDIT_TYPE,

    AUDIT_DATE

    )

    select

    Ins.Field1,

    Ins.Field2,

    Ins.Field3,

    Ins.Field4,

    Ins.Field5,

    Ins.Field6,

    Ins.Field7,

    Del.Field1,

    Del.Field1,

    'UPDATE',

    getdate()

    from inserted Ins inner join deleted Del

    on Ins.KeyField = Del.KeyField

    where Ins.Field1 <> Del.Field2

    or Ins.Field2 < 0

    end

  • Hey homebrew, that works pretty good. I also got the email to work when I check for 1 field, but not for multiple fields. But what if I'm checking 2 separate fields for change and would send 2 different emails? Do I have to write 2 separate triggers? I would think that I could put an 'OR' statement in there, like this (it looks lengthy, but skip over the joins and get down to the 'IF' statement):

    CREATE TRIGGER tr_MyTrigger

    ON MyTable

    AFTER UPDATE

    AS

    DECLARE @DealID INT

    DECLARE @Deal VARCHAR(40)

    DECLARE @oldStatus VARCHAR(10)

    DECLARE @newStatus VARCHAR(10)

    DECLARE @oldEmployee VARCHAR(30)

    DECLARE @newEmployee VARCHAR(30)

    DECLARE @textStatus VARCHAR(250)

    DECLARE @textEmployee VARHCAR(250)

    SET @DealID = (SELECT (DealID) FROM Deleted)

    SET @Deal = (SELECT (Deal) FROM MyTable WHERE DealID = @DealID)

    SET @oldStatus = (SELECT DS.DealStatus FROM Deleted D INNER JOIN DealStatuses DS ON DS.DealStatusID = D.DealStatusID)

    SET @newStatus = (SELECT DS.DealStatus FROM Inserted I INNER JOIN DealStatuses DS ON DS.DealStatusID = I.DealStatusID)

    SET @oldEmployee = (SELECT E.Employee FROM Deleted D INNER JOIN vwEmployeesALL E ON E.EmployeeID = D.EmployeeID)

    SET @newEmployee = (SELECT E.Employee FROM Inserted I INNER JOIN vwEmployeesALL E ON E.EmployeeID = I.EmployeeID)

    SET @textStatus = 'Deal ' + @Deal + ' has been changed from ' + @oldStatus +' status to ' + @newStatus + ' status.'

    SET @textEmployee = 'Deal ' + @Deal + ' has been moved from ' + @oldEmployee +' to ' + @newEmployee

    IF (UPDATE (DealStatusID))

    EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',

    @subject = @textStatus,

    @body_format = 'HTML'

    OR

    (UPDATE (ClosingEmployeeID))

    EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',

    @subject = @textEmployee,

    @body_format = 'HTML'

  • You essentially want to write those as two separate IF statements. Depending on whether you want someone to get just one e-mail, or possibly several if the row had both columns updated, you'd then add an ELSE or not.

    So - if you want them to get 2 emails if BOTH columns were updated then :

    ...

    IF (UPDATE (DealStatusID))

    EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',

    @subject = @textStatus,

    @body_format = 'HTML'

    IF (UPDATE (ClosingEmployeeID))

    EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',

    @subject = @textEmployee,

    @body_format = 'HTML'

    if you only want them to get just one, then:

    IF (UPDATE (DealStatusID))

    EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',

    @subject = @textStatus,

    @body_format = 'HTML'

    ELSE

    IF (UPDATE (ClosingEmployeeID))

    EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',

    @subject = @textEmployee,

    @body_format = 'HTML'

    You could also change the second one around by combining into a single IF with a CASE statement, but I don't think it buys you anything and is less readable IMO.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks Matt. Of course 10 minutes after I posted this, I figured out the multiple IF statement solution. Sometimes I feel like such a dork.

  • SQLWannabe (6/3/2008)


    Thanks Matt. Of course 10 minutes after I posted this, I figured out the multiple IF statement solution. Sometimes I feel like such a dork.

    Welcome to the club !

  • sometimes is good to warn people about "sending emails from triggers may not be what you want" 😉


    * Noel

  • SQLWannabe (6/3/2008)


    Thanks Matt. Of course 10 minutes after I posted this, I figured out the multiple IF statement solution. Sometimes I feel like such a dork.

    I get to experience that feeling several times a day..:D

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • noeld (6/3/2008)


    sometimes is good to warn people about "sending emails from triggers may not be what you want" 😉

    Yes - I really think they should be warned ....

    You could really screw performance up by sending emails directly from a trigger - much better to use the trigger to just add a row to some "data to email" table - which would have something like a PROCESSEDDATE field in as well - then all the rows could be processed with a scheduled job (scheduled as often as you need) that goes through the rows in the "data to email" table and performs the emails then sets PROCESSEDDATE to GetDate() - that way you don't trash the realtime performance of your system, can properly recover from email outages (i.e. if the emails fail to send just don't set PROCESSEDDATE etc etc ... also with a bit more thought where two or more fields have changed you can make it send the info of all the changes for the same email address in one email - keeping down the amount of emails you balst out - and I am sure anybody who receives email would appreciate this consolidation as it helps keep that inbox slim

  • Right. And there is more. Such trigger would fail on multirow updates, so for proper work you'd have to open a cursor.

    If the trigger instead of sending emails just inserts into queue table, you have a simple set based solution instead of cursor.

  • Hence my reasoning for stating in my original post that this is not a table that will get inserted or updated very often.

Viewing 14 posts - 1 through 13 (of 13 total)

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