May 2, 2016 at 10:53 am
The subject line says it all. I'm testing something in an UPDATE trigger. Basically, I'd like to do a printf() type of thing. I created the trigger, used the UPDATE() function within it and gave it one of the column names, then from within SSMS I opened the table (it only has 2 rows) and did an "Edit the top 200 rows" option. I didn't see anything within SSMS. I thought I would. Here's the code that I put in:
CREATE TRIGGER [BOTS].[Template_UTrig]
ON [BOTS].[Template]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE(StringField)
BEGIN
SELECT 'StringField was updated'
END
ELSE
BEGIN
SELECT 'StringField was not updated'
END
END
So, did I do something wrong?
Kindest Regards, Rod Connect with me on LinkedIn.
May 2, 2016 at 10:56 am
It's returned to the caller, but since the Edit window doesn't have any way to show resultsets coming back, it would have been discarded by SSMS.
Write an update statement, and you'll see the results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2016 at 10:58 am
Oh, you don't want to do that, especially in a system with MARS involved. That would cause multiple recordsets potentially. You probably want to make a log table for debugging purposes, and insert a row into that table as opposed to trying to debug print.
May 2, 2016 at 11:03 am
jeff.mason (5/2/2016)
Oh, you don't want to do that, especially in a system with MARS involved. That would cause multiple recordsets potentially. You probably want to make a log table for debugging purposes, and insert a row into that table as opposed to trying to debug print.
And yes, this is a much easier way to debug a trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2016 at 11:32 am
Consider PRINT-ing the text to the SSMS Message window.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 2, 2016 at 11:44 am
Rod at work (5/2/2016)
The subject line says it all. I'm testing something in an UPDATE trigger. Basically, I'd like to do a printf() type of thing. I created the trigger, used the UPDATE() function within it and gave it one of the column names, then from within SSMS I opened the table (it only has 2 rows) and did an "Edit the top 200 rows" option. I didn't see anything within SSMS. I thought I would. Here's the code that I put in:
CREATE TRIGGER [BOTS].[Template_UTrig]
ON [BOTS].[Template]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE(StringField)
BEGIN
SELECT 'StringField was updated'
END
ELSE
BEGIN
SELECT 'StringField was not updated'
END
END
So, did I do something wrong?
Quick thought, this trigger will fire once for each update regardless of the number of rows updated, is that the intended behaviour?
😎
May 2, 2016 at 1:38 pm
Eirikur Eiriksson (5/2/2016)
Rod at work (5/2/2016)
The subject line says it all. I'm testing something in an UPDATE trigger. Basically, I'd like to do a printf() type of thing. I created the trigger, used the UPDATE() function within it and gave it one of the column names, then from within SSMS I opened the table (it only has 2 rows) and did an "Edit the top 200 rows" option. I didn't see anything within SSMS. I thought I would. Here's the code that I put in:
CREATE TRIGGER [BOTS].[Template_UTrig]
ON [BOTS].[Template]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE(StringField)
BEGIN
SELECT 'StringField was updated'
END
ELSE
BEGIN
SELECT 'StringField was not updated'
END
END
So, did I do something wrong?
Quick thought, this trigger will fire once for each update regardless of the number of rows updated, is that the intended behaviour?
😎
Yes.
Kindest Regards, Rod Connect with me on LinkedIn.
May 2, 2016 at 1:57 pm
jeff.mason (5/2/2016)
Oh, you don't want to do that, especially in a system with MARS involved. That would cause multiple recordsets potentially. You probably want to make a log table for debugging purposes, and insert a row into that table as opposed to trying to debug print.
Ultimately that is the goal. I just wanted to see what would happen using the UPDATE() function. I've never used it before, so I went ahead and did what Eric suggested and changed the SELECT to a PRINT. (Also had to comment out the SET NOCOUNT ON, too.) I saw the comment posted to the Results window when I modified the StringField value. However in my testing I noticed that if I set it to what it already is, the UPDATE() function says I've modified it. Technically, I suppose that's right. However I kind of thing if the better thing to do is check
insert.StringField <> deleted.StringField
and just forget the whole UPDATE() function stuff. I want to know if there's a real change, not just a technical change.
BTW, now that I've tested it I've disabled the trigger. This was just a proof of concept exercise.
Kindest Regards, Rod Connect with me on LinkedIn.
May 2, 2016 at 2:16 pm
Rod at work (5/2/2016)
However in my testing I noticed that if I set it to what it already is, the UPDATE() function says I've modified it.
The UPDATE function has nothing to do with whether a value has changed or not. It returns TRUE if the column was included in the UPDATE statement, FALSE if it wasn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2016 at 12:00 pm
Rod at work (5/2/2016)
jeff.mason (5/2/2016)
Oh, you don't want to do that, especially in a system with MARS involved. That would cause multiple recordsets potentially. You probably want to make a log table for debugging purposes, and insert a row into that table as opposed to trying to debug print.Ultimately that is the goal. I just wanted to see what would happen using the UPDATE() function. I've never used it before, so I went ahead and did what Eric suggested and changed the SELECT to a PRINT. (Also had to comment out the SET NOCOUNT ON, too.) I saw the comment posted to the Results window when I modified the StringField value. However in my testing I noticed that if I set it to what it already is, the UPDATE() function says I've modified it. Technically, I suppose that's right. However I kind of thing if the better thing to do is check
insert.StringField <> deleted.StringField
and just forget the whole UPDATE() function stuff. I want to know if there's a real change, not just a technical change.
BTW, now that I've tested it I've disabled the trigger. This was just a proof of concept exercise.
Im inquisitive... why did you have to comment out the SET NOCOUNT ON line?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply