SQL Server Programming Part 15 - DML Triggers

welcome to this was our tutorial in this video we're going to look at D ml or data manipulation language triggers in Microsoft sequel server we'll begin the video with a quick look at what triggers actually are and how DML triggers fittings a bigger picture will then look at the data manipulation events that you can attach your triggers to and then show you the difference between an after trigger and an instead of trigger when we've covered the basics we'll look at how you can use the inserted and deleted tables to get access to the data that your triggers might work on and finally we'll show you a practical example of using triggers and inserted tables to validate the data that's being added to your database so let's get started in sequel server a trigger is a special kind of stored procedure that you can attach to various events which happen in your database they come in three main flavors there's D ml or data manipulation language triggers there are d dl or data definition language triggers and then finally there are logon triggers in this video we're going to focus on the d ml triggers and as I've already mentioned it stands for data manipulation language that basically corresponds to three main events that can happen in your database these are the insert update and delete events these events are associated with tables or views in your database and the DML triggers can be attached to either a table or a view and they can be one of two types they can be either an after or run instead of trigger and this video is going to explain how all of that works we'll start by showing you how you can create basic DML triggers attached to a table before you create a trigger you need to decide which table or view in your database you want to attach it to in this example we're going to attach a trigger to the actor table in our movies database there's a couple of ways to achieve adding a trigger to a table one way is to find the table in the tables folder expand the table itself and find the triggers folder you can then right-click on the folder and choose new trigger and that will create a page of horrible-looking the system generated code what you would have to do now is you would have to modify this code to make it fit the trigger that you want to create personally I prefer not to do that I'm gonna close this window down and instead I'm going to create a brand new blank query window and in this window I can write out the code that will create my trigger I'll begin with the use statement to make sure that we're working in the correct database and go to ensure that the next statement will be the first one in the batch and then this is the simple statement that you use to create a trigger literally create trigger followed by whatever name you want your trigger to have I'm going to start my with with the letters TRG - anything that is a trigger and I'm going to call it actor's changed following that you need to say which table or view the they're the trigger is created for and you do that with an on statement we say on TBL actor in this case after this you have to decide whether your trigger will be an after trigger or an instead of trigger as I mentioned very briefly earlier on and after trigger will run immediately after the events you set it for and an instead of trigger will run in place of the events in this case we're going to use an after trigger and following this keyword you list out which events you want the trigger to handle so for instance if I only wanted to handle the insert event for this table I could say after insert but I can actually extend this to handle two or three events so if I wanted this to handle any data change event in my entire actors table I could add on the word updates followed by the word delete separated by commas and this trick will handle every single beta modification event in that table final a couple of key words to add the word as and it's useful to have a begin and an end statement followed by the word go and inside the begin and end block we can actually write out what we want our trigger to do this one's not going to be very complicated at all in fact all it's going to do is print a message to the messages window to tell us that something has happened in the actor's table so we can use a print statement and say something happened to the actor table very very simply okay so all we need to do now is execute the code create the trigger itself and when we do that we should see the nice message commands completed successfully so now that we've created the trigger we'll need some way to test that it works and basically in order to do that we need to trigger one of these three events either inserts and records into that table update a record in there will delete something actually written a bit of code already here's one I prepared earlier to do exactly that so I've turned off the row counts just to tidy up the output so we can see a little bit more clearly what's going on and then three separate statements basically want to insert a new record into the outer table want to update that record and then another one finally to delete it again so if I execute that code what we should see is three messages one for each event that has caused our trigger to fire let's have a quick look at how you could choose to modify your trigger if you wanted to change the way that it worked and if you still have the original code available that you used to create the trigger in the first place you can easily just head back to it modify the word create to the word altar and then maybe change something about your what your trigger does so maybe instead of something happened I'll put a more more specific message theta was changed in the outer table there we go if I execute this code now it will modify the way the trigger works and if I head back to my test code when I run this one again I'll see a different message this time so that's one way to modify a trigger just use the original code that you used to create it in the first place if you've made the mistake and you haven't saved that code or you've lost it you can also get back to changing a trigger through the object Explorer so if you expand your databases folder through the app to table and into the triggers folder you'll see that any triggers you've created will be listed there sometimes you might find that you need to refresh this list first so if you right-click the triggers folder and choose to refresh it I mean it wasn't there will appear then you can right-click the trigger that you want to modify and click the modify option and that will take you to some system generated code that will allow you to modify the way that it works if you wanted to delete a trigger rather than modify it again there are two different ways to do that if you head to the object Explorer window and again browse through your database the tables folder the table which contains your trigger and into the triggers folder you can right-click the trigger that you want to remove and choose to delete it when the dialog box appears all you need to do then is click the ok button I'm not going to do that's how much you're going to show you how you can also write some code to do the same thing so if I cancel from this dialog box in any query window effectively if I create a brand new blank one I make sure I'm using the Craig database so use movies and go I can simply say to drop a trigger I could spell trigger properly that would help and then the trigger I've created which was TRG actors changed if I execute this code now the trigger will be removed from the from the table so that means that if I find any sample code that previously showed as this information when data was changing that dis table if I execute this code now I don't see any such messages because the trigger has been removed so we've seen an example of a symbol after trigger let's have a look at how to create an instead of trigger as well you begin in the same way as for creating an after trigger with the words create trigger and then the name that you want your trigger to have I'm going to call this one actor inserted you then say what table you want the trigger to be created on and again in our case it's going to be on the TBL actor then you say rather than after we say instead of and then again we list out which events we want our trigger to be attached to previously we listed insert update and delete in a comma separated list in this example I'm only going to attach the trigger to the insert event then we have a couple of other key words to fill in the as keyword and we'll have a beginning and an end block and the go statement and then inside the beginning end block we can decide what we want our trigger to do pretty viously we used a very simple print statement to print some information to the messages window in this example what I'd like to do is use the raise error statement to raise a proper error message to the system I don't want to go into too much detail about how this statement works if you're really interested a quick google will will turn about help page on the Microsoft developer Network site which explains all about how to raise error statement works what we're going to do is we're going to provide a custom error message what you're going to say normal actors can be inserted we're also going to provide a severity level for our error and also a state so the three values that have to be filled in and just remember that also you have to enclose the arguments for this statement in a set of parentheses there we go and that is exactly one of our trigger will do whenever anybody tries to in certain and actor interview outer table the system will raise an error instead so I'm going to execute this code to create the trigger and then what we will need to do is test if it works so to test that this trigger does its job we can run some sample code to try to insert a new actor into the actors table so I've turned off the row counts again as I did in the previous previous example then I tried to insert an actor into the actors table and then I've tried to select the record inserted where the actors ID is 999 which is the value I've passed into the in certainty statement if I execute this query what RTC is what looks like a system-generated error message as an error number it has a severity level 16 and the state of 1 it also has our customized error message no more actors can be inserted just to prove that the actor hasn't been inserted into that table this select star from daylight to where the actors ID is 999 if we look at the results panel we will see that we don't return any records so that's the difference between an instead of and an after trigger the instead of trigger completely replaces what the original event would have done often it can be useful for your triggers to have access to the data that's been modified in one of the data modification events and for this reason you've got access to two tables the inserted table and the deleted table the inserted table is used in the insert and update events and the deleted table is used in the delete and update events to show you how you can quickly use one of those I'm going to make a modification to the trigger that we created earlier where we're inserting an actor into the actors table I'm going to replace the word create with our adulterer and instead of making it an instead of trigger I'm going to make this an after trigger I'm going to remove the error message that we hear that we raised previously and I'm going to replace that with a select statement but it's going to select everything from a table called inserted you can see that the inserted table appears automatically in the intellisense list this is a built-in part of any database so if I execute this code to modify my trigger I can then run some sample code that will try to insert a record into the actors table so here it is it's going to insert a new record with an ID of 909 into the actors table if I execute that code you can see although I didn't ask it to select anything in the code that I've written I see the record that has been inserted listed below and the reason that's happened is purely because that's what my trigger has been told today so there we go the insert a table shows you any records have been inserted into the table in the insert event you can use the insert a table to help you with the validation of data in your database so what we're going to do to demonstrate that is create a new trigger called TRG cast member added we're going to attach that to the cast table and it's going to run after a record is inserted into that table the idea is that we're trying to make sure that the actor were assigning to a role is valid the criteria we're going to use actually made a quick modification to the at table to include an extra field so if you look in the list of columns or the outer table I've actually just included an extra belong called up to date of death is a bit of a morbid example but we're going to record when an actor died we're gonna make sure that we don't then us try to assign them to a new role in a new film so if I have a quick look at the data store in that table we've got our new actor inserted from a previous example and the ID number is 999 and I've set the date of death for that actor to today's date none of the other actors have a date of death inserted so it's just that single one what we're going to do is write our trigger so that it checks if the ID of the actor that's included in the inserted table matches an actor whose date of death is not null then we're going to stop and allowing the user to add that actor to that particular role so to make my trigger work I need to check whether the actor whose ID I'm inserting into the cast table has his or her date of death fill them and to do that I'm going to use an if exists within my trigger inside the set of parentheses what I'm going to do then is write a simple select statement so I'm going to select everything from TBL actor I'm going to give it going to give that an alias as a I'm going to join that to with an inner join I'm going to join it to the inserted table I'm going to give that an alias as I the fields that I'm going to join on are a doctor ID and I'm going to check why that is equal to i dot cast actor ID all I need to do now is add a where clause to the query to check where actor data death is not null so that if statement will return true if the actor whose whose idea mean setting into the cast table has his data death filled in what do I want to do if that is true I'm going to write a begin an end block below my if statement because there's several actions I'd like to perform first of all as we saw earlier on I'm going to raise an error message to the system and the error that I'm going to raise is going to have a message of something like sorry and that act or has expired it's a bit of a mobile example but there we go I'm going to give it a severity of 16 and a state of 1 what I'd also like to do is make sure that the the insert doesn't carry through so I want to make sure I've rolled back my transaction that will be occurring at that point we've got separate videos on on transaction so if you're not sure about what transactions mean roughly speaking it cancels the insert and we've got a much more detailed video that explains that in a lot more air more detail finally what I'd also like to do is return at that point on this entire procedure to be ended at that point so what I should be able to do now is execute this script to create that trigger and all that remains is for me to test that it works in the way I've intended ok so here we are ready to test our trigger I'm going to try to insert a new record into the cast table I should assign some not quite random values but a new ID number for the cast ID which I have to provide the first actor that I'm going to try to insert is actually one who does exist and who is not a deceased so Tom Cruise's I've tried a number one and I'm going to insert him into film number 333 which happens to be the latest Star Trek movie star trek into darkness and he's going to be inserted with a cast with a roll of random a redshirt so because Tom Cruise's date of death is currently null the system will allow me to insert him as a cast member into this film and I execute this query everything will work exactly as intended what happens now if I modify first of all I'll add another value to the cast ID and instead of inserting Tom Cruise into the role what I'm now going to do is insert actor number 999 which if you remember briefly from earlier on I showed you was the new actor that I we just sort of generic on that we've made up the ID number is 999 and clearly the date of death field is not null so if I try to execute this same script now to insert the new matter I will actually get a an error message so we see the customer ID we created sorry that actor has expired and we can see that the transaction that I rollback has been aborted so that actor does not end up that role does not end up being created because the actor has expired so as I say it's a little bit of a morbid example but hopefully you can see how you could use triggers to enforce your business logic your your validation rules you can use the inserted table to check whether the values that are being inserted into your tables match criteria based on values contained in other tables not something you can't do using simple validation rules so hopefully that gives you a nice overview of how basic triggers work in sequel server hope you've enjoyed the video thanks for watching if you've enjoyed this training video you can find many more online training resources at ww-why Zeljko UK

Channel: WiseOwlTutorials

Views: 70,883

Rating: 4.9478583 out of 5

Keywords: sql server, Database Trigger, Data Manipulation Language, DML Trigger, wise owl

Id: WBmE4Utu6P4

Length: 18min 10sec (1090 seconds)

Published: Fri Nov 22 2013