MySql trigger

Trigger is a database object, associated with a table. Trigger does it’s specified function if an event occures such as insert/update/delete records.

Benefits of Triggers in MySQL:

• triggers can change  values before insert/update . If you set the trigger to listen on insert/update and the criteria is not met than value will be modifiade this will be applied with the insert.
• triggers can check values on row level insert or update and it’s able to determine what values where deleted or updated to.

Create trigger:

-- create a table
drop table if exists triggtest;

create table triggtest(
 firstname varchar(30) not null default 'John',
 lastname varchar(30)not null default 'Doe',
 age tinyint signed
);
desc triggtest;
insert into triggtest values
 ('Adam', 'Smith', 42),
 ('Joe', 'White', -28); -- just to have a wrong entry
select * from triggtest;

-- create a basic trigger 'listening' for minus age values
create trigger triggtest_tr
before insert
 on triggtest
 for each row
 set NEW.age =
 if(new.age < 0, 35, truncate(new.age,-1));

-- now let's try the above inserts again
insert into triggtest values
 ('Adam', 'Smith', 42),
 ('Joe', 'White', -28); -- after trigger created minus values are replaced with 35, all other values rounded up/down
select * from triggtest;

The create trigger above does a simple value check before insert, the ‘for each row’ means in this case that it’s checked every time insert is issued. Finaly set new.age (it works as a variable) the criteria and action for true and false result.

Take a look on the create trigger syntax:

CREATE TRIGGER trigger_name
 {Before | After} {Insert | Update | Delete}
 ON tbl_name
 FOR EACH ROW
 trigger_body