July 9, 2009 at 10:18 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:34 pm
there is no "updated" table that I'm aware of. All you have for triggers is inserted and deleted tables.
The probability of survival is inversely proportional to the angle of arrival.
July 9, 2009 at 2:26 pm
sturner (7/9/2009)
there is no "updated" table that I'm aware of. All you have for triggers is inserted and deleted tables.
Right, so you need to compare the values of inserted and deleted for the field(s) you want to check.
select Ins.KeyFld
from inserted Ins
inner join deleted Del on Ins.KeyFld = Del.KeyFld
where Ins.Fld2 Del.Fld2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply