November 16, 2011 at 8:21 am
I'm trying to create a trigger in a table so that anytime the 'SCRStatus' column was '6', and is changed to any new status, I insert a row into a new table I recently created meant to hold emails for later delivery (I'll have to work on that next....) I'm struggling with:
...Since the trigger can fire on a multiple-row update (unlikely...) I need to deal with each update independently. Does the UPDATE() function do that as I've coded it?
...I understand how to see the "inserted" table (I think...) but how do I see the "deleted" table as well? What does the 'FROM' clause look like?
...Is my embedded SELECT statement to retrieve the StatusDescription coded correctly?
This is hard to test. Are there "normal" testing strategies for testing triggers?
Thanks guys. Herewith my (partial) trigger code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE TRIGGER Notify_SCR_Change
ON tblSCR
AFTER ,UPDATE
AS
BEGIN
DECLARE @Recipient nvarchar(40)=N'MTV_CONST_FIELD@xxxxxxx.com'
SET NOCOUNT ON;
INSERT INTO tblPendingMessage (Recipient,MessageSubject,MessageBody,MessageGenerated)
SELECT
@Recipient,
N'SCR has changed from Estimated',
N'SCR Number ' + SCRNbr + N' has changed from ESTIMATED status to ' + (SELECT SCRStatusDescription FROM tblSCRStatus WHERE tblSCR.SCRStatus=tblSCRStatus.SCRStatusID),
GetDate
FROM inserted
WHERE update(SCRStatus)
END
GO
Jim
November 16, 2011 at 8:40 am
This looks pretty good. However your select statement is not quite correct. You need to get the status relative to the row in inserted. The way your subselect is coded it will return more than 1 value and it is not related to the current row.
You did a good job of making the trigger deal with multiple rows.
You could use your status table to get the current status too.
This should be pretty close but not totally sure about which columns to use for joins.
INSERT INTO tblPendingMessage (Recipient,MessageSubject,MessageBody,MessageGenerated)
SELECT
@Recipient,
N'SCR has changed from Estimated',
N'SCR Number ' + SCRNbr + N' has changed from ' + delStatus.SCRStatusDescription + ' status to ' + newStatus.SCRStatusDescription,
GetDate
FROM inserted i
join deleted d on i.SCRID = d.SCRID join tblSCRStatus delStatus on delStatus.SCRStatusID = d.SCRStatusID
join tblSCRStatus newStatus on newStatus.SCRStatusID = i.SCRStatusID
WHERE update(SCRStatus)
As you said, testing is a bit more challenging for triggers. Since this is basically an audit trigger it is fairly easy to test. You can add some print statements in places so you know it will make it there. You can also comment out the insert but leave the select statement so you will see the results in SSMS. Of course the best place to start testing is in SSMS so you can see all your debugging stuff. Then when you are comfortable with that you will just have to update a record and then check out your audit table. You are 99% there already.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2011 at 8:40 am
You want to select from the main table in your insert, so
SELECT ...
FROM tblSCR t
Then join that with the deleted table to look for previous status.
INNER JOIN deleted d
on t.pk = d.pk
where d.status = '6'
This limits your trigger to dealing with rows that were previously a 6.
Triggers deal with all rows at one time, not each update independently. So code to write a query that returns all the rows affected, and then inserts that entire batch at once into the other table.
If you want to know what's changed, use columns_updated - http://msdn.microsoft.com/en-us/library/ms186329.aspx
November 16, 2011 at 10:18 am
I don't know that you want to join back to the original table, but you should use both inserted and deleted because the functions update and columns_updated consider a column as updated if the column is included in an update statement whether the value changed or not. So I'd suggest something like this:
INSERT INTO tblPendingMessage
(
Recipient,
MessageSubject,
MessageBody,
MessageGenerated
)
SELECT
@Recipient,
N'SCR has changed from Estimated',
N'SCR Number ' + SCRNbr +
N' has changed from ESTIMATED status to ' + S.SCRStatusDescription,
GetDate
FROM
INSERTED AS I
JOIN DELETED D
ON I.primary_key = D.primary_key AND
/* new value is different than the old value */
I.scrstatusid <> D.scrstatusid
JOIN tblscrstatus AS S
ON I.scrstatusid = S.scrstatusid
WHERE
/* only when the old value is 6 */
D.scrstatusid = 6
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 16, 2011 at 10:28 am
Jack, after some further research, I found a link to Garth's blog where he did something almost exactly how I needed it done. It is pretty much the same as what you suggested, and it works.
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Jim Shaffer>
-- Create date: <11/16/2011,,>
-- Description:<Sends an EMail to a distribution list when SCR Status changes,,>
-- =============================================
CREATE TRIGGER Notify_SCR_Change
ON tblSCR
AFTER UPDATE
AS
--Make sure Priority was changed
IF NOT UPDATE(SCRStatus)
RETURN
IF EXISTS (SELECT * from inserted a JOIN deleted b ON a.SCRID=b.SCRID WHERE b.SCRStatus=6)
BEGIN
DECLARE @Recipient nvarchar(40)
SET @Recipient=N'MTV_CONST_FIELD@xxxxxx.com'
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar = CHAR(13) + CHAR(10)
SET NOCOUNT ON;
INSERT INTO tblPendingMessages (Recipient,MessageSubject,MessageBody,MessageGenerated)
SELECT
@Recipient,
N'SCR has changed from ESTIMATED status to '
+ (SELECT SCRStatusDescription FROM tblSCRStatus WHERE a.SCRStatus=tblSCRStatus.SCRStatusID) ,
N'SCR Number ' + a.SCRNbr + N' has changed from ESTIMATED status to '
+ (SELECT SCRStatusDescription FROM tblSCRStatus WHERE a.SCRStatus=tblSCRStatus.SCRStatusID) + @NewLineChar +
N'SCR Description: ' + @NewLineChar + coalesce(a.SCRDescription,N'none') + @NewLineChar +
N'Here is a link address for the SCR: ' + coalesce(a.SCRDocumentLink,N'none'),
GetDate()
FROM inserted a JOIN deleted b ON a.SCRID=b.SCRID
WHERE b.SCRStatus=6 and a.SCRStatus <> 6
END
GO
I tested it by using a test DB that's structurally identical to the production db.
Thanks so much for your help, both of you. I appreciate your time!
Jim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply