From the course: Complete Guide to Advanced SQL Server
Join today to access over 24,400 courses taught by industry experts.
Recursive triggers - SQL Server Tutorial
From the course: Complete Guide to Advanced SQL Server
Recursive triggers
It is possible to write a trigger that causes itself to fire again. This is called a recursive trigger. It's easy to get caught in an infinite loop this way, however, so the option to do this is turned off, by default. Unlike nested triggers, though, this is a database-level setting, not a server-level setting. So if you have a database that would benefit from a trigger operation firing multiple times on itself, then this could be a technique worth investigating. Let's take a look by creating a new table called Numbers. And the only row that we need to pay attention is this one here called RowValue. It'll be an integer data type. So go ahead and create that table. And then we can turn our attention to the trigger. I'm going to create a new trigger called RecursiveExample. It'll be on the Numbers table. And it's going to be an AFTER INSERT trigger. The commands that we're going to do is going to run through this IF conditional statement. And we're going to evaluate if the row value…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.
