Scribd
Upload
11 views11 pages

Section 9

The document explains procedures and triggers in programming, particularly in MySQL. Procedures are reusable blocks of code that can be called multiple times to reduce repetition, while triggers automatically perform actions in response to data changes in a table. Syntax for creating, calling, and dropping procedures and triggers is provided along with examples.

Uploaded by

Nguyễn Xuân Mai
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
11 views11 pages

Section 9

The document explains procedures and triggers in programming, particularly in MySQL. Procedures are reusable blocks of code that can be called multiple times to reduce repetition, while triggers automatically perform actions in response to data changes in a table. Syntax for creating, calling, and dropping procedures and triggers is provided along with examples.

Uploaded by

Nguyễn Xuân Mai
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 11

Procedure and Trigger

Procedure

• A procedure in programming refers to a collection of


statements or tasks packaged as a self-contained unit.
Procedures are typically created to perform a specific task
and can be called and used in multiple places within a
program.
Procedure

• The main purpose of using procedures is to create a block


of code that can be reused multiple times. Instead of
duplicating the same code in different locations, you can
define a procedure and invoke it when needed. This helps
reduce code repetition, enhances modularity, and facilitates
maintenance.
Procedure

• Syntax to call a procedure

CALL procedure_name(argument1, argument2, ...);

• Example:
CALL get_employees_by_age(30);
Procedure

• Syntax to create a procedure in MySQL:


DELIMITER //
CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype,
...)
BEGIN
SQL statement;
END //
DELIMIER ;
• Example:
DELIMITER //
CREATE PROCEDURE get_employees_by_age(max_age INT)
BEGIN
SELECT * FROM employees WHERE age < max_age;
END //
DELIMITER ;
Procedure

• Syntax to drop procedure

DROP PROCEDURE procedure_name;

• Example:
DROP PROCEDURE get_employees_by_age;
Trigger

• Triggers in MySQL are used to automatically perform


actions when there is a data change in a specific table.
Triggers can be activated by events such as INSERT,
UPDATE, or DELETE.
Trigger

• Types of trigger
– BEFORE trigger: This trigger is activated before an INSERT,
UPDATE, or DELETE event occurs on a table. A BEFORE
trigger allows you to modify data or perform checks before the
actual change takes place.
– AFTER trigger: This trigger is activated after an INSERT,
UPDATE, or DELETE event has occurred on a table. An AFTER
trigger allows you to perform actions after the data change has
been made.
Trigger

• Syntax of Before or After Trigger:

DELIMITER $$
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
Statement;
END //
DELIMITER ;
Trigger

• Example of Before or After Trigger:


DELIMITER $$
CREATE TRIGGER trigger_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
UPDATE employees SET age = 18 WHERE id = NEW.id;
END //
DELIMITER ;
Trigger

• Syntax to drop trigger:


DROP TRIGGER trigger_name;

• Example:

DROP TRIGGER trigger_after_insert;

You might also like