MySQL Trigger

MySQL Trigger


Trigger is a stored procedure that defines an action that the database should take when some database related statement(DML Statement) such as insert/update/delete occurs. Using triggers we can create custom constraints (Checks) to control the integrity of the data.
 

Trigger Syntax

delimiter $$
create trigger <trigger_name>
before/after insert/update/delete
on table_name
[for each row]
[when condition]
Begin
MySQL Statements;

end; $$
delimiter ;


* Delimiter is the marker (; too) for the end of each command you send to the MySQL. But when defining triggers and stored procedures we need to write delimiter to contain semicolons (;)

Complete Example of MySQL Trigger
 

STEP 1: START MYSQL COMMAND LINE CLIENT




STEP 2: CREATE DATABASE


create database samp;



STEP 3: OPEN DATABASE

use samp;


STEP 4: CREATE TABLE

create table emp5
(id integer primary key,
ename varchar(20),
sal integer);


STEP 5: INSERT VALUES INTO TABLE
Add single record value

insert into emp5 values(10,'a',50000);
                      Or
Add multiple record values
insert into emp5 values(10,'a',50000),(11,'b',60000),(12,'c',70000);




STEP 6: CREATE TRIGGER

Write a trigger to ensure that no salary of less than 50000 can be inserted in the database.

e.g.



delimiter $$
create trigger check_sal
before insert on emp5
for each row
begin
if new.sal < 50000 then
signal sqlstate '45000'
set message_text = 'Error: Salary must be atleast 50000!';
end if;
end; $$

delimiter ;



Then we can check above trigger working by using the following statement

insert into emp5 values(13,'d',30000);




Dropping a trigger­: We can drop or delete a trigger by using the following syntax
Syntax
 
Drop trigger <triggger_name> ;
 

e.g.
 
drop trigger check_sal ;