Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
In our two previous examples we created one trigger that will block deletions from the categories table, instead marking records as inactive, and we created another trigger that when a category is marked as inactive, it marks all products as inactive also. This means that if I issue a DELETE statement, the first trigger fires, blocking my DELETE statement, and instead updating the record. That update, the update that was caused by the trigger, will in turn cause the second trigger to fire.
This situation where the actions of one trigger cause the next trigger to fire is called nested triggers. The ability to nest triggers is turned on by default, but you can turn it off if you want to. I have some code in your exercise files that will do this. It's an SP configure, the feature we would like to configure is nested triggers. It is turned on by default which would be a 1. So we'll use a 0 to turn it off and if you'd like to turn it back on, just use a 1.
Even when nested triggers is turned on, it is always limited to 32 levels. So we can have trigger 1, cause trigger 2 to fire which causes trigger 3 to fire, which causes trigger 4 to fire, but you can only go 32 levels deep. That is at least the machine says, you can only go 32 levels deep. As a best practice I would recommend a lot less than 32 levels. Obviously, something that goes 30 or 31 levels deep could be very difficult to troubleshoot and very difficult to get your mind around what's going on.
As a best practice, I usually try and limit my nesting to three levels or less.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.