Post reply

Trigger to execute dbmail based on cloumn update.

  • July 9, 2009 at 10:16 am

    #95824

    SSC,

    SQL Server 2K5.

    I am workiing on creating a trigger to email a customer rep when a PO date column gets updated.

    Details:

    Order header table has a PO number column and a created user column (userid). PO table has a PO date. User table has email address and userid coulmns.

    When the PO date column gets updated I want my trigger to email the created user an email notice to call the customer and tell them that their order will be late.

    This is what I have so far:

    CREATE TRIGGER xut_promdatechange

    ON purorddet

    AFTER UPDATE

    AS

    DECLARE @@email varchar(255),

    @@ordnbr varchar(15)

    select @@email = c.EMailAddress, @@ordnbr = a.ordnbr

    from soheader a inner join updated b

    on a.custordnbr = b.ponbr

    inner join testmlsystem.dbo.Userrec c

    on a.Crtd_User = c.userid

    IF UPDATE(promdate)

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @@email,

    @query = 'SET QUOTED_IDENTIFIER OFF

    select "The PO date has been changed for order "+ordnbr".

    Please contact the customer with the new date"

    from soheader where ordnbr = @@ordnbr',

    @subject = 'Purchase order update';

    GO

    Can anyone help me with what I'm doing wrong?

    Thanks!

  • July 9, 2009 at 12:08 pm

    #1022587

    First off, what is the problem? Is the trigger not working correctly or not working at all? Second, some sample data would help in solving the problem. Please read the link in my signature.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • July 9, 2009 at 12:18 pm

    #1022592

    Duplicate post. No replies to this thread please. Direct replies to:http://qa.sqlservercentral.com/Forums/Topic750337-145-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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