SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 SQL After Update Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samindayapa32
Starting Member

USA
1 Posts

Posted - 06/04/2014 :  11:51:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000