To create a trigger in MySQL, a keyword TRIGGER is used.

Basic syntax to create trigger:

Syntax

CREATE

   [DEFINER = {user | CURRENT_USER}]

>   TRIGGER 'triggerName' BEFORE/AFTER INSERT/UPDATE/DELETE

   ON 'database'.'table'

>   FOR EACH ROW

>   BEGIN

>   ----trigger body  for every event----

>   END;

Where,

DEFINER clause specifies the account to be used when the trigger has to be accessed. It can be username@hostname, CURRENT_USER.

The triggerName is the unique name of the trigger.

Trigger body can have OLD and NEW keywords. OLD keyword is used when an already existing record is updated. NEW is used when we are creating a new row.

Example

The example below sets trigger on inserting a new row. It gives the verdict of the student's exam based on the marks:

CREATE TABLE tblmarks (NAME VARCHAR(50), MARKS INT, VERDICT VARCHAR(50));

DELIMITER //

CREATE TRIGGER setVerdict BEFORE INSERT ON tblmarks

   FOR EACH ROW

   BEGIN

   IF NEW.MARKS > 85 THEN

      SET NEW.VERDICT='DISTINCTION';

   ELSE IF (NEW.MARKS < 85) AND (NEW.MARKS > 60) THEN

      SET NEW.VERDICT = 'FIRST CLASS';

   ELSE IF (NEW.MARKS < 60) AND (NEW.MARKS > 35) THEN

      SET NEW.VERDICT = 'SECOND CLASS';

   ELSE

      SET NEW>VERDICT = 'FAIL';

   END IF;

   END //

DELIMITER ;

 

INSERT INTO tblmarks VALUES ("Kaushik",56,'Pass'),("Jackson",90,'Pass'),("David",32,'Fail');

The example above will produce something like this:

SELECT * FROM tblmarks;

Output:

Inserting trigger while adding a row
Kaushik 56 SECOND CLASS
Jackson 90 DISTINCTION
David 32 FAIL