Trigger doesn't work - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger doesn't work

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2009-10-14 : 15:47:39

I haven't used triggers before, and what I want to do is set one on a table, so that when a certain column gets updated with a certain value, an email (actually a SQL report) is sent to a list of emails.

The column is "activity_status", and if it changes from "Submitted" to "Approved", then I want to send the email.

I've tried the trigger below, which never works. I've tried updating one record in a table with multiple records, and tried it on a table with only one entry. I don't know enough about triggers to see where I've gone wrong.

CREATE TRIGGER [dbo].[email_alert]
ON [dbo].[tbl_intents]
FOR UPDATE
AS
IF UPDATE (activity_status)
BEGIN

DECLARE @intent varchar(15),@origStatus varchar(15),
@newStatus varchar(15),@email varchar(max)

--Get the deleted status
SELECT @intent = (intent_id), @origStatus = (activity_status)
FROM deleted d

IF @origStatus = 'Submitted'
BEGIN
--Get the updated status
SELECT @newStatus = activity_status
FROM inserted

IF @newStatus = 'Approved'

set @email = ''

SELECT @email = @email + coalesce (email,' ') + ';'
FROM tbl_emailList

SET @email = substring(@email,1,len(@email)-1)

EXEC ReportServer.dbo.sp_DD_email_intent_approval @scheduleID = 'BAA69110-9805-45CA-A1C9-DF72369DAF80',
@email = @email, @intent = @intent
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources