29 Triggers in MySQL (contd.)
Bhumika Shah
Introduction
• Database Triggers are the database objects which reside in system catalog. The triggers are special Type of procedures which can be called implicitly.
• Each trigger is associated with a table which can be activated on any DML statement like (Insert ,update or delete).
Implementation of Triggers:
In the previous module, we have already seen the introduction and partial implementation of Triggers. Hence , let us continue with remaining types of Triggers
To have a quick recap of tables we have used :
Emp Table
Emp_log table
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)
Example of Before Delete Trigger
Use ‘test’;
Create trigger emp_Bdel before delete on emp for each row
Begin
Insert into emp_log values(old.empid, old.name, old.salary, ‘Delete’ , now(),user());
End;
Compile and Run the Trigger
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.
So, First view records in emp_log
Select * from emp_log;
Then :
Delete from emp where emp_id=7;
And now, again see the corresponding effect in emp_log table.
In all the examples previously we saw, After Triggers first and then Before Triggers
But , Delete is the exception and there is a reason behind it.
In After Delete, there’s is nothing much to maintain. The only thing you can maintain is User Log , i.e.
who deleted the record and what was the old value.
After Delete Trigger
Example of After Delete Trigger
Use ‘test’;
Create trigger emp_Adel after delete on emp for each row
Begin
Insert into emp_log1 values(user(), concat(‘Delete employee Salary , Name : ‘, old.name, ‘OLD salary :’, old.salary) );
End;
Compile and Run the Trigger
You can test the trigger by deleting a row and check the effect in emp_log1 table.
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.
So, First view records in emp_log1
Select * from emp_log1;
Then :
Delete from emp where emp_id=6;
And now, again see the corresponding effect in emp_log1 and emp_log table.
Restrictions on Triggers
• Return statement is not permitted in triggers
• Trigger cache does not detect metadata of the objects
• Triggers cannot be activated by referential integrities(foreign key actions)
• Triggers do not work for system tables
• To delete the trigger
• Drop trigger <triggername>
• To view triggers
• Show triggers
Views in MySQL
What are views
• Views are the logical tables which do not exist physically
• They are type of queries which are stored and can be referred to at a later stage
• Views will not store any data but just display the data from the existing tables
• Some views are updateable, for views to be updateable there should be 1:1 relationship between view and underlying table(s)
• The use of complex queries might also make view non-updateable
Advantages & Limitations of Views
• Advantages
• Simplification of Complex Queries
• Selective columns can be displayed/hidden from the underlying table
• Security
• Calculated/Generated Columns
• Backward Compatibility
• No Data Storage required
• Limitation
• Performance Considerations
• Dependency on tables
• Syntax
Create [OR REPLACE]
[ALGORITHM = {UNDIFENED |MERGER| TEMPTABLE} ]
DEFINER = {USER | CURRENT_USER}]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED |LOCAL] CHECK OPTION]
You can observe ,the Create View command in the navigator pane.
Creating a View :
Create view ‘new_view‘ as
Select name,designation from emp;
It can be observed that on applying the view, how the compiler has added the algorithm and aliases.
To test ,
Select * from new_view;
Update new_view set designation = ‘Çlerk’ where name = ‘raj’
And, observe the corresponding effects in the respective view and table.
Views with aggregate functions
Create view ‘Maxsal’ AS
Select Designation , max(salary) from emp
Group by designation;
To Test:
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.
But, you can’t insert in those types of views.
Updateable Views
• A view is updateable only if
• The select statement refers to only one table
• The select statement is not using any aggregate functions
• The select statement has not used Distinct
• The select statement is not referring to other views which are read-only
Views from Multiple Tables
Views can be created from multiple tables also.
Create View ‘empdept’ As
Select e.empid,e.name, e.salary , e.designation,
d.deptid, d.dname , d.location
from emp e, dept d
where e.deptid = d.deptid;
To view :
Select * from empdept;
Additional Reading:
1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers
2) MySQL documentation : dev.mysql.com/doc/