When is a trigger activated ?

A trigger is activated when changes are made on that table where it is associated with, more precisely on Insert/Update/Deleted execution statements

A simple example:

-- Firstly we create a test table

02.CREATE TABLE test (id INT, sum DECIMAL(10,2));

-- now we create the trigger for that table:

04.delimiter//

05.create trigger insert_trg before insert on test

06.for each row

07.begin

08.       if new.sum<0 then

09.           set new.sum=0;

10.       endif;

11.end//

12.delimiter;

What is this trigger doing ?

It is a simple field validation.

When we will try to insert into the table (ex insert into test values (1,-3)) this field sum will always be greater than 0 because we change negative values to 0.

How it works:

Create trigger is creating the trigger with the name insert_trg

The keyword BEFORE indicates the trigger action time. In this case, the trigger should activate before each row inserted into the table. You can use the AFTER keyword to activate it after each inserted row in the table.

The Insert keyword stands for the action what will activate the trigger. You can also use Update or Delete.

For each row means this trigger will be activated for each inserted action.

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used as there is no old row. In a DELETE trigger, only OLD.col_name can be used as there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before they were updated and NEW.col_name to refer to the columns of the row after they were updated.

By using the BEGIN … END construct you can define a trigger that executes multiple statements.

What is the command to list all triggers in a MySQL database?

The command is:

show triggers 

or you can access the INFORMATION_SCHEMA table directly by:

select trigger_schema, trigger_name, action_statement 

from information_schema.triggers