MySql Trigger

MySql Trigger

A trigger is a stored program which executed automatically to respond to a specific event. such as insert, update or delete occurred in a table.

Syntax :

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;

Notes:
event_name :  All triggers must have unique names within a schema
trigger_event : Indicates the kind of operation that activates the trigger.
tbl_name : The trigger becomes associated with the table named tbl_name

Example 1: Suppose you have user table and user_audit table and you want to track new users only. Then you have to create a trigger

DELIMITER $$

CREATE
TRIGGER `blog_after_insert` AFTER INSERT 
ON `user` 
FOR EACH ROW 
BEGIN
SET @changetype = 'NEW';
INSERT INTO user_audit (user_id, changetype) VALUES (NEW.id, @changetype);
    END
$$
DELIMITER;

Example 2: Create a trigger to set default_template is there is no default_template

DROP TRIGGER IF EXISTS `before_template_insert`;
DELIMITER //
CREATE TRIGGER `before_template_insert` BEFORE INSERT ON `templates`
FOR EACH ROW 

BEGIN

DECLARE default_Value tinyint(1);
SELECT COUNT(*) INTO default_Value FROM templates WHERE default_templ = 1;

IF(default_Value = 0) THEN
SET New.default_templ = 1;
ELSE
SET New.default_templ = 0;
END IF;
END 
//

DELIMITER ;

Example 3: Create TRIGGER to manage sort order

DROP TRIGGER IF EXISTS `before_flower_insert`;
DELIMITER //
CREATE TRIGGER `before_flower_insert` BEFORE INSERT ON `flowers`
FOR EACH ROW 
BEGIN
SET New.order_id =1 + IFNULL((SELECT MAX(order_id) FROM flowers), 0);
END 
//
DELIMITER ;

Example 4: TRIGGER to update same table after new row insert

DELIMITER $$ 
DROP TRIGGER IF EXISTS parkmedia_amount $$ 
CREATE TRIGGER parkmedia_amount 
BEFORE INSERT ON `parkmedia` FOR EACH ROW 
BEGIN 
IF NEW.media_type = 'vid' THEN 
SET new.amount = (SELECT CAST(default_values AS DECIMAL(10, 2)) as amount from payment_gateways where title ='vid_amount');
ELSE 
SET new.amount = (SELECT CAST(default_values AS DECIMAL(10, 2)) as amount from payment_gateways where title ='img_amount'); 
END IF; 
END; 
$$ 
DELIMITER ;