PL/SQL tutorial 15: DML Trigger With Before Insert, Update and Delete DML Examples

Key Takeaways

what's up internet I'm Manish from Rebellion rider.com and welcome to the second tutorial of triggers in Oracle PL SQL tutorial Series in today's tutorial we will be doing some practical examples of first type of trigger which is DML or data manipulation language trigger so without further Ado let's start the tutorial all the necessary Theory regarding DML triggers have been explained in the previous tutorial thus today we will concentrate on practical examples only you can watch that tutorial link is in the show notes having said that let's start today's tutorial in order to demonstrate the creation process of DML trigger we need to create a table so let's quickly create a very simple table table created I have created this table with the name superheroes which has only one column Shore name with data type warart 2 and data 15 now I will write a DML tree Trier which will work on this table I'm going to write a very simple trigger on this table which will print a user defined message every time someone tries to insert a row in this table this example is intended to demonstrate you the proper process of trigger creation but first let's close this script output panel perfect always set your server output on otherwise the output message written from your trigger will not be displayed back to you you here is our trigger the first four lines of this trigger is the detailed part as these lines are telling you all about it while the later six lines Define the working part of our trigger because these lines are showing you what exactly is this trigger doing let's discuss the working part first here in the Declaration section I have declared a variable of watchart two type which I used in the execution section using select into statement which we have discussed in PL SQL tutorial 3 for holding the username of the user who performed the insert DML on the table superheroes also by using dbms output statement I am printing a userdefined message this will get displayed after every insert DML on the table now let's come to the detail part of this trigger here as you can see the name of this trigger is Biore superheroes where bi means before insert you can name your trigger whatever you want this trigger will get executed for each row before every inserted statement on the superhero state aable as we set its state as enable hence this trigger will come into the action immediately after we execute and compile it now let's execute and compile this trigger here we are trigger compiled now let's check whether this trigger is working or not and to do so we have to insert a row in the table but before that let's close this script output and make some space done let's write the insert statement now execute in the result you can see here is our user defined message with the name of the user which we use to insert the row similarly you can create a trigger for update DML you just have to change the keyword insert with update in second line of your trigger let me show you how to save the time let's copy this whole code and make some space okay and paste it here now let's make some changes here let's replace this insert keyword with update done also modify the output string a little bit okay let's do one more thing rename this trigger as buor superheroes where Buu means before update now let's execute and compile it here we are trigger compile successfully now it's time to take this trigger on a test run let's update the row which we inserted earlier to see whether this trigger is doing what we want it to do or not let's close this script output and write the update statement let's execute and as you can see here is our result similarly you can write a trigger for delete DML operation like this you can have three different triggers for three different DML events on one table don't you think that if we can cover all these three events in just one trigger then it will be a great relief if you think so then my dear friend I have some good news for you let me show you how we can achieve this feat let's close this script output and make some space great now we will write our triggers code here is our code as you can see in the detailed part of the trigger this time I have included all three DML events in just one line using our operator this line indicates that this trigger will fire before either insert or update or delete DML event the rest of the detail part is the same as that of previous examples major changes can be seen in the working part of the trigger where we used if then else if a statement and printed a user defined message for all three insert update and delete DML event let me quickly brief you on this if then LF is statement for in-depth knowledge you can watch the pl SQL tutorial 10 there I have explained this statement in detail link link is in the description let's come to the statement in order to Target a particular DML operation in PL SQL we have some predefined predicates which we can use as valid conditions such as in if condition we have inserting for insert DML deleting for delete and updating for update these predicates will evaluate to be true when their respective DML events occur for example if a user executes an insert on table superheroes then first if condition will be executed and print the underlying user defined message back to you similarly if delete DML is executed then second condition will become true and print the message and if an update DML is executed then third statement will print its message on your screen let's compile this trigger trigger compile now let's check this trigger let's start with insert DML but before that let's close this script output and make some space okay done now let's let's insert a r in our superheroes table let's execute here is our result which is the user defined string which we specified in our trigger now let's check for update DML let's execute this update statement again the result is the string which we specified in the trigger now let's check the delete DML and see what happens execute perfect which means our trigger is working perfectly hope this example has successfully demonstrated you the process of a trigger Creation in Oracle database you can visit my blog and find all the codes which we performed and talked about in this tutorial with in-depth knowledge on the topic link is in the description hope you learned something and enjoyed watching if so then please hit the like button if you have any question then feel free to either tweet me at Rebellion rer or drop a comment down below more importantly make sure to subscribe as more interesting videos on triggers are coming your way thanks for watching I'm Manish from Rebellion Rider