MySQL - Stored Procedures and Triggers

MySQL - Stored Procedures and Triggers

When multiple applications need to perform common database operations, stored procedures can be used. It is a set of sql statements stored on the server. It has a name, parameters and some sql statements.

Example:
CREATE OR REPLACE PROCEDURE sample (x VARCHAR) IS
"BEGIN "
"INSERT INTO sample_table VALUES(x); "
"END;";
A trigger is a set of code which is executed in response to some event.

E.g Update employee_perfomance table when a new task is inserted in task table. Here, the trigger is “update” and the event is “inserting a new row”.

Example:
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS some_function (50009, 16, 10)

MySQL - Stored Procedures and Triggers

Stored Procedures:

A set of SQL statements is called a stored procedure, which can be compiled and stored in the server. The purpose of stored procedures is that, reissuing the entire queries is avoided. The query is parsed only once, thus provides better performance and information passed between server and the client is very less.

Triggers:

A trigger is fired when a particular event occurs. It is also a stored procedure. A stored procedure can be installed which triggers automatically every time a record is deleted or updated or inserted.
MySQL - Primary keys and auto increment fields in MySQL
Primary keys and auto increment fields in MySQL - Primary key is used to uniquely identify a row in a table. Usually the primary key....
MySQL - COMMIT and ROLLBACK in MySQL
COMMIT and ROLLBACK in MySQL - A transaction in MySQL is a set of SQL statements written to perform a specific task......
MySQL - ALTER command to add and drop INDEX in MySQL
ALTER command to add and drop INDEX in MySQL - An index in MySQL can be added using ALTER statement in multiple ways....
Post your comment