Triggers and Stored Procedure

TRIGGERS

SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs.

Implementation

• While trigger is implemented in MySQL has all features in standard SQL but there are some restrictions you should be aware.

Restrictions:

• It is not allowed to create a trigger for views or temporary table.
• It is not allowed to use transaction in a trigger.
• Return statement is disallowed in a trigger.
• Creating a trigger for a database table causes the query cache invalidated.

*Query cache allows you to store the result of query and corresponding select statement.

In the next time, when the same select statement comes to the database server, the database server will use the result which stored in the memory instead of parsing and executing the query again.

• All trigger for a database table must have unique name.

*It is allowed that triggers for different tables having the same name but it is recommended that trigger should have unique name in a specific database. 

MYSQL Stored Procedures

A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure.

Stored Procedures Advantages

• Stored procedure increases performance of application.
• Stored procedure reduces the traffic between application and database .
• Stored procedure is reusable and transparent to any application which wants to use it.
• Stored procedure is secured.

Stored Procedures Disadvantages

• Stored procedures make the database server high load in both memory and processors.
• Stored procedure only contains SQL declarative statements.
• Stored procedure is difficult to debug.
• Store procedure is not easy to write and maintain.

If you run the command above you will get all employees in the employees database table.

• Parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.
• IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside stored procedure does not change parameter.
• OUT this mode indicates that stored procedure can change this parameter and pass back to the calling program.
• INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program.