July 9, 2009 at 8:57 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:13 pm
Your problem is that you are referencing the updated table which does not exist. In SQL Server triggers you need to reference the Inserted and Deleted virtual tables. In an update situation the Inserted table contains the new row(s) and the Deleted table contains the old row(s).
In your trigger you want to do something like this:
CREATE TRIGGER xut_promdatechange ON purorddet
AFTER UPDATE
AS
SET NOCOUNT ON
DECLARE
@email varchar(255),
@ordnbr varchar(15),
@sql VARCHAR(MAX)
IF UPDATE(promdate)
BEGIN
/*
this verifies that the column actually changed in value. As a IF UPDATE() will return true if you execute
a statement LIKE this UPDATE TABLE SET COLUMN = COLUMN which is not a change.
*/
IF EXISTS(SELECT 1 FROM inserted I JOIN deleted D ON I.Key_Column = D.Key_Column AND I.promdate D.promdate)
BEGIN
select
@email = c.EMailAddress,
@ordnbr = a.ordnbr
from
soheader a inner join
inserted b
on a.custordnbr = b.ponbr inner join
testmlsystem.dbo.Userrec c
on a.Crtd_User = c.userid
SET @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 + ''''
EXEC msdb.dbo.sp_send_dbmail @recipients = @email,
@query = @query, @subject = 'Purchase order update' ;
END
END
GO
Of course the problem with this trigger is that it will only work correctly for a single row update. If you have a multiple row update you will only send out 1 email for a somewhat random row. Whenever I need to send out email about changes I use a trigger to populate a staging table and usually have a Windows service read the table and send out the email. In 2005 you could also use Service Broker to handle that. I wrote this basic article[/url] about Triggers a few months ago that you might find helpful.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply