Sunday, February 19, 2012

Help with UPDATE trigger

I am trying to setup a trigger that sends an email if a field is changed to specific data. The trigger works when ever the field is changed, but I only need an email if the field is changed to 'In Review'
Any help is greatly appreciated.

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain.com',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain.com',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email serverl'
select RC = @.rc
END

goYou need to check for the updated value.|||I'm sorry, but I am new to SQL.
Where and how do I insert CHECK|||Got it.

I added an IF
Here is the code I have if anyone needs

-- Drop the trigger if it already exists
IF EXISTS(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[reviewntc]') AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [reviewntc]
GO

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
set nocount on
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
IF @.CDStatus = 'In Review'
BEGIN
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email server'
select RC = @.rc

END
END
go

No comments:

Post a Comment