Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 SQL After Update Trigger

Author  Topic 

samindayapa32
Starting Member

1 Post

Posted - 2014-06-04 : 11:51:57
I'm trying to Create trigger that will recognize that the status column data have changed from Credit hold ='c' to new ='N' and send a email to the user but I cant figure out how to check if the column was 'C' before it get to N because the status column can changed with many other statuses
right now this is what I have
CREATE TRIGGER [dbo].[sol_update_CreditHold]
ON [dbo].[orders_all]
AFTER UPDATE
AS
BEGIN
DECLARE @StatusChange char(1)
IF UPDATE (status)
BEGIN
SELECT
@StatusChange = COUNT(*)
FROM
inserted i
JOIN
deleted d
ON i.order_no = d.order_no and i.ext= d.ext
WHERE i.status <> d.status and d.status in ('C','H')
IF(@StatusChange = 'N')
BEGIN
DECLARE @OrderNo int, @OrderExt int, @status char(1), @WhoEntered varchar(20)
IF @status = 'N'
SELECT
@OrderNo= order_no,
@OrderExt = ext,
@status = status,
@WhoEntered = who_entered
FROM
inserted

BEGIN
DECLARE @body1 Varchar(100)
Declare @recipients_email varchar(max)
SET @body1 = 'Credit hold released for-'+ convert(varchar(5),@OrderNo) +', '+convert(varchar(5),@OrderExt)+' '
select @recipients_email=email_address from SolidealControl..SOL_Epicor_Emails where ID = '5'
EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients_email,
@subject = 'Credit hold released',
@body = @body1,
@profile_name = 'sql',
@body_format = 'HTML'
END
END
END


END

Saminda Yapa
ERP Analyst
   

- Advertisement -