March 23, 2017 at 3:38 pm
Hello,
I am trying to create a trigger that will send out an email when a specific field value gets set to "Approved". Currently, my trigger is sending an email that anytime a field is updated in my row. What do I need to change in my logic below so that only when the field status='Approved" AFTER UPDATE an email gets sent?
CREATE TRIGGER tr_SendEmail
ON WRK_TBL
AFTER UPDATE
AS
DECLARE @record_id int
BEGIN
DECLARE tgr_cursor CURSOR
FOR SELECT ID FROM WRK_TBL WHERE (Status='Approved')
OPEN tgr_cursor
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM tgr_cursor
INTO @record_id
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Reporting',
@recipients = 'myemail@test.com' ,
@body = 'Test TGR EMAIL',
@subject = 'Test TGR EMAIL'
FETCH NEXT FROM tgr_cursor
INTO @record_id
END
March 24, 2017 at 12:46 am
There is no filter on the trigger, obviously it will send mail for any update.
😎
Recommend that you separate the send mail from the trigger by adding a queue table. You can then use a scheduled job to send the mail and delete the entries from the queue.
Quick exampleUSE TEEST;
GO
SET NOCOUNT ON;
-- The source table to monitor for updates
IF OBJECT_ID(N'dbo.TBL_TEST_TRIGGER') IS NOT NULL DROP TABLE dbo.TBL_TEST_TRIGGER;
CREATE TABLE dbo.TBL_TEST_TRIGGER
(
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_TRIGGER_ID PRIMARY KEY CLUSTERED
,TT_DT DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_TRIGGER_TT_DT DEFAULT (GETDATE())
,TT_VAL INT NOT NULL
,[Status] VARCHAR(50) NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_TRIGGER_STATUS DEFAULT ('Pending')
);
-- A queue table which receives values on update from the source table
IF OBJECT_ID(N'dbo.TBL_TEST_TRIGGER_QUEUE') IS NOT NULL DROP TABLE dbo.TBL_TEST_TRIGGER_QUEUE;
CREATE TABLE dbo.TBL_TEST_TRIGGER_QUEUE
(
TQ_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_TRIGGER_QUEUE_TQ_ID PRIMARY KEY CLUSTERED
,ID INT NOT NULL
,TT_DT DATETIME NOT NULL
);
GO
-- Trigger to capture the updates
CREATE TRIGGER dbo.TRG_DBO_TBL_TEST_TRIGGER_ON_APPROVAL
ON dbo.TBL_TEST_TRIGGER
AFTER UPDATE
AS
BEGIN
INSERT INTO dbo.TBL_TEST_TRIGGER_QUEUE(ID,TT_DT)
SELECT
I.ID
,I.TT_DT
FROM inserted I
WHERE I.[Status] = 'Approved'
END
GO
-- Few values to test
INSERT INTO dbo.TBL_TEST_TRIGGER(TT_VAL)
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-- Assert that the queue is empty
SELECT
TTQ.TQ_ID
,TTQ.ID
,TTQ.TT_DT
FROM dbo.TBL_TEST_TRIGGER_QUEUE TTQ;
-- Update few values to 'Approved'
UPDATE TT
SET TT.Status = 'Approved'
FROM dbo.TBL_TEST_TRIGGER TT
WHERE TT.ID IN (1,4,7,9);
-- Check the content of the queue
SELECT
TTQ.TQ_ID
,TTQ.ID
,TTQ.TT_DT
FROM dbo.TBL_TEST_TRIGGER_QUEUE TTQ;
March 24, 2017 at 7:26 am
Thank you this makes a lot more sense to do this way, much appreciated!
March 24, 2017 at 8:39 am
Just ping back if you need any help
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply