MySQL stored procedure&triggers

MySQL stored procedure:

It is a block of code stored on the server which executes a set of MySQL statements which increases performance of application.Once created, stored procedure is compiled and stored in the database catalog. It reduced the traffic between application and database server because instead of sending multiple uncompiled commands statement, application only has to send the stored procedure name and get the result back.It is reusable to any application

disadvantages of stored procedures

Stored procedure make the database server high load in both memory for and processors.
it only contains declarative SQL so it is very difficult to write a procedure with complexity of requirement


CREATE PROCEDURE myproc()
BEGIN
SELECT FROM tab;
END


MySQL Triggers

MySQL trigger is a piece of code that fires whenever an event occures to a table.The event can be a DML statement such as

delete - the trigger fires whenever 'delete' command executes on the table
insert - the trigger fires whenever 'insert' command executes on the table
update - the trigger fires whenever the table is updated

The trigger may be fired before the event occurs or after the event occurs


When creating a trigger you need to specify four pieces of information:

The unique trigger name
associated table
The event that the trigger should respond to (DELETE, INSERT, or UPDATE)
When the trigger should be executed (before or after processing)

CREATE TRIGGER trigger_name
ON table_name
FOR EACH ROW
BEGIN

END

create database tabdb
use tabdb
create table tab1(int val1);
create table tab2(int val2);
create table tab3 ( int val3 auto_increment PRIMARY KEY);


CREATE TRIGGER tabtrig BEFORE INSERT ON tab1
FOR EACH ROW BEGIN
INSERT INTO tab1 SET val1 = NEW.val1;
DELETE FROM tab3 WHERE val3 = NEW.val1;
END;

Advantages of using triggers

Using a trigger You can use them to check for, and prevent, bad data entering the database
you can catch the errors in business logic in the database level.
trigger provides an alternative way to run scheduled tasks. you can handle those tasks before or after changes being made to database tables.
A trigger generally performs the types of tasks described faster than application code, and and can be activated easily and quickly behind the scenes and does not need to be a part of your application code

While trigger is implemented there are some restrictions like following:

it's not allowed to call a stored procedure in a trigger.
It's not allowed to create a trigger for views or temporary table.
It's not allowed to use transaction in a trigger.
'Return' is not possible with a trigger.
triggers 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.

A stored procedure can only be run by some one or something and that's where the MySQL trigger is used.MySQL triggers are simple, effective, way of managing data in a database - the database user needs to be aware of the entering data and if stored procedures are used then the programming will take care of everything else.