Write an Article?
Ask Question

MySQL Triggers with Examples

Previous Page
Next Page

Triggers are stored programs that are automatically run in response to an event. The event could be an insert, an update, or a delete statement.

There are two types of triggers in SQL.

  • Row-level triggers: The triggers are executed after insertion/deletion/update to a single row.
  • Statement-level triggers: These triggers are executed once every transaction.

MySQL supports only Row-level triggers.

Advantages of triggers:

  • Maintain the integrity of data
  • Better error-handling capacity
  • Enable edits-auditing
  • Faster data updates. We do not need to wait for scheduled tasks and scripts to run ti update data.

Triggers of course come with added processing overhead. Also, triggers allow only a limited number of validations.

How to create a trigger in MySQL?

The Basic syntax to create a trigger is

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

Let us look at examples related to some of the options.

We will consider a banking system that has an audit log table. We will insert transactions done in the audit_log table using triggers.

Before insert trigger in MySQL:

BEFORE INSERT triggers are automatically fired before an insert event occurs on the table. For example, Whenever a customer logs in to the system, even before he does any task, we wish to audit the log-in transactions. We can do so as follows:

CREATE TRIGGER login_trigger
   BEFORE INSERT ON customers
   FOR EACH ROW
INSERT INTO audit_log
SET audit_id = (select count(*) from customers),
    audit_date = NOW(),
    transaction_type = 'LOGIN',
    account_id = NEW.customer_id,
    description = 'New Login.';

Now before inserting a record in a customer, we will get a record in the audit_log without explicitly writing an insert.

After insert trigger in MySQL:

AFTER INSERT triggers are automatically invoked after an insert event occurs on the table. Let's say after inserting a record in the transactions table, we want to audit it, we can do:

CREATE TRIGGER audit_transaction
AFTER INSERT ON transactions
FOR EACH ROW
INSERT INTO audit_log
SET audit_id = (select max(transaction_id) from transactions),
    audit_date = NOW(),
    transaction_type = (select type_id from transactions having max(transaction_id)),
    account_id = NEW.sender_account_number,
    description = (select type_name from transaction_types where type_id in (select type_id from transactions having max(transaction_id)));

For example, after inserting the below record, the following will be seen in the audit_log table.

insert into transactions values (default,'1234REF','1112549087','660993452',100,300,NULL,NOW(),2);

Similarly, we can create CREATE BEFORE Update, CREATE AFTER Update, CREATE Before delete and Create after Delete triggers as well.

The format remains the same.

How to show triggers in MySQL?

To see what triggers are available, we can use the Show Triggers statement

Show Triggers;

How to drop triggers in MySQL?

To drop triggers, we use the drop trigger statement. The Basic syntax is

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

If we do not use the If Exists clause and try to drop a trigger that does not exist, MySQL throws an error. Otherwise, a warning is issued.

Example:

To drop the login_trigger and remove it, we do:

DROP trigger login_trigger;

If we try to drop a trigger that does not exist, MySQL throws an error. For example

DROP trigger some_trigger;

To avoid this error we can use the Drop IF Exists construct. The IF Exists checks if the trigger exists or not. If the trigger does not exist, then only a warning is issued, the execution is not halted due to an error.

DROP trigger IF EXISTS some_trigger;

 

Previous Page
Next Page