Recursive Trigger - Query

Question

Recursive Trigger - Query

arvind.patil 60171

SSC Enthusiast

Points: 174
More actions
July 19, 2018 at 5:39 am

#354523

CREATE TABLE Questions
(
QuestionID INT
, QuestionTitle VARCHAR(100)
, datechanged DATETIME
, IsApproved BIT DEFAULT 0
)
go
CREATE TRIGGER updateQuestions ON dbo.Questions
FOR UPDATE
AS
UPDATE Q
SET Q.datechanged = GETUTCDATE()
FROM inserted i
INNER JOIN dbo.Questions Q
ON I.QuestionID = q.QuestionID
go
INSERT INTO Questions
( QuestionID
, QuestionTitle
, datechanged
)
VALUES
( 1
, 'Select me!'
, GETUTCDATE()
)
go
INSERT INTO Questions
( QuestionID
, QuestionTitle
, datechanged
)
VALUES
( 2
, 'Tables and Columns, Oh My'
, GETUTCDATE()
)
go
UPDATE Questions
SET IsApproved = 0
WHERE QuestionID IN ( 1, 2 )
go
My question is why this trigger is not behaving like recursive trigger.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

arvind.patil 60171 - Thursday, July 19, 2018 5:39 AM
CREATE TABLE Questions
(
QuestionID INT
, QuestionTitle VARCHAR(100)
, datechanged DATETIME
, IsApproved BIT DEFAULT 0
)
go
CREATE TRIGGER updateQuestions ON dbo.Questions
FOR UPDATE
AS
UPDATE Q
SET Q.datechanged = GETUTCDATE()
FROM inserted i
INNER JOIN dbo.Questions Q
ON I.QuestionID = q.QuestionID
go
INSERT INTO Questions
( QuestionID
, QuestionTitle
, datechanged
)
VALUES
( 1
, 'Select me!'
, GETUTCDATE()
)
go
INSERT INTO Questions
( QuestionID
, QuestionTitle
, datechanged
)
VALUES
( 2
, 'Tables and Columns, Oh My'
, GETUTCDATE()
)
go
UPDATE Questions
SET IsApproved = 0
WHERE QuestionID IN ( 1, 2 )
go
My question is why this trigger is not behaving like recursive trigger.

I see no code which would cause recursion here. What are you trying to achieve?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

arvind.patil 60171 SSC Enthusiast Points: 174 More actions · Answer 2

I read this Query from Question of the day post. Was trying to learn about triggers.

So my query is since the trigger is updating the Questions table

UPDATE Q

SET Q.datechanged = GETUTCDATE()

FROM inserted i

INNER JOIN dbo.Questions Q

ON I.QuestionID = q.QuestionID

Why is the trigger is not calling itself again ?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

arvind.patil 60171 - Thursday, July 19, 2018 6:25 AM
I read this Query from Question of the day post. Was trying to learn about triggers.
So my query is since the trigger is updating the Questions table
UPDATE Q
SET Q.datechanged = GETUTCDATE()
FROM inserted i
INNER JOIN dbo.Questions Q
ON I.QuestionID = q.QuestionID
Why is the trigger is not calling itself again ?

Aha, now I understand.
Whether or not a trigger recurses in this way is a database setting, which by default is turned off. Try running the following, to see whether recursive triggers are enabled for any of your databases:

SELECT d.name , d.is_recursive_triggers_on FROM sys.databases d;

If you want to turn on recursive triggers in a particular database, you'll need to issue a suitable ALTER DATABASE command to do so.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 4

And please understand that you could seriously negatively affect the behavior of other triggers in your database if you do that, because that setting is in effect for the ENTIRE database, and NOT just the one trigger. VERY DANGEROUS thing to do unless you know exactly what you are doing.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

andycadley SSCertifiable Points: 5296 More actions · Answer 5

I'm not a fan of triggers in general, but recursive triggers are absolutely the spawn of satan. There's a really good reason they are disabled by default in SQL Server and I definitely would strongly consider a redesign of any system that actually required them.