SQL After Update Trigger - SQL Server Forums

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources