Join Martin Guidry for an in-depth discussion in this video Using "instead of" triggers, part of SQL Server: Triggers, Stored Procedures, and Functions.
- View Offline
- Exercise Files
The next type of triggers we will talk about are 'instead of' triggers.…These triggers are executed as an alternative to an INSERT, UPDATE, or DELETE statement.…These triggers completely block the original statement leaving the data unchanged.…However, inside the body of the trigger we can choose to modify data and we usually do.…In this example, I'll be of using a trigger to enforce a data integrity rule.…The rule states that we cannot ever delete a category.…We must just mark that category as inactive.…
So if someone tries to delete the category, I want to trigger to block the…action and instead update the record and set active equal to 0.…I staged some code for you in your exercise file.…Let's talk about this code.…CREATE TRIGGER CategoryDelete on (dbo).(Categories) and line 3 says INSTEAD OF DELETE.…So again that will prevent the DELETE statement from happening.…Line 6, 7, and 8 perform an update on the categories table instead of the delete.…
It's going to set active equal to 0 where the category is in and then we select…
- Comparing triggers, functions, and stored procedures
- Installing and configuring SQL Server
- Creating a stored procedure
- Returning data using data sets
- Creating user-defined functions
- Using "after," "instead," and nested triggers
- Modifying existing stored procedures
- Implementing logging on DELETE
- Choosing between T-SQL and CLR
- Executing a stored procedure
- Passing parameters
Skill Level Advanced
1. Getting Started
2. Installing SQL Server
Configuring your environment4m 53s
3. Stored Procedures
4. User-Defined Functions
6. Putting It All Together
7. Using CLR Assemblies
8. Exploring an ASP.NET Web Application
Next steps1m 43s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.