Scribd
Upload
8 views3 pages

Procedure and Triggers

The document outlines the creation and usage of stored procedures and triggers in MySQL. It details the syntax for defining procedures with input parameters and how to call them, as well as the types of triggers and their syntax for execution based on specific events. Additionally, it provides examples of creating tables and triggers, along with the use of the NEW keyword to reference new values in triggers.

Uploaded by

shashankan077
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views3 pages

Procedure and Triggers

The document outlines the creation and usage of stored procedures and triggers in MySQL. It details the syntax for defining procedures with input parameters and how to call them, as well as the types of triggers and their syntax for execution based on specific events. Additionally, it provides examples of creating tables and triggers, along with the use of the NEW keyword to reference new values in triggers.

Uploaded by

shashankan077
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

PROCEDURE

----------
Stored procedures are a set of SQL statements that can be stored in the database
and executed as a single unit.

Syntax:
-------

DELIMITER //

CREATE PROCEDURE procedure_name (parameter_list)


BEGIN
-- SQL statements
END //

DELIMITER ;

DELIMITER: Changes the statement delimiter to avoid confusion with semicolons used
inside the procedure.

procedure_name: Name of the procedure.

parameter_list: A list of input (IN) parameters.

BEGIN ... END: Encapsulates the body of the procedure.

TO CALL PROCEDURE
-----------------
CALL procedure_name(arguments);

IN PARAMETER
------------
Definition: Used to pass input values to the procedure.

SYNTAX:
-------
CREATE PROCEDURE ProcedureName(IN param_name datatype)

EX:
---

DELIMITER //

CREATE PROCEDURE GreetUser(IN user_name VARCHAR(50))


BEGIN
SELECT CONCAT('Hello, ', user_name) AS Greeting;
END //

DELIMITER ;

CALL GreetUser('RAMU'); -- Output: Hello, RAMU

TRIGGERS
--------
A trigger in MySQL is a set of SQL statements that are automatically executed (or
triggered) in response to certain events on a table.
Types of Triggers
------------------
MySQL supports the following types of triggers based on timing and event:

AFTER INSERT: Executes after a row is inserted.


AFTER UPDATE: Executes after a row is updated.
AFTER DELETE: Executes after a row is deleted.

SYNTAX:
-------
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- SQL statements
END;

trigger_name: Name of the trigger.

BEFORE or AFTER: Specifies when the trigger is executed.

INSERT, UPDATE, or DELETE: Specifies the event that causes the trigger to fire.

table_name: Table on which the trigger is defined.

FOR EACH ROW: Specifies that the trigger is executed for every row affected by the
triggering event.

TO VIEW TRIGGERS
----------------
SHOW TRIGGERS;

TO DROP TRIGGER
---------------
DROP TRIGGER IF EXISTS trigger_name;

EX:

TO APPLY TRIGGER WE NEED 2 TABLES:


----------------------------------
CREATE TABLE EMP
(
ID INT,
FNAME VARCHAR(10) ,
LNAME VARCHAR(20) ,
AGE INT
);

CREATE TABLE EMP_DATA


(
ID INT,
FNAME VARCHAR(10) ,
LNAME VARCHAR(20)
);

TO CREATE TRIGGER
-----------------
DELIMITER //
CREATE TRIGGER EMP_TRIGGER
AFTER INSERT ON EMP_DATA
FOR EACH ROW
BEGIN
INSERT INTO EMP(ID,FNAME,LNAME)VALUES(NEW.ID,NEW.FNAME,NEW.LNAME);
END //

When to Use NEW


---------------
NEW refers to the new values being inserted or updated in a row.

INSERT Triggers: Use NEW to access the values of the new row being inserted.
UPDATE Triggers: Use NEW to access the values of the row after the update.

You might also like