Triggers In SQL| Triggers In Database | SQL Triggers Tutorial For Beginners | Edureka Rewind

Key Takeaways

hey guys this is prati from edu Rea and I welcome you all to this interesting session on triggers in SQL but before we get started let's have a look at the agenda for today's session so we're going to begin with understanding what is a trigger followed by that we'll be looking at its syntax and example next up let's try to get the detailed explanation on various operations that can be performed using trigger and finally we'll wrap up this session by discussing its advantages and disadvantages so I hope the agenda is clear you guys so with that being said let's get started the first Topic in today's discussion is what are triggers triggers are the SQL code that are automatically executed in response to certain events on a particular table these are used to maintain the Integrity of the data in a given table a trigger in SQL Works similar to real world trigger I know this is a formal definition but let's try to understand it in a better way here we can see Anna is asking Sharon about triggers Sharon imagines trigger to a falling dominoes how does a falling dominoes is related to a trigger let's move further and see the explanation given by Sharon Anna is a marketing officer in a company when a new customer data is entered into the company's database she has to send the welcome email to each and every new customer if it is one or two customer Anna can do it manually but what if the count is more than a th000 it will be a repetitive and tiring job efficiency of Anna may drop well in such scenario triggers come in handy Anna can easily create a trigger which will automatically send a welcome email to new customer once the data is enter into the database after creating a trigger in the database of the company she doesn't have to monitor the trigger frequently the efficiency of the work is increased with this analogy given by Sharon I hope you're clear with the introduction of trigger let's head to the next topic that is syntax and example of a trigger on the screen we can see the syntax of a trigger and let me explain each and every part in detail the first part is create trigger these two keywords are used to specify that a trigger block is going to be declared the next one is trigger name it specifies the name of the trigger trigger name has to be unique and shouldn't repeat the next one is before or after this specifies when the trigger will be executed it tells us the time at which the trigger is initiated that is either before ongoing event or after the ongoing event before triggers are used to update or validate record values before they are saved to the database while after triggers are used to access Fields values that are sent by the system and to affect changes in other records the records that activate the after trigger are read only we cannot use after trigger if we want to update a record because it will lead to a readon error the next part of the syntax is insert update or delete these are the DML operations and we can use either of them in a given trigger next is on table name we need to mention the table name on which the trigger is being applied don't forget to use on keyword and also so make sure the select before or after any column value of a row changes while column level triggers get executed before or after the specified column changes the final part of the syntax consists of queries that needs to be executed for a trigger in the given trigger we are trying to calculate the marks of a student as soon as his details are updated to the database here we are adding 100 marks to the marks column of of each new record also make a note new keyword refers to the row that is getting affected so this was all about a simple trigger but we can also create a nester trigger that can do multiple process also handling it and terminating it at the right time is very important if you don't end the trigger properly it may lead to an infinite Loop you might be wondering in which scenario we can use the nested trigger rather than giving you a tailored answer let me share a scenario with you which will help you in understanding the N trigger in a better way continuing from the earlier scenario Anna sent an email for every new customer that was added to the company's database now what if she wishes to keep a track of numbers of customers to whom the email was sent now Anna needs to create a nested trigger to keep the track of the count along with sending an email doesn't it sound simple so that was all about the syntax of trigger let's now try to implement an example of trigger in SQL so the next topic is operations in trigger we can perform many operations using triggers some may be simple and some may be little complex but once if we go through the query it's easy to understand the first operation is drop we can use drop command to remove a trigger from the database and the Syntax for this is drop trigger trigger name to understand this in a better way let's head to my SQL workbench I hope you know how to use my SQL workbench if not then check SQL basics for beginner video by edura for better understanding of how my SQL workbench will work to drop a trigger from the database the trigger has to be present present in our database so this is a syntax used to drop a trigger so let me execute this query so we can see in the status bar the trigger with the name sample trigger has been dropped let's head to the next operation that is display we can use show command to display the triggers from the database and the Syntax for this is show triggers in database name so let me head back to my SQL workbench to execute this command to display a trigger the trigger has to be present in the database so let me execute this SQL query to display the triggers that are present in my database I can see that the trigger with the name sample trigger is present in my database edura so let's head to the next operation the next operation is insert and the first variant is before insert triggers before insert triggers are used to update or validate record values before they save to database and the Syntax for this is create trigger calculate here calculate is the name of the trigger before insert on the table name for each row and set operation so let me head back to my SQL workbench to execute this query before explaining before insert trigger let me explain something else I will be using student table and The Columns of this table are it has student ID first name last name address City and marks I will be creating a trigger with the name sample trigger here before insert trigger is used to set 100 marks extra for each and every student marks column when the new student value is added to the table so let me execute this trigger and we can see that the trigger has been created now let me insert a record into the table I'll be using insert into command to insert a record into the table so let me execute this query but before that make sure to remember I will be adding details of wami K his marks will be 478 but as we have created a trigger it has to add 100 marks extra and the resulting marks has to be 578 so let me execute this query okay the query has been inserted so let me check the table and we can see that the marks of wami K is 578 so we can clearly say that the trigger has worked so the next variant of insert is after insert triggers after insert triggers are used to access Fields values that are set by the system and to affect changes in other records and the syntax is create trigger name of the trigger that is total Mark after insert on student student is a table name for each row insert into final marks value of the column so let's head back to my SQL workbench to execute this example to use this variant we need one more table that is final marks table where the trigger will store the results this SQL query will create a table with the name final marks so let me execute this query so we can see in the status bar the table with the name final marks has been created whenever we insert data to student table the previous trigger that is sample trigger will be executed now with the help of after insert trigger let's store the marks of the student table in the final Mark table this will be the after insert trigger and the name of this trigger is scal we will be updating total marks from student table to final marks table let me execute this trigger and we can see that the trigger with the name Cal has been created to check the working of a trigger named Cal let me insert the data of rajit Kumar into student table and make a note his marks is 500 so we can see that the data has been inserted into the student table let me execute this query and we can see that the student name rajit Kumar has 600 marks let's move to the next topic that is advantage and disadvantage let's look at the advantages of a trigger first one is forcing security approvals on the table that are present in the database the next one is triggers provide another way to check the Integrity of the data the third one is triggers counter react invalid exchanges the fourth one is triggers handle errors from the database layer and the final one is normally triggers can be useful for inspecting the data changes in the table let's look at the disadvantage of a trigger triggers can only provide extended validation that is not all kind of validations for simple validations you cannot use Nal unique check and foreign key constraints in a given trigger the next one is triggers may increase the overate of a database and the final disadvantage is triggers can be difficult to troubleshoot because they execute automatically in the database this may not be visible to client applications this brings us to the end of this trigger in SQL session I hope you understood the concepts of trigger that's all from my side for today's session thank you and have a great day