SQL Server Programming Part 16 - DDL Triggers

welcome to this YSL tutorial in this video we're going to cover DDL or data definition language triggers in Microsoft sequel server we'll begin the video with an explanation of what triggers actually are and how DDL triggers fit into the bigger picture of triggers in sequel server then we'll talk about some of the main data definition events you might be interested in attaching your triggers to and where you can go to find a complete list of the data definition events will set you through how to write code to create DDL triggers and also how to modify and delete them later we'll also have an explanation of DDL trigger scope which is basically down to whether you want your triggers to work on a single database or across an entire server the last thing we'll cover in the video is how you can change the firing order of your triggers so how you can use the first and the last trigger that fire for a particular event so let's get started in sequel server a trigger is a special kind of stored procedure which is executed in response to certain events in a database they come in three main forms these are DML or data manipulation language triggers DDL or data definition language and finally there are logon triggers as well in this video we're going to focus on DDL triggers and as I've already mentioned DDR stands for data definition language basically this is anything which modifies the objects in a database so it basically corresponds to three main keywords in sequel server create alter and drop there are several other keywords but these are the main focus for this video you can create a DDL trigger so that it fires only in response to events on a single database or you can make it so that the DDL trigger fires in response to events across an entire server we're going to begin this video with a quick look at how to create a simple DDL trigger attached to the create table event in a database before you create a DDL trigger you need to decide whether it will be scoped to a single database or to the entire server and the method that you use to create your trigger can depend on that choice if you're creating a database scoped trigger one way to create it is to expand the database folder then the database you want to attach the to than the programmability folder and finally within their find the database triggers folder you can right click on that folder and choose to create a new database trigger and that will create some system generator code that you can modify to create your trigger we're not going to do it that way we're going to write the code out from the scratch shortly but before we do that let's find out where you can store your data your server scope - triggers if I collapse all these folders to find a server scope to trigger need to expand the server objects folder within there you'll find a triggers folder unfortunately you can't right-click and create a new server scope to trigger using a menu option so if you want to serve a scope to trigger you have to write the code from scratch we're going to do that to create a new trigger that's attached to our movies database and it's going to handle the create table event to create a new DDL trigger scope to a database you'll need a new query window and at the top of this I'm going to write a quick use a statement to make sure we're working in the correct database use movies go to make sure the next statement is the first in the batch and then the statement to create a trigger which is literally create trigger following that you give your trigger at a sensible name mine is going to be called TRG no new tables hopefully that gives you a clue about what my trigger will do following the name of your trigger you say what the trigger is scoped to so I'm going to say on database which means that my trigger will only respond to events in the database that the trigger is being created in later on we'll talk about how you can extend that scope to the entire server following the scope you say which event or events your trigger will handle and do that without with word for and then need to list the event names that you want your trigger to handle and there's an awful lot of events that a DDL trigger can handle the best way to find the entire list is with a quick Google for DDL events which have returned fairly close to the table of age the DDL DDL events list on the the Microsoft developer Network site and in there and what you will hopefully find is a great big long list of all the DDL events the one that we're going to use today in this quick example is called create table and there it is so I can scope my DDL trigger to the create table event so back to my code what I literally have to write is create underscore table and that trigger will only work for the create table event in the movies database next I have to say what I want my trigger to do I'm gonna write the word as and then optionally I begin in an end block and within there the instructions that I want my trigger to perform so I'm gonna have a really simple print statement that's going to print a message to the end user no new tables please and following that I need to make sure that the the the statement which tries to create the table doesn't carry forward so I want to roll back the transaction that's being created when somebody tries to create a new table all I need to do now is execute this code to create my trigger now this commands completed successfully and I should now be able to find that trigger within the object Explorer window in my movies database programmability and database triggers I'm going to refresh this folder first by right clicking on it and choosing refresh I'm going to expand the folder there's my new trigger just created to test that the trigger works we need to try to create a new table inside the movies database and I've got a bit of sample code here that I'll try to do that it's an incredibly simple table with a single column called ID and if I try to execute this code it's not going to work we're going to see the error message or the message that we asked our trigger to display no new tables please and then actual error message explaining that the transaction was ended in trigger so the end result is that in the movies database we do not end up with a test table we should also make sure that the trigger doesn't prevent us from creating tables in other databases so you have a test DB database that currently doesn't contain any tables at all about in the system generated ones so if I modify my code to say use test DB instead of movies and try to execute this code will find that this does work so a database scope to trigger as we explained only effects events in the database that your trigger is attached to if you want to make changes to the way a trigger works you'll need to write some code to do it and the easiest thing to do by far is use the code you used to create the trigger in the first place find the word create and change that to the word alter and then you can make any changes to your trigger that you like I'm going to extend what my trigger does it's going to handle a couple of extra events it's going to handle the alter table and the drop table events as well as the create table so basically any change it made to my tables at all will be prevented by this trigger I make a quick change of the message of this that gets printed as well so it says no changes to tables please and then if I execute this code my trigger will be updated to test if it works I can go back to my test code where I was creating a new table so let's modify which database we're using so it uses the movies database again if I execute this code I'll be told again that I can't create a new table as I couldn't before the message is slightly different this time you said no changes to tables please if I write some code that tries to drop a table as well if I say drop table let's see one of the tables in this database is TBL caste um and if I try to execute that code again I'll be told the same message so this part might trigger no change to tables please and I find that my transaction was ended in the trigger if you want to remove a trigger completely you knew that in one of two ways you neither use the object Explorer and if you expand the database and the programmability folder database triggers you can right-click on the trigger you want to remove and choose to delete it you'll be presented with a dialog box asking if you want to definitely do that I'm going to click cancel in this case so I can show you how to write the code to it as well so I'm going to create a new query window to do this and I'm going to make sure that I'm using movies and go and to write code to delete trigger we write the statement drop trigger and then TRG no new tables which was the name of my trigger and we also have to say that it was on the database so if I execute the code I'll find that my trigger has now been deleted if I go back to doctor to Explorer refresh my database triggers folder I'll find that the trigger is no longer there rather than deleting a trigger you can also choose to temporarily disable it if I change my code again so that recreates my new table trigger I'll change the word alter to the word create and execute that code I'll be able to find my trigger again in the object Explorer window and if i refresh my database triggers folder one way to disable that trigger is actually using the opposite Explorer when you have a right-click on the trigger there's an option there to disable it if I choose that option I'll find that I get a little message confirming that that's happened and if I were to now go and run some sample code to try to perhaps create a new table in the movies database what I should find is that my trigger is no longer active so let me just run a bit quick bit of code that will drop that table again so drop table TBL test and go but actually that so everything that I want to do the tables I can now do because my trigger has been disabled if I head back to the object Explorer window I can right click on the trigger again I can choose to enable it again I cannot get this another error message sorry confirmation message and now this time if I try to execute some code to create my table I won't be allowed to because my trigger is now enable them so that's one way to enable the disable triggers using the object Explorer window I can also write code to disable and enable my triggers so if I create a new query window make sure that I'm using my movies database again and go sorry and go and then I can write segments that both enable and disable my triggers so disable the trigger and I simply write disable trigger followed by what the name of the trigger is which is called no new tables and on database if I execute that code now that will disable my triggers one quick way to prove that to you is if I go back to my object Explorer if I right-click on that trigger if i refresh the table first and right-click on that trigger notice I can't choose to disable it anymore because the air the trigger is already disabled by running my code again if I wanted to enable the trigger in code I simply change the word disable it's the word enable and execute the code again and quickly prove to you in the object Explorer if I already fresh the database triggers folder again if I right-click on that trigger I can't enable it anymore because it already is by running that code a neat little trick you can perform when you're disabling triggers in code is to disable all of the triggers on the database or on the server in one go so I change my code again so that I'm disabling a trigger so disable trigger rather than using the named trigger you can replace the name of a trigger with the word all instead so if I execute that code I'll find that all of the triggers on the entire database have now been disabled if i refresh the database triggers follow again I'll find if I try to disable the trigger I can't because it already is Rhian abling triggers as you'd expect he says straightforward is changing the word disable to the word enable and if I execute this code again go back to the object Explorer refresh this folder I'll find that this trigger has now been enabled again and that will be true for any other triggers on this database so there we go nice little trick for enabling and disabling all triggers so far all the examples we've created have been scoped to a single database but it's relatively straightforward to extend that scope to an entire server genuinely all you would have to do is in the code that creates your trigger modify the word database and replace it with words all server and that's genuinely all that's needed that will create a trigger that scoped to the entire server you execute this code now and again commands completed successfully to find my trigger in the object Explorer I'll have to well first what I'm going to collapse the databases folder to make it a bit easy to read and in the server list look for the server objects folder and then look for the triggers folder in there I'm going to quick refresh to make sure my track is going to be in there and now it is my server scopes trigger you can follow this same rule if you want to to do anything that we've done so far with any of our database triggers so you want to for instance let's see enable triggers on a server then you can exchange the word database with the words all server so this little string of code here will Allen will enable all the service cope triggers on the server and what else do we have available we could drop triggers on the server as well as on a database and again just exchange the word database for the words all server and executing the code will remove the trigger and there we go so we've just seen that it's possible to create two triggers to handle the same set of events one scoped to the server and once go to a database it's worth are quickly mentioning something about the order in which those triggers will fire service cope triggers will always fire before any database scope triggers and there's nothing you can do to affect that but as we've seen that we can create more than one trigger to handle the same set of events with different scopes it's also worthwhile mentioning that you can create multiple triggers in the same scope to handle the same set of events and if you want to affect the order of that set of triggers that are is something that you can do about it to show you how this works we're going to create two new triggers on our database on our movies database they're incredibly simple triggers they can handle the create table event and all they're going to do is print a unique message when that event runs so if I execute this set of code to create these two triggers and head back to my object Explorer if i refresh my database triggers folder in the movies database I'll have those two new triggers I also want to disable the no new tables trigger as well I don't want that one to be running otherwise that's going to interfere with what I'm about to show you so if I disable the no new tables trigger and there it is what I should find now is that if I try to create a new table in this database then I'm going to get two separate messages but I'm not entirely sure which order those messages will appear so I'm not sure which order my two triggers will firing so if I execute this code here to create again my simple test table in the movies database I actually get my mind triggers in the order in which they've been created so I've got the second trigger message appearing first and the first trigger message appearing second what I'd like to do is I'd like to change the order of those two things happening I'd like to make sure that the first trigger does run first to change the order in which your triggers fire on either a database or a server you can use the cleverly named system stored procedure called set trigger order so that's a quick look at how that works if I create a new query window and want to make sure that I'm using my movies database and go and then I'm going to execute or exec a system store procedure called SP and so all set trigger order and there is now there are four different parameters for this stored procedure the first parameter is the name of the trigger who's all you want to set so as you can see in the tooltip here as I'm listening out my my parameters it says at trigger name then the second parameter is the order that you want the trigger to run in the third parameter is the statement type that the trigger handles and finally there's a parameter called namespace so that's essentially whether its scope to the database or the server so if I fill in these parameters quickly the trigger name here is equal to t RG first trigger so I'm going to set this one to be the year the first trigger the second parameter is the order so the order equals first the third parameter is the statement type so at St MT type smoke want to type in and that's going to be equal to create table now for the statement type parameter you can only specify one single statement at a time so if my trigger handle to create table alter table and drop table I'd have to execute this stored procedure separately for each one of those events and finally I'll set the at namespace parameter and that namespace so as well that the cap with a lowercase n namespace is equal to database one quick note if you were doing this for the server you wouldn't say in place of database you wouldn't say all server to extend the scope in this case you would just say the word server so that's how you how you change that but might trigger a scope to the database so on to make sure that I have that word in there so executing this this code now will change the order of my triggers so that first trigger is the first one if I ask you that I can see that it's successfully completed and if I head back to my test code which is going to try to create a new table I'll need to delete it first let me execute that code first to make sure that my type test the table at disappeared and if I execute this code again now this time I say that the first figure is the first one that's fired just to finish off this little section it's worth mentioning you can also change a trigger to be the last one that's five so if I go back to my code which was changing the order of my triggers the other available options for the app order parameter as well as first we can say last and that will set this trigger to be there the last one if I execute this code again and then back to my test code deletes my test table again and then try to create it again you can see that the first trigger now runs last finally as well as saying first and last if you just wanted to clear the ordering for a particular trigger you can actually set the order back to none so I execute this code again now and again head back to the test code delete my table and then recreate it I'll see that again my first trigger comes in last which fired last I came back to the original order that my triggers were created in so there we go there's a quick example of how you can change the orders of your triggers hopefully it doesn't need to be mentioned but I'm going to anyway that you can only have one first and one last trigger the remaining triggers you have handling a particular event will run in the order in which you've created them if you've enjoyed this training video you can find many more online training resources at ww-why 0uk

Channel: WiseOwlTutorials

Views: 54,152

Rating: 4.8468084 out of 5

Keywords: sql server, Database Trigger, Data Definition Language (Programming Language), SQL (Programming Language), wise owl

Id: 9XCB0y44b1g

Length: 20min 17sec (1217 seconds)

Published: Wed Nov 27 2013