{"id":260,"date":"2018-07-13T07:03:57","date_gmt":"2018-07-13T07:03:57","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&#038;p=260"},"modified":"2018-12-07T11:45:29","modified_gmt":"2018-12-07T11:45:29","slug":"triggers-in-mysql","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/triggers-in-mysql\/","title":{"rendered":"Triggers in MySQL"},"content":{"raw":"<div><span style=\"float: right\"><a href=\"https:\/\/youtu.be\/9nC3CmHVkPU\" target=\"_blank\" rel=\"noopener\"><img src=\"http:\/\/epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/2018\/11\/download.png\" alt=\"epgp books\" width=\"75px\" height=\"75px;\" \/><\/a>\r\n<\/span><\/div>\r\n<div>\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n<strong>Introduction<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">\u2022\u00a0 Database Triggers are the database objects which reside in system catalog. The triggers are special type of procedures which can be called implicitly.<\/p>\r\n<p style=\"text-align: justify\">\u2022 Each trigger is associated with a table which can be activated on any DML statement like (Insert , update or delete).<\/p>\r\n&nbsp;\r\n\r\n<strong>Why Triggers<\/strong>\r\n\r\n&nbsp;\r\n\r\n\u2022 Triggers help you to enforce business rules\r\n\r\n\u2022 Triggers help you to validate the data even before they are inserted or updated\r\n\r\n\u2022 Triggers help you to keep log of records like maintaining audit trail\r\n\r\n\u2022 Triggers help you to enforce security authorization\r\n\r\n&nbsp;\r\n\r\n<strong>Limitations<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">\u2022\u00a0 Triggers increase overhead on system as they are called on each update\/insert , wherever applied which results into system running slower<\/p>\r\n\u2022 It is difficult to view the triggers as compared to viewing constraints , relationships, indexes etc.\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\n<strong>Syntax of Trigger:<\/strong>\r\n\r\n&nbsp;\r\n\r\nCREATE\r\n\r\n[DEFINER = { user | CURRENT_USER }]\r\n\r\nTRIGGER trigger_name\r\n\r\ntrigger_time trigger_event\r\n\r\nON tbl_name FOR EACH ROW\r\n\r\n[trigger_order]\r\n\r\ntrigger_body\r\n\r\ntrigger_time: { BEFORE | AFTER }\r\n\r\ntrigger_event: { INSERT | UPDATE | DELETE }\r\n\r\ntrigger_order: { FOLLOWS | PRECEDES } other_trigger_name\r\n\r\n<\/div>\r\n&nbsp;\r\n\r\n<strong style=\"text-align: initial;font-size: 1em\">Events of Trigger<\/strong>\r\n<div>\r\n<ul>\r\n \t<li style=\"text-align: justify\">Trigger time is the action time of the trigger. Action refers to before or after which denotes the trigger is to be activated before or after each row to be processed.<\/li>\r\n \t<li>Trigger event specifies the action which will activate the trigger.<\/li>\r\n \t<li>The trigger event allowed are :<\/li>\r\n \t<li>Insert : Whenever a new row is inserted<\/li>\r\n \t<li>Update : Whenever existing row is modified<\/li>\r\n \t<li>Delete : Whenever existing row is deleted<\/li>\r\n<\/ul>\r\n&nbsp;\r\n\r\nTrigger Body\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">\u2022 Body contains various statements to be executed when trigger activates. But if the trigger body contains multiple statements you must use Begin\u2026 End block.<\/p>\r\n\u2022 Certain statements not permitted in stored procedures are\r\n\r\n\u2022 Lock\u2026unlock table\r\n\r\n\u2022 Alter table\r\n\r\n\u2022 Alter View, etc\u2026:OLD and :New\r\n\r\n\u2022 To refer columns in trigger body two aliases are used , :old and :new\r\n\r\n\u2022 :old \u2013 It refers to existing row before it is updated or deleted.\r\n\r\n\u2022 :new \u2013 It refers to the new row which is to be inserted or and existing row after it is updated.\r\n\r\n<\/div>\r\n&nbsp;\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">Defining a Trigger<\/span>\r\n<div>\r\n\r\n\u00a0 \u00a0To create a new trigger , Right click on Table and select alter table.\r\n\r\n<\/div>\r\n<img class=\"alignnone size-full wp-image-263 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-121.png\" alt=\"\" width=\"719\" height=\"393\" \/>\r\n<div>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Once you click Alter table , table screen open which has multiple tabs, Select Trigger tab and you can see the different types of trigger available<\/p>\r\n&nbsp;\r\n\r\n<img class=\"alignnone size-full wp-image-264 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-122.png\" alt=\"\" width=\"715\" height=\"339\" \/>\r\n\r\n&nbsp;\r\n\r\nYou will require one LOG table to see the effect of Trigger execution.\r\n\r\n&nbsp;\r\n\r\nStructure of LOG table is as follows: (empid, name , salary , action, upd_date)\r\n\r\n<\/div>\r\n<img class=\"alignnone size-full wp-image-265 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-123.png\" alt=\"\" width=\"676\" height=\"360\" \/>\r\n<div>\r\n\r\n&nbsp;\r\n\r\nExample of <strong>After Insert Trigger<\/strong>\r\n\r\n&nbsp;\r\n\r\nUse \u2018test\u2019;\r\n\r\n&nbsp;\r\n\r\nCreate trigger emp_AIns after insert on emp for each row\r\n\r\nBegin\r\n\r\nInsert into emp_log values(new.empid, new.name, new.salary, \u2018Insert\u2019 , now());\r\n\r\nEnd;\r\n\r\n&nbsp;\r\n\r\n<img class=\"alignnone size-full wp-image-266 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-124.png\" alt=\"\" width=\"504\" height=\"367\" \/>\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">After writing the code for the above trigger you can test it by inserting a row in emp table and you will notice the corresponding effect in emp_log table.<\/p>\r\n\r\n<\/div>\r\n&nbsp;\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">Let us look at another example for <\/span><strong style=\"text-align: initial;font-size: 1em\">Before Insert Trigger<\/strong>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">Before Inserts are usually used for validations and after insert are used for Logs<\/span>\r\n<div>\r\n\r\n&nbsp;\r\n\r\n<strong>Before Insert<\/strong>\r\n\r\n&nbsp;\r\n\r\nCreate trigger emp_BIns before insert on emp for each row\r\n\r\nBegin\r\n\r\nIf new.salary=0 then\r\n\r\nSet new.salary = 100;\r\n\r\nEnd if\r\n\r\nEnd;\r\n\r\n&nbsp;\r\n\r\nHere, we keep minimum salary as 100, So , if salary as 0 is entered , it is changed to 100.\r\n\r\n&nbsp;\r\n\r\n<img class=\"alignnone size-full wp-image-267 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-125.png\" alt=\"\" width=\"637\" height=\"323\" \/>\r\n\r\n&nbsp;\r\n\r\nYou can test the trigger by inserting a new row, and entering 0 as salary.\r\n\r\n<\/div>\r\n&nbsp;\r\n\r\n<span style=\"text-align: justify;font-size: 1em\">Similarly examples for <\/span><strong style=\"text-align: justify;font-size: 1em\">Before Update<\/strong><span style=\"text-align: justify;font-size: 1em\"> and <\/span><strong style=\"text-align: justify;font-size: 1em\">After Update<\/strong><span style=\"text-align: justify;font-size: 1em\"> are done , And the same rule applies , <\/span><strong style=\"text-align: justify;font-size: 1em\">before<\/strong><span style=\"text-align: justify;font-size: 1em\"> is <\/span><strong style=\"text-align: justify;font-size: 1em\">validating <\/strong><span style=\"text-align: justify;font-size: 1em\">and<\/span><strong style=\"text-align: justify;font-size: 1em\"> after <\/strong><span style=\"text-align: justify;font-size: 1em\">is<\/span><strong style=\"text-align: justify;font-size: 1em\"> maintaining log<\/strong>\r\n<div>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Here, we have created one more table name emp_log1(Username,description) wherein we maintain the user information about which user updates the data.<\/p>\r\n&nbsp;\r\n\r\n<strong>After Update<\/strong>\r\n\r\n&nbsp;\r\n\r\nCreate trigger emp_AUPD after update on emp for each row\r\n\r\nBegin\r\n\r\nInsert into emp_log1 values(user(), concat(\u2018Update employee Salary , Name : \u2018, old.name, \u2018OLD\r\n\r\nsalary :\u2019, old.salary , \u2018New Salary : \u2018 , new.salary ) );\r\n\r\nEnd;\r\n\r\n<img class=\"alignnone size-full wp-image-268 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-126.png\" alt=\"\" width=\"669\" height=\"314\" \/>\r\n\r\n&nbsp;\r\n\r\n<strong>Test after Update<\/strong>\r\n\r\n&nbsp;\r\n\r\nFirst check the data in emp_log1\r\n\r\nSelect * from emp_log1;\r\n\r\nYou will not get any rows\r\n\r\nThen, view the data in <strong>emp<\/strong> table\r\n\r\nThen issue the following statement :\r\n\r\n<\/div>\r\n&nbsp;\r\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Update emp set salary = salary + 500; (Note : we are not giving where condition , hence will affect all rows)<\/span><\/p>\r\n\r\n<div>\r\n\r\n&nbsp;\r\n\r\nNow , issue following statement\r\n\r\nSelect * from emp_log1;\r\n\r\n&nbsp;\r\n\r\n<img class=\"alignnone size-full wp-image-269 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-127.png\" alt=\"\" width=\"711\" height=\"358\" \/>\r\n\r\n&nbsp;\r\n\r\n&nbsp;\r\n\r\nSimilarly, in before update , we check the salary updated should not be less than the existing salary.\r\n\r\nSimilar type of trigger like <strong>Before Insert.<\/strong>\r\n\r\n<\/div>\r\n<table>\r\n<tbody>\r\n<tr>\r\n<td><strong>you can view video on Triggers in MySQL<\/strong><\/td>\r\n<td><a href=\"https:\/\/youtu.be\/9nC3CmHVkPU\" target=\"_blank\" rel=\"noopener\"><img class=\"alignnone wp-image-120\" src=\"http:\/\/epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/2018\/11\/download.png\" alt=\"\" width=\"36\" height=\"36\" \/><\/a><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n\r\n<strong style=\"text-align: initial;font-size: 1em\">References:<\/strong>\r\n<div>\r\n\r\n&nbsp;\r\n\r\n1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers\r\n\r\n2) Learning PHP, MySQL &amp; JavaScript , Robin Nixon , O\u2019Reilly Publications\r\n<p style=\"text-align: justify\">3) PHP and MySQL Web Development , Luke Welling , Laura Thomson , Pearson Publications, Fourth Edition<\/p>\r\n4) MySQL Documentation : https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/create-procedure.html\r\n\r\n5) www.mysqltutorial.org\r\n\r\n&nbsp;\r\n\r\nAdditional Reading:\r\n\r\n&nbsp;\r\n\r\n1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers\r\n\r\n2) MysQL documentation : dev.mysql.com\/doc\/\r\n\r\n&nbsp;\r\n\r\n<\/div>","rendered":"<div><span style=\"float: right\"><a href=\"https:\/\/youtu.be\/9nC3CmHVkPU\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" src=\"http:\/\/epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/2018\/11\/download.png\" alt=\"epgp books\" width=\"75px\" height=\"75px;\" \/><\/a><br \/>\n<\/span><\/div>\n<div>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Introduction<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">\u2022\u00a0 Database Triggers are the database objects which reside in system catalog. The triggers are special type of procedures which can be called implicitly.<\/p>\n<p style=\"text-align: justify\">\u2022 Each trigger is associated with a table which can be activated on any DML statement like (Insert , update or delete).<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Why Triggers<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>\u2022 Triggers help you to enforce business rules<\/p>\n<p>\u2022 Triggers help you to validate the data even before they are inserted or updated<\/p>\n<p>\u2022 Triggers help you to keep log of records like maintaining audit trail<\/p>\n<p>\u2022 Triggers help you to enforce security authorization<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Limitations<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">\u2022\u00a0 Triggers increase overhead on system as they are called on each update\/insert , wherever applied which results into system running slower<\/p>\n<p>\u2022 It is difficult to view the triggers as compared to viewing constraints , relationships, indexes etc.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Syntax of Trigger:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>CREATE<\/p>\n<p>[DEFINER = { user | CURRENT_USER }]<\/p>\n<p>TRIGGER trigger_name<\/p>\n<p>trigger_time trigger_event<\/p>\n<p>ON tbl_name FOR EACH ROW<\/p>\n<p>[trigger_order]<\/p>\n<p>trigger_body<\/p>\n<p>trigger_time: { BEFORE | AFTER }<\/p>\n<p>trigger_event: { INSERT | UPDATE | DELETE }<\/p>\n<p>trigger_order: { FOLLOWS | PRECEDES } other_trigger_name<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><strong style=\"text-align: initial;font-size: 1em\">Events of Trigger<\/strong><\/p>\n<div>\n<ul>\n<li style=\"text-align: justify\">Trigger time is the action time of the trigger. Action refers to before or after which denotes the trigger is to be activated before or after each row to be processed.<\/li>\n<li>Trigger event specifies the action which will activate the trigger.<\/li>\n<li>The trigger event allowed are :<\/li>\n<li>Insert : Whenever a new row is inserted<\/li>\n<li>Update : Whenever existing row is modified<\/li>\n<li>Delete : Whenever existing row is deleted<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Trigger Body<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">\u2022 Body contains various statements to be executed when trigger activates. But if the trigger body contains multiple statements you must use Begin\u2026 End block.<\/p>\n<p>\u2022 Certain statements not permitted in stored procedures are<\/p>\n<p>\u2022 Lock\u2026unlock table<\/p>\n<p>\u2022 Alter table<\/p>\n<p>\u2022 Alter View, etc\u2026:OLD and :New<\/p>\n<p>\u2022 To refer columns in trigger body two aliases are used , :old and :new<\/p>\n<p>\u2022 :old \u2013 It refers to existing row before it is updated or deleted.<\/p>\n<p>\u2022 :new \u2013 It refers to the new row which is to be inserted or and existing row after it is updated.<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">Defining a Trigger<\/span><\/p>\n<div>\n<p>\u00a0 \u00a0To create a new trigger , Right click on Table and select alter table.<\/p>\n<\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-263 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-121.png\" alt=\"\" width=\"719\" height=\"393\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-121.png 719w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-121-300x164.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-121-65x36.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-121-225x123.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-121-350x191.png 350w\" sizes=\"auto, (max-width: 719px) 100vw, 719px\" \/><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Once you click Alter table , table screen open which has multiple tabs, Select Trigger tab and you can see the different types of trigger available<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-264 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-122.png\" alt=\"\" width=\"715\" height=\"339\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-122.png 715w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-122-300x142.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-122-65x31.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-122-225x107.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-122-350x166.png 350w\" sizes=\"auto, (max-width: 715px) 100vw, 715px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>You will require one LOG table to see the effect of Trigger execution.<\/p>\n<p>&nbsp;<\/p>\n<p>Structure of LOG table is as follows: (empid, name , salary , action, upd_date)<\/p>\n<\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-265 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-123.png\" alt=\"\" width=\"676\" height=\"360\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-123.png 676w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-123-300x160.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-123-65x35.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-123-225x120.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-123-350x186.png 350w\" sizes=\"auto, (max-width: 676px) 100vw, 676px\" \/><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p>Example of <strong>After Insert Trigger<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>Use \u2018test\u2019;<\/p>\n<p>&nbsp;<\/p>\n<p>Create trigger emp_AIns after insert on emp for each row<\/p>\n<p>Begin<\/p>\n<p>Insert into emp_log values(new.empid, new.name, new.salary, \u2018Insert\u2019 , now());<\/p>\n<p>End;<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-266 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-124.png\" alt=\"\" width=\"504\" height=\"367\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-124.png 504w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-124-300x218.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-124-65x47.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-124-225x164.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-124-350x255.png 350w\" sizes=\"auto, (max-width: 504px) 100vw, 504px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">After writing the code for the above trigger you can test it by inserting a row in emp table and you will notice the corresponding effect in emp_log table.<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">Let us look at another example for <\/span><strong style=\"text-align: initial;font-size: 1em\">Before Insert Trigger<\/strong><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">Before Inserts are usually used for validations and after insert are used for Logs<\/span><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p><strong>Before Insert<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>Create trigger emp_BIns before insert on emp for each row<\/p>\n<p>Begin<\/p>\n<p>If new.salary=0 then<\/p>\n<p>Set new.salary = 100;<\/p>\n<p>End if<\/p>\n<p>End;<\/p>\n<p>&nbsp;<\/p>\n<p>Here, we keep minimum salary as 100, So , if salary as 0 is entered , it is changed to 100.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-267 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-125.png\" alt=\"\" width=\"637\" height=\"323\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-125.png 637w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-125-300x152.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-125-65x33.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-125-225x114.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-125-350x177.png 350w\" sizes=\"auto, (max-width: 637px) 100vw, 637px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>You can test the trigger by inserting a new row, and entering 0 as salary.<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"text-align: justify;font-size: 1em\">Similarly examples for <\/span><strong style=\"text-align: justify;font-size: 1em\">Before Update<\/strong><span style=\"text-align: justify;font-size: 1em\"> and <\/span><strong style=\"text-align: justify;font-size: 1em\">After Update<\/strong><span style=\"text-align: justify;font-size: 1em\"> are done , And the same rule applies , <\/span><strong style=\"text-align: justify;font-size: 1em\">before<\/strong><span style=\"text-align: justify;font-size: 1em\"> is <\/span><strong style=\"text-align: justify;font-size: 1em\">validating <\/strong><span style=\"text-align: justify;font-size: 1em\">and<\/span><strong style=\"text-align: justify;font-size: 1em\"> after <\/strong><span style=\"text-align: justify;font-size: 1em\">is<\/span><strong style=\"text-align: justify;font-size: 1em\"> maintaining log<\/strong><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Here, we have created one more table name emp_log1(Username,description) wherein we maintain the user information about which user updates the data.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>After Update<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>Create trigger emp_AUPD after update on emp for each row<\/p>\n<p>Begin<\/p>\n<p>Insert into emp_log1 values(user(), concat(\u2018Update employee Salary , Name : \u2018, old.name, \u2018OLD<\/p>\n<p>salary :\u2019, old.salary , \u2018New Salary : \u2018 , new.salary ) );<\/p>\n<p>End;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-268 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-126.png\" alt=\"\" width=\"669\" height=\"314\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-126.png 669w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-126-300x141.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-126-65x31.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-126-225x106.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-126-350x164.png 350w\" sizes=\"auto, (max-width: 669px) 100vw, 669px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Test after Update<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>First check the data in emp_log1<\/p>\n<p>Select * from emp_log1;<\/p>\n<p>You will not get any rows<\/p>\n<p>Then, view the data in <strong>emp<\/strong> table<\/p>\n<p>Then issue the following statement :<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Update emp set salary = salary + 500; (Note : we are not giving where condition , hence will affect all rows)<\/span><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p>Now , issue following statement<\/p>\n<p>Select * from emp_log1;<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-269 aligncenter\" src=\"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/29\/2018\/07\/1-127.png\" alt=\"\" width=\"711\" height=\"358\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-127.png 711w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-127-300x151.png 300w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-127-65x33.png 65w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-127-225x113.png 225w, https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-content\/uploads\/sites\/29\/2018\/07\/1-127-350x176.png 350w\" sizes=\"auto, (max-width: 711px) 100vw, 711px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Similarly, in before update , we check the salary updated should not be less than the existing salary.<\/p>\n<p>Similar type of trigger like <strong>Before Insert.<\/strong><\/p>\n<\/div>\n<table>\n<tbody>\n<tr>\n<td><strong>you can view video on Triggers in MySQL<\/strong><\/td>\n<td><a href=\"https:\/\/youtu.be\/9nC3CmHVkPU\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-120\" src=\"http:\/\/epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/2018\/11\/download.png\" alt=\"\" width=\"36\" height=\"36\" \/><\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong style=\"text-align: initial;font-size: 1em\">References:<\/strong><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p>1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers<\/p>\n<p>2) Learning PHP, MySQL &amp; JavaScript , Robin Nixon , O\u2019Reilly Publications<\/p>\n<p style=\"text-align: justify\">3) PHP and MySQL Web Development , Luke Welling , Laura Thomson , Pearson Publications, Fourth Edition<\/p>\n<p>4) MySQL Documentation : https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/create-procedure.html<\/p>\n<p>5) www.mysqltutorial.org<\/p>\n<p>&nbsp;<\/p>\n<p>Additional Reading:<\/p>\n<p>&nbsp;<\/p>\n<p>1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers<\/p>\n<p>2) MysQL documentation : dev.mysql.com\/doc\/<\/p>\n<p>&nbsp;<\/p>\n<\/div>\n","protected":false},"author":3,"menu_order":28,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["miss-bhumika-shah"],"pb_section_license":""},"chapter-type":[],"contributor":[60],"license":[],"class_list":["post-260","chapter","type-chapter","status-publish","hentry","contributor-miss-bhumika-shah"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/users\/3"}],"version-history":[{"count":9,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/260\/revisions"}],"predecessor-version":[{"id":515,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/260\/revisions\/515"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/260\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/media?parent=260"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapter-type?post=260"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/contributor?post=260"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/license?post=260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}