Nadimul De Cj Nadimul De Cj - 1 month ago 8
MySQL Question

About database triggers and stored procedure

I have lack of knowledge about database trigger and stored procedure.

I don't understand those terms. Can anyone help me to make a clear concept about database trigger and stored procedure?

How can I implement database trigger and stored procedure in PHP and MYSQL?

I think a simple example in PHP can make me to understand about those topics.

Thanks in advance ....

Answer

A trigger is executed when a given operation happens - an INSERT, an UPDATE or a DELETE. You cannot control when exactly it happens or how many times - it just happens. On the other hand, a stored procedure must be run by you - you have to execute it, it doesn't automatically run each time a row is inserted etc. More Detail

Trigger Code

CREATE
TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `database`.`table`
FOR EACH ROW BEGIN
    -- trigger body
    -- this code is applied to every 
    -- inserted/updated/deleted row
END;

Store Procedure Code

DELIMITER $$
DROP FUNCTION IF EXISTS `functionname` $$
CREATE FUNCTION `functionname`(`fid` int) RETURNS varchar(25) CHARSET utf8
BEGIN
 DECLARE fName varchar(25);
 SELECT name into fName FROM table
 WHERE id = fid;
 RETURN fName;
END $$
DELIMITER ;