SQL

Triggers in MySQL example

delimiter //
CREATE TRIGGER person_ai AFTER INSERT
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;

trigger in mysql syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

mysql trigger

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter //
CREATE TRIGGER name_of_trigger [BEFORE|AFTER] [INSERT|UPDATE|DELETE] 
ON test1
FOR EACH ROW
BEGIN
	/* 
    Examples of code to write 
    */
	[INSERT|DELETE|UPDATE|IF|ELSEIF|END IF];
    /* 
    NEW.a1 meanse the new value that will be added with INSERT into test1
    */
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
    IF NEW.a1 < 0 THEN
    	SET NEW.a1 = 0;
    ELSEIF NEW.a1 > 100 THEN
    	SET NEW.a1 = 100;
    END IF;
END//
delimiter;

Triggers in MySQL example

delimiter //
CREATE TRIGGER person_bu BEFORE UPDATE
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

trigger in mysql

CREATE TRIGGER Product_Details_tr 
BEFORE INSERT ON Product_Details 
FOR EACH ROW 
SET NEW.User_ID = CURRENT_USER();

Triggers in MySQL example

delimiter //
CREATE TRIGGER person_bi BEFORE INSERT
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

mysql Trigger

select trigger_schema, trigger_name, action_statement
from information_schema.triggers

Mysql create trigger

CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal
  AFTER INSERT ON animals FOR EACH ROW
    UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
ERROR 1359 (HY000): Trigger already exists

CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER increment_animal
  AFTER INSERT ON animals  FOR EACH ROW
    UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
Query OK, 0 rows affected (0.12 sec)

CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS increment_animal
  AFTER INSERT ON animals FOR EACH ROW
    UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message                |
+-------+------+------------------------+
| Note  | 1359 | Trigger already exists |
+-------+------+------------------------+
1 row in set (0.00 sec)

triggers in mysql

A trigger is a set of codes that executes in response to some events.