MySQL Server : Open Source Database : MySQL Trigger

• MySQL Trigger
• Downloads Examples

• A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.

• The trigger becomes associated with the table named tbl_name, which must refer to a permanent table.
• You cannot associate a trigger with a TEMPORARY table or a view.

• Triggers are automatic execution of SQL commands or a stored procedure automatically after or before INSERT, UPDATE, or DELETE commands.
• For example, you can test every UPDATE operation to see whether the altered data conform to a particular set of rules.
• Other possible applications include changes to logging and updating variables or columns in other tables.

Mysql Create Trigger Syntax

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

• There are similar syntax rules for trigger code as for SPs. In particular, the code must be introduced with BEGIN and terminated with END if it contains more than one command.
• All SP language elements are available for triggers. However, there are many restrictions as to which SQL commands may be used in trigger code (more on this below).
• Within the trigger code you can access the columns of the current record:
• OLD.columnname returns the content of an existing record before it is changed or deleted (UPDATE, DELETE).
• NEW.columnname returns the content of a new or altered record (INSERT, UPDATE).
• You may change NEW.columnname in BEFORE INSERT triggers and BEFORE UPDATE triggers

Note :
To redefine the mysql delimiter, use the delimiter command.
The delimiter is changed to >> to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure.
This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

mysql> delimiter >>
You can restore back as below
mysql> delimiter ;

mysql> select * from admin;
+----------+----------+
| username | password |
+----------+----------+
| tom1     | cat      | 
| tom      | cat      | 
+----------+----------+
2 rows in set (0.00 sec)
mysql> delimiter >>
mysql> CREATE TRIGGER updateLowerCaseUSername_Trigger 
       BEFORE INSERT ON admin 
	   FOR EACH ROW 
	   BEGIN 
		SET NEW.username=lower(NEW.username); 
	   END;>>
Query OK, 0 rows affected (0.00 sec)

mysql> insert into admin values(‘RAJ','jAMes');>>
Query OK, 1 row affected (0.00 sec)

mysql> select * from admin;>>
+----------+----------+
| username | password |
+----------+----------+
| tom1     | cat      | 
| tom      | cat      | 
| raj      | jAMes    |  (Here raj is in lowercase)
+----------+----------+
3 rows in set (0.00 sec)

Example 2:

With the help of below trigger, we will get total column calculated automatically thru trigger.

mysql> create table marks
    -> (
    -> rollno int,
    -> m1 int,
    -> m2 int,
    -> tot int) CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into marks values(1,90,90,0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from marks;
+--------+------+------+------+
| rollno | m1   | m2   | tot  |
+--------+------+------+------+
|      1 |   90 |   90 |    0 | 
+--------+------+------+------+
1 row in set (0.00 sec)

Changing delimiter from ; to >> since we need to 
mysql> delimiter >>
mysql> create trigger updateTOT_Trigger 
       BEFORE INSERT ON marks 
	   FOR EACH ROW  
	   BEGIN 
	   SET NEW.tot=NEW.m1+NEW.m2; 
	   END; 
	   >>
Query OK, 0 rows affected (0.00 sec)

mysql> select * from marks;>>
+--------+------+------+------+
| rollno | m1   | m2   | tot  |
+--------+------+------+------+
|      1 |   90 |   90 |    0 | 
+--------+------+------+------+
1 row in set (0.00 sec)

mysql> insert into marks values(2,90,80,0);>>
Query OK, 1 row affected (0.00 sec)

mysql> select * from marks;>>
+--------+------+------+------+
| rollno | m1   | m2   | tot  |
+--------+------+------+------+
|      1 |   90 |   90 |    0 | 
|      2 |   90 |   80 |  170 | (Here total column calculated using trigger)
+--------+------+------+------+
2 rows in set (0.00 sec)

Alter Trigger

Alter trigger will help to alter trigger logic in database.

mysql>delimiter >>
mysql> Alter TRIGGER updateLowerCaseUSername_Trigger 
       BEFORE INSERT ON admin 
	   FOR EACH ROW 
	   BEGIN 
		SET NEW.password=lower(NEW.password); 
	   END;>>

Drop Trigger

Drop Trigger Syntax

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

Drop Trigger Example

Drop trigger will help to delete trigger from database.

mysql>DROP TRIGGER updateTOT_Trigger;