{"id":271,"date":"2018-07-13T07:14:19","date_gmt":"2018-07-13T07:14:19","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=271"},"modified":"2018-08-13T05:46:51","modified_gmt":"2018-08-13T05:46:51","slug":"triggers-in-mysql-contd","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/triggers-in-mysql-contd\/","title":{"rendered":"Triggers in MySQL (contd.)"},"content":{"raw":"
\r\n\r\n \r\n\r\nIntroduction<\/strong>\r\n\r\n \r\n

\u2022 Database Triggers are the database objects which reside in system catalog. The triggers are special Type\u00a0 \u00a0of procedures which can be called implicitly.<\/p>\r\n

\u2022 Each trigger is associated with a table which can be activated on any DML statement like (Insert ,update or delete).<\/p>\r\n \r\n\r\nImplementation of Triggers:\r\n\r\n \r\n

In the previous module, we have already seen the introduction and partial implementation of Triggers. Hence , let us continue with remaining types of Triggers<\/p>\r\n \r\n\r\nTo have a quick recap of tables we have used :\r\n\r\n \r\n\r\nEmp Table\r\n\r\nEmp_log table\r\n\r\n<\/div>\r\n\"\"\r\n

\r\n\r\n \r\n

You will require one LOG table to see the effect of Trigger execution. Structure of LOG table is as follows: (empid, name , salary , action, upd_date)<\/p>\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\nExample of Before Delete Trigger<\/strong>\r\n\r\n \r\n\r\nUse \u2018test\u2019;\r\n\r\nCreate trigger emp_Bdel before delete on emp for each row\r\n\r\nBegin\r\n\r\nInsert into emp_log values(old.empid, old.name, old.salary, \u2018Delete\u2019 , now(),user());\r\n\r\nEnd;\r\n\r\n \r\n\r\nCompile and Run the Trigger\r\n\r\n<\/div>\r\n\"\"\r\n

\r\n\r\n \r\n

After writing the code for the above trigger you can test it by deleting a row from emp table and you will notice the corresponding effect in emp_log table.<\/p>\r\n \r\n\r\nSo, First view records in emp_log\r\n\r\n \r\n\r\nSelect * from emp_log;\r\n\r\nThen :\r\n\r\nDelete from emp where emp_id=7;\r\n\r\n \r\n\r\nAnd now, again see the corresponding effect in emp_log table.\r\n\r\n \r\n\r\nIn all the examples previously we saw, After Triggers first and then Before Triggers\r\n\r\nBut , Delete is the exception and there is a reason behind it.\r\n\r\nIn After Delete, there\u2019s is nothing much to maintain. The only thing you can maintain is User Log , i.e.\r\n\r\nwho deleted the record and what was the old value.\r\n\r\n \r\n\r\nAfter Delete Trigger<\/strong>\r\n\r\n \r\n\r\nExample of After Delete Trigger<\/strong>\r\n\r\n \r\n\r\nUse \u2018test\u2019;\r\n\r\nCreate trigger emp_Adel after delete on emp for each row\r\n\r\nBegin\r\n\r\nInsert into emp_log1 values(user(), concat(\u2018Delete employee Salary , Name : \u2018, old.name, \u2018OLD salary :\u2019, old.salary) );<\/span>\r\n\r\nEnd;<\/span>\r\n\r\n<\/div>\r\n

\r\n\r\n \r\n\r\nCompile and Run the Trigger\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\nYou can test the trigger by deleting a row and check the effect in emp_log1 table.\r\n\r\n \r\n

After writing the code for the above trigger you can test it by deleting a row from emp table and you will notice the corresponding effect in emp_log1 and emp_log both tables.<\/p>\r\n \r\n\r\nSo, First view records in emp_log1\r\n\r\n \r\n\r\nSelect * from emp_log1;\r\n\r\nThen :\r\n\r\nDelete from emp where emp_id=6;\r\n\r\nAnd now, again see the corresponding effect in emp_log1 and emp_log table.\r\n\r\n \r\n\r\nRestrictions on Triggers<\/strong>\r\n\r\n \r\n\r\n\u2022 Return statement is not permitted in triggers\r\n\r\n\u2022 Trigger cache does not detect metadata of the objects\r\n\r\n\u2022 Triggers cannot be activated by referential integrities(foreign key actions)\r\n\r\n\u2022 Triggers do not work for system tables\r\n\r\n\u2022\u00a0<\/span>To delete the trigger<\/strong>\r\n\r\n\u2022 Drop trigger <triggername><\/span>\r\n\r\n\u2022\u00a0<\/span>To view triggers<\/strong>\r\n\r\n\u2022 Show triggers<\/span>\r\n\r\n<\/div>\r\n

\r\n\r\n\"\"\r\n\r\n<\/div>\r\n\u00a0 \u00a0 Views in MySQL<\/strong>\r\n\r\n \r\n\r\nWhat are views<\/strong>\r\n
\r\n\r\n \r\n

\u2022 Views are the logical tables which do not exist physically<\/p>\r\n

\u2022 They are type of queries which are stored and can be referred to at a later stage<\/p>\r\n

\u2022 Views will not store any data but just display the data from the existing tables<\/p>\r\n

\u2022\u00a0 Some views are updateable, for views to be updateable there should be 1:1 relationship between view\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0and underlying table(s)<\/p>\r\n

\u2022 The use of complex queries might also make view non-updateable<\/p>\r\n

<\/p>\r\nAdvantages & Limitations of Views<\/strong>\r\n\r\n \r\n\r\n\u2022 Advantages\r\n\r\n\u2022 Simplification of Complex Queries\r\n\r\n\u2022 Selective columns can be displayed\/hidden from the underlying table\r\n\r\n\u2022 Security\r\n\r\n\u2022 Calculated\/Generated Columns\r\n\r\n\u2022 Backward Compatibility\r\n\r\n\u2022 No Data Storage required\r\n\r\n\u2022 Limitation\r\n\r\n\u2022 Performance Considerations\r\n\r\n\u2022 Dependency on tables\r\n\r\n\u2022 Syntax\r\n\r\n \r\n\r\nCreate [OR REPLACE]\r\n\r\n[ALGORITHM = {UNDIFENED |MERGER| TEMPTABLE} ]\r\n\r\nDEFINER = {USER | CURRENT_USER}]\r\n\r\n[SQL SECURITY { DEFINER | INVOKER }]\r\n\r\nVIEW view_name [(column_list)]\u00a0<\/span>\r\n\r\nAS select_statement\u00a0<\/span>\r\n\r\n[WITH [CASCADED |LOCAL] CHECK OPTION]<\/span>\r\n\r\n<\/div>\r\n\"\"\r\n

\r\n\r\n \r\n\r\nYou can observe ,the Create View command in the navigator pane.\r\n\r\n \r\n\r\nCreating a View :<\/strong>\r\n\r\nCreate view \u2018new_view\u2018 as<\/strong>\r\n\r\n\u00a0Select name,designation from emp;<\/strong>\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\n \r\n

It can be observed that on applying the view, how the compiler has added the algorithm and aliases.<\/p>\r\nTo test ,\r\n\r\n \r\n

Select * from new_view;<\/p>\r\n

Update new_view set designation = \u2018\u00c7lerk\u2019 where name = \u2018raj\u2019<\/p>\r\nAnd, observe the corresponding effects in the respective view and table.\r\n\r\n \r\n\r\nViews with aggregate functions<\/strong>\r\n\r\n \r\n\r\nCreate view \u2018Maxsal\u2019 AS\r\n\r\nSelect Designation , max(salary) from emp\r\n\r\nGroup by designation;\r\n\r\n<\/div>\r\n

\r\n\r\n\"\"\r\n\r\n \r\n\r\nTo Test:\r\n\r\n \r\n

It displays you, designation wise maximum salary. If you also want to view the name of the employee with maximum salary, you can add name in the query.<\/p>\r\n \r\n\r\nBut, you can\u2019t insert in those types of views.\r\n\r\n \r\n\r\nUpdateable Views<\/strong>\r\n\r\n \r\n\r\n\u2022 A view is updateable only if\r\n\r\n\u2022 The select statement refers to only one table\r\n\r\n\u2022 The select statement is not using any aggregate functions\r\n\r\n\u2022 The select statement has not used Distinct\r\n\r\n\u2022 The select statement is not referring to other views which are read-only\r\n\r\n \r\n\r\nViews from Multiple Tables<\/strong>\r\n\r\n \r\n\r\nViews can be created from multiple tables also.<\/strong>\r\n\r\n<\/div>\r\n\u00a0<\/strong>\u00a0 \u00a0 Create View \u2018empdept\u2019 As<\/span>\r\n\r\nSelect e.empid,e.name, e.salary , e.designation,<\/span>\r\n\r\nd.deptid, d.dname , d.location<\/span>\r\n\r\nfrom emp e, dept d<\/span>\r\n\r\nwhere e.deptid = d.deptid;<\/span>\r\n

\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\nTo view :<\/strong>\r\n\r\n \r\n\r\nSelect * from empdept;\r\n\r\n<\/div>\r\n
\r\n\r\n\"\"\r\n\r\n<\/div>\r\nAdditional Reading:<\/strong>\r\n1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers\r\n2) MySQL documentation : dev.mysql.com\/doc\/","rendered":"
\n

 <\/p>\n

Introduction<\/strong><\/p>\n

 <\/p>\n

\u2022 Database Triggers are the database objects which reside in system catalog. The triggers are special Type\u00a0 \u00a0of procedures which can be called implicitly.<\/p>\n

\u2022 Each trigger is associated with a table which can be activated on any DML statement like (Insert ,update or delete).<\/p>\n

 <\/p>\n

Implementation of Triggers:<\/p>\n

 <\/p>\n

In the previous module, we have already seen the introduction and partial implementation of Triggers. Hence , let us continue with remaining types of Triggers<\/p>\n

 <\/p>\n

To have a quick recap of tables we have used :<\/p>\n

 <\/p>\n

Emp Table<\/p>\n

Emp_log table<\/p>\n<\/div>\n

\"\"<\/p>\n

\n

 <\/p>\n

You will require one LOG table to see the effect of Trigger execution. Structure of LOG table is as follows: (empid, name , salary , action, upd_date)<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

Example of Before Delete Trigger<\/strong><\/p>\n

 <\/p>\n

Use \u2018test\u2019;<\/p>\n

Create trigger emp_Bdel before delete on emp for each row<\/p>\n

Begin<\/p>\n

Insert into emp_log values(old.empid, old.name, old.salary, \u2018Delete\u2019 , now(),user());<\/p>\n

End;<\/p>\n

 <\/p>\n

Compile and Run the Trigger<\/p>\n<\/div>\n

\"\"<\/p>\n

\n

 <\/p>\n

After writing the code for the above trigger you can test it by deleting a row from emp table and you will notice the corresponding effect in emp_log table.<\/p>\n

 <\/p>\n

So, First view records in emp_log<\/p>\n

 <\/p>\n

Select * from emp_log;<\/p>\n

Then :<\/p>\n

Delete from emp where emp_id=7;<\/p>\n

 <\/p>\n

And now, again see the corresponding effect in emp_log table.<\/p>\n

 <\/p>\n

In all the examples previously we saw, After Triggers first and then Before Triggers<\/p>\n

But , Delete is the exception and there is a reason behind it.<\/p>\n

In After Delete, there\u2019s is nothing much to maintain. The only thing you can maintain is User Log , i.e.<\/p>\n

who deleted the record and what was the old value.<\/p>\n

 <\/p>\n

After Delete Trigger<\/strong><\/p>\n

 <\/p>\n

Example of After Delete Trigger<\/strong><\/p>\n

 <\/p>\n

Use \u2018test\u2019;<\/p>\n

Create trigger emp_Adel after delete on emp for each row<\/p>\n

Begin<\/p>\n

Insert into emp_log1 values(user(), concat(\u2018Delete employee Salary , Name : \u2018, old.name, \u2018OLD salary :\u2019, old.salary) );<\/span><\/p>\n

End;<\/span><\/p>\n<\/div>\n

\n

 <\/p>\n

Compile and Run the Trigger<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

You can test the trigger by deleting a row and check the effect in emp_log1 table.<\/p>\n

 <\/p>\n

After writing the code for the above trigger you can test it by deleting a row from emp table and you will notice the corresponding effect in emp_log1 and emp_log both tables.<\/p>\n

 <\/p>\n

So, First view records in emp_log1<\/p>\n

 <\/p>\n

Select * from emp_log1;<\/p>\n

Then :<\/p>\n

Delete from emp where emp_id=6;<\/p>\n

And now, again see the corresponding effect in emp_log1 and emp_log table.<\/p>\n

 <\/p>\n

Restrictions on Triggers<\/strong><\/p>\n

 <\/p>\n

\u2022 Return statement is not permitted in triggers<\/p>\n

\u2022 Trigger cache does not detect metadata of the objects<\/p>\n

\u2022 Triggers cannot be activated by referential integrities(foreign key actions)<\/p>\n

\u2022 Triggers do not work for system tables<\/p>\n

\u2022\u00a0<\/span>To delete the trigger<\/strong><\/p>\n

\u2022 Drop trigger <triggername><\/span><\/p>\n

\u2022\u00a0<\/span>To view triggers<\/strong><\/p>\n

\u2022 Show triggers<\/span><\/p>\n<\/div>\n

\n

\"\"<\/p>\n<\/div>\n

\u00a0 \u00a0 Views in MySQL<\/strong><\/p>\n

 <\/p>\n

What are views<\/strong><\/p>\n

\n

 <\/p>\n

\u2022 Views are the logical tables which do not exist physically<\/p>\n

\u2022 They are type of queries which are stored and can be referred to at a later stage<\/p>\n

\u2022 Views will not store any data but just display the data from the existing tables<\/p>\n

\u2022\u00a0 Some views are updateable, for views to be updateable there should be 1:1 relationship between view\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0and underlying table(s)<\/p>\n

\u2022 The use of complex queries might also make view non-updateable<\/p>\n

\n

Advantages & Limitations of Views<\/strong><\/p>\n

 <\/p>\n

\u2022 Advantages<\/p>\n

\u2022 Simplification of Complex Queries<\/p>\n

\u2022 Selective columns can be displayed\/hidden from the underlying table<\/p>\n

\u2022 Security<\/p>\n

\u2022 Calculated\/Generated Columns<\/p>\n

\u2022 Backward Compatibility<\/p>\n

\u2022 No Data Storage required<\/p>\n

\u2022 Limitation<\/p>\n

\u2022 Performance Considerations<\/p>\n

\u2022 Dependency on tables<\/p>\n

\u2022 Syntax<\/p>\n

 <\/p>\n

Create [OR REPLACE]<\/p>\n

[ALGORITHM = {UNDIFENED |MERGER| TEMPTABLE} ]<\/p>\n

DEFINER = {USER | CURRENT_USER}]<\/p>\n

[SQL SECURITY { DEFINER | INVOKER }]<\/p>\n

VIEW view_name [(column_list)]\u00a0<\/span><\/p>\n

AS select_statement\u00a0<\/span><\/p>\n

[WITH [CASCADED |LOCAL] CHECK OPTION]<\/span><\/p>\n<\/div>\n

\"\"<\/p>\n

\n

 <\/p>\n

You can observe ,the Create View command in the navigator pane.<\/p>\n

 <\/p>\n

Creating a View :<\/strong><\/p>\n

Create view \u2018new_view\u2018 as<\/strong><\/p>\n

\u00a0Select name,designation from emp;<\/strong><\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

It can be observed that on applying the view, how the compiler has added the algorithm and aliases.<\/p>\n

To test ,<\/p>\n

 <\/p>\n

Select * from new_view;<\/p>\n

Update new_view set designation = \u2018\u00c7lerk\u2019 where name = \u2018raj\u2019<\/p>\n

And, observe the corresponding effects in the respective view and table.<\/p>\n

 <\/p>\n

Views with aggregate functions<\/strong><\/p>\n

 <\/p>\n

Create view \u2018Maxsal\u2019 AS<\/p>\n

Select Designation , max(salary) from emp<\/p>\n

Group by designation;<\/p>\n<\/div>\n

\n

\"\"<\/p>\n

 <\/p>\n

To Test:<\/p>\n

 <\/p>\n

It displays you, designation wise maximum salary. If you also want to view the name of the employee with maximum salary, you can add name in the query.<\/p>\n

 <\/p>\n

But, you can\u2019t insert in those types of views.<\/p>\n

 <\/p>\n

Updateable Views<\/strong><\/p>\n

 <\/p>\n

\u2022 A view is updateable only if<\/p>\n

\u2022 The select statement refers to only one table<\/p>\n

\u2022 The select statement is not using any aggregate functions<\/p>\n

\u2022 The select statement has not used Distinct<\/p>\n

\u2022 The select statement is not referring to other views which are read-only<\/p>\n

 <\/p>\n

Views from Multiple Tables<\/strong><\/p>\n

 <\/p>\n

Views can be created from multiple tables also.<\/strong><\/p>\n<\/div>\n

\u00a0<\/strong>\u00a0 \u00a0 Create View \u2018empdept\u2019 As<\/span><\/p>\n

Select e.empid,e.name, e.salary , e.designation,<\/span><\/p>\n

d.deptid, d.dname , d.location<\/span><\/p>\n

from emp e, dept d<\/span><\/p>\n

where e.deptid = d.deptid;<\/span><\/p>\n

\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

To view :<\/strong><\/p>\n

 <\/p>\n

Select * from empdept;<\/p>\n<\/div>\n

\n

\"\"<\/p>\n<\/div>\n

Additional Reading:<\/strong>
\n1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers
\n2) MySQL documentation : dev.mysql.com\/doc\/<\/p>\n","protected":false},"author":3,"menu_order":29,"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-271","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\/271","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":4,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/271\/revisions"}],"predecessor-version":[{"id":435,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/271\/revisions\/435"}],"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\/271\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/media?parent=271"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapter-type?post=271"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/contributor?post=271"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/license?post=271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}