This statement creates a new trigger. 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.

Trigger names exist in the schema namespace, meaning that all
triggers must have unique names within a schema. Triggers in
different schemas can have the same name.

The DEFINER clause determines the security
context to be used when checking access privileges at trigger
activation time, as described later in this section.

trigger_time is the trigger action
time. It can be BEFORE or
AFTER to indicate that the trigger activates
before or after each row to be modified.

Basic column value checks occur prior to trigger activation, so
you cannot use BEFORE triggers to convert
values inappropriate for the column type to valid values.

trigger_event indicates the kind of
operation that activates the trigger. These
trigger_event values are permitted:

INSERT: The trigger activates
whenever a new row is inserted into the table; for example,
through INSERT,
LOAD DATA, and
REPLACE statements.

UPDATE: The trigger activates
whenever a row is modified; for example, through
UPDATE statements.

DELETE: The trigger activates
whenever a row is deleted from the table; for example, through
DELETE and
REPLACE statements.
DROP TABLE and
TRUNCATE TABLE statements on
the table do not activate this trigger,
because they do not use DELETE.
Dropping a partition does not activate
DELETE triggers, either.

The trigger_event does not represent a
literal type of SQL statement that activates the trigger so much
as it represents a type of table operation. For example, an
INSERT trigger activates not only
for INSERT statements but also
LOAD DATA statements because both
statements insert rows into a table.

A potentially confusing example of this is the INSERT
INTO ... ON DUPLICATE KEY UPDATE ... syntax: a
BEFORE INSERT trigger activates for every row,
followed by either an AFTER INSERT trigger or
both the BEFORE UPDATE and AFTER
UPDATE triggers, depending on whether there was a
duplicate key for the row.

Note

Cascaded foreign key actions do not activate triggers.

As of MySQL 5.7.2, it is possible to define multiple triggers for
a given table that have the same trigger event and action time.
For example, you can have two BEFORE UPDATE
triggers for a table. By default, triggers that have the same
trigger event and action time activate in the order they were
created. To affect trigger order, specify a
trigger_order clause that indicates
FOLLOWS or PRECEDES and the
name of an existing trigger that also has the same trigger event
and action time. With FOLLOWS, the new trigger
activates after the existing trigger. With
PRECEDES, the new trigger activates before the
existing trigger.

Before MySQL 5.7.2, there cannot be multiple triggers for a given
table that have the same trigger event and action time. For
example, you cannot have two BEFORE UPDATE
triggers for a table. But you can have a BEFORE
UPDATE and a BEFORE INSERT trigger,
or a BEFORE UPDATE and an AFTER
UPDATE trigger.

Within the trigger body, you can refer to columns in the subject
table (the table associated with the trigger) by using the aliases
OLD and NEW.
OLD.col_name refers
to a column of an existing row before it is updated or deleted.
NEW.col_name refers
to the column of a new row to be inserted or an existing row after
it is updated.

MySQL stores the sql_mode system
variable setting in effect when a trigger is created, and always
executes the trigger body with this setting in force,
regardless of the current server SQL mode when the
trigger begins executing.

The DEFINER clause specifies the MySQL account
to be used when checking access privileges at trigger activation
time. If a user value is given, it
should be a MySQL account specified as
'user_name'@'host_name',
CURRENT_USER, or
CURRENT_USER(). The default
DEFINER value is the user who executes the
CREATE TRIGGER statement. This is
the same as specifying DEFINER = CURRENT_USER
explicitly.

If you specify the DEFINER clause, these rules
determine the valid DEFINER user values:

If you do not have the SUPER
privilege, the only permitted user
value is your own account, either specified literally or by
using CURRENT_USER. You cannot
set the definer to some other account.

If you have the SUPER
privilege, you can specify any syntactically valid account
name. If the account does not exist, a warning is generated.

Although it is possible to create a trigger with a nonexistent
DEFINER account, it is not a good idea for
such triggers to be activated until the account actually does
exist. Otherwise, the behavior with respect to privilege
checking is undefined.

MySQL takes the DEFINER user into account when
checking trigger privileges as follows:

Be careful with BEFORE triggers. Constraints may occur, specifically if you are using InnoDB engine, where an insert will fail, but actions from your BEFORE trigger will succeed.

Use BEFORE triggers primarily for constraints or rules, not transactions, tweaking the NEW.* columns should be fine.

Stick with AFTER triggers for most other operations, such as inserting into a history table or updating a denormalization.

Posted by
Luciano Fantuzzi
on
April 16, 2007

It's not possible to perform a "STOP ACTION" into a TRIGGER. For example, if you're deleting a row and this action activates a trigger, is not possible to abort the proccess of DELETE of the row. A way to abort the current operation, is to cause a deliberated error.

...you can use Triggers and DES_ENCRYPT to move your password encryption to the database level and enforce it in a way that stops developers forgeting to use it (or bypassing it) with the following triggers...

CREATE TRIGGER user_update BEFORE UPDATE ON `user` FOR EACH ROW SET NEW.Password = DES_ENCRYPT(NEW.Password);

...you'll also notice the first one enforces auditing in a way that saves you from relying on developers getting that right as well.

You could give your dev's a nice stored proc to retrieve or comapre their submitted password but hopefully they can remember either DES_ENCRYPT/_DECRYPT or your phone number ;^).

Whilst bearing in mind that this doesn't magically make your entire system "secure" by some magic wave of a wand, given that you've implemented SSL it should be trivial to secure the link between web and database server (if there even is a gap) and then you can use HTTPS and only a little more careful thought to implement a system that is secure from submission page through to backup system in such a way that only someone physically stood at the server with the server's and Mysql's root password could decrypt the password/data.

Posted by
Nicolas LESCURE
on
August 13, 2007

Another way to "STOP ACTION" is to create a table (stop_action) with just a primary key(reason_to_stop). Then you pre-fill this table with some text ('do not do that', 'or that either')=> to stop action, just do an insert into this table (stop_action) with any of the pre-filled value ('do not do that').

Posted by
Anony Mous
on
June 20, 2008

TIP: to create a simple trigger that initializes multiple columns such as DATE type columns when a record is created:

Luciano Fantuzzi suggested to cause a deliberate error; this might cause problems though.It was my first approach too in a sanity-check like trigger. It calls a procedure which does the actual check and then assigns either 1 or 0 to @resultBool - 0 meaning it did not pass. In that case i wanted to prevent the INSERT by causing a deliberate error in the form of updating a non-existing table. This is what my attempt looked like:

While mysql allows the trigger even though the table doesn't exist, it will _always_ complain (throw an error) when it executes the trigger, even if @resultBool = 1. So, this will not work.

The suggestion of Nicolas LESCURE does work in combination with the IF-statement

Posted by
S Roberts
on
June 2, 2009

On Stop Action problems.

My solution was to create a unique/primary key on the table I want to insert into (in this case on S_ID), then if my sanity check fails I change the s_id to 0. This method will only ever create one duff record with an s_id of 0. Obviously you need to INSERT ignore!

To invoke a shell command from a trigger I used a roundabout approach, write to a predefined folder using trigger code 'select into outfile', and famd to monitor that folder, which triggers a php script.

The clear benefit is that the error message will be thrown reliably and contain arbitrary (though short) free text.

Posted by
Rui Da-Costa
on
August 25, 2011

Here's how I got it working using signals for raising the error:delimiter //use test//create table trigger_test(id int not null)//drop trigger if exists trg_trigger_test_ins //create trigger trg_trigger_test_ins before insert on trigger_test for each row begin declare msg varchar(255); if new.id < 0 then set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char)); signal sqlstate '45000' set message_text = msg; end if;end//

delimiter ;-- run the following as seperate statements:insert into trigger_test values (1), (-1), (2); -- everything fails as one row is badselect * from trigger_test;insert into trigger_test values (1); -- succeeds as expectedinsert into trigger_test values (-1); -- fails as expectedselect * from trigger_test;

Posted by
Jon Vance
on
November 1, 2012

I have discovered something that can be VERY important if you don't know about it. When using INSERT IGNORE, insert triggers are STILL FIRED when a duplicate key constraint prevents new rows from being inserted.