July 9, 2009 at 10:16 am
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
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.
July 9, 2009 at 12:18 pm
Duplicate post. No replies to this thread please. Direct replies to:http://www.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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply