From the course: Complete Guide to Advanced SQL Server

Unlock the full course today

Join today to access over 24,400 courses taught by industry experts.

Firing INSTEAD OF triggers

Firing INSTEAD OF triggers - SQL Server Tutorial

From the course: Complete Guide to Advanced SQL Server

Start my 1-month free trial Buy for my team

Firing INSTEAD OF triggers

“

With an INSTEAD OF trigger, you can intercept a T-SQL command and completely replace it with a different set of instructions. We can use this to create a trigger that will prevent any rows from being deleted from a table. Let's set up the structure by creating a new table called AccountsPayable in the KinetEcoTRG database. This table will have two columns, AccountID and AccountNumber. And then I'm going to insert three different rows of values into the table. I'll highlight lines one through 13 and execute them to set up the structure, and then we'll select everything from that table. So this is our starting point. We have a table with three different rows in it. Now, I want to set up a trigger that will prevent any of these values from being deleted from the table. We do that starting on line number 18. Here, I'm going to create a trigger called dbo.AccountsDelete. We're going to attach it on the dbo.AccountsPayable table. Now, when do we want this trigger to fire? We want the…

Contents