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 3: OPEN DATABASE
use samp;
STEP 4: CREATE TABLE
create table emp5
(id integer primary key,
ename varchar(20),
sal integer);
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 ;
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 ;