What is Mysqli Trigger?

Introduction on Triggers

A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.


Summary: in this tutorial, we will  give you a brief overview of SQL trigger, its advantages and disadvantages.

A SQL trigger is a set of  SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event associated with a table occurs e.g.,  insert, update or delete.

A SQL trigger is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

It is important to understand SQL trigger’s advantages and disadvantages so that you can use it appropriately. In the following sections, we will discuss the advantages and disadvantages of using SQL triggers.

Advantages of using SQL triggers

  • SQL triggers provide an alternative way to check the integrity of data.
  • SQL triggers can catch errors in business logic in the database layer.
  • SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked  automatically before or after a change  is made to the data in the tables.
  • SQL triggers are very useful to audit the changes of data in tables.

Disadvantages of using SQL triggers

  • SQL triggers only can provide an extended validation and they cannot replace all the validations. Some simple validations have to be done in the application layer. For example, you can validate user’s inputs in the client side by using JavaScript or in the server side using server-side scripting languages such as JSP, PHP, ASP.NET, Perl.
  • SQL triggers are invoked and executed invisible from the client applications, therefore, it is difficult to figure out what happen in the database layer.
  • SQL triggers may increase the overhead of the database server.

Triggers or stored procedures? It is recommended that if you have no way to get the work done with stored procedure, think about SQL trigger.

MySQL Triggers Implementation

Summary: in this tutorial, you will learn about MySQL triggers implementation. In addition, we will show you how MySQL stores the triggers and the limitations of triggers in MySQL.

Introduction to MySQL triggers

In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table. A trigger can be defined to be invoked either before or after the data is changed by INSERT, UPDATE or DELETE statement. Before MySQL version 5.7.2, you can to define maximum six triggers for each table.

  • 1
    BEFORE INSERT

     – activated before data is inserted into the table.

  • 1
    AFTER INSERT

     – activated after data is inserted into the table.

  • 1
    BEFORE UPDATE

     – activated before data in the table is updated.

  • 1
    AFTER UPDATE

     – activated after data in the table is updated.

  • 1
    BEFORE DELETE

     – activated before data is removed from the table.

  • 1
    AFTER DELETE

     – activated after data is removed from the table.

However, from MySQL version 5.7.2+, you can define multiple triggers for the same trigger event and action time.

When you use a statement that does not use 

1
INSERT

1
DELETE

 or 

1
UPDATE

 statement to change data in a table, the triggers associated with the table are not invoked. For example, the TRUNCATE statement removes all data of a table but does not invoke the trigger associated with that table.

There are some statements that use the 

1
INSERT

 statement behind the scenes such as REPLACE statement or LOAD DATA statement. If you use these statements, the corresponding triggers associated with the table are invoked.

You must use a unique name for each trigger associated with a table. However, you can have the same trigger name defined for different tables though it is a good practice.

You should name the triggers using the following naming convention:

For example, 

1
before_order_update

 is a trigger invoked before a row in the 

1
order

 table is updated.

The following naming convention is as good as the one above.

For example, 

1
order_before_update

 is the same as 

1
before_update_update

 trigger above.

MySQL triggers storage

MySQL stores triggers in a data directory e.g., 

1
/data/classicmodels/

 with the files named 

1
tablename.TRG

 and 

1
triggername.TRN

 :

  • The 
    1
    tablename.TRG

     file maps the trigger to the corresponding table.

  • the 
    1
    triggername.TRN

     file contains the trigger definition.

You can back up the MySQL triggers by copying the trigger files to the backup folder. You can also backup the triggers using the mysqldump tool.

MySQL trigger limitations

MySQL triggers cover all features defined in the standard SQL. However, there are some limitations that you should know before using them in your applications.

MySQL triggers cannot:

  • Use 
    1
    SHOW

    1
    LOAD DATA

    1
    LOAD TABLE

    , BACKUP DATABASE, 

    1
    RESTORE

    1
    FLUSH

     and 

    1
    RETURN

    statements.

  • Use statements that commit or rollback implicitly or explicitly such as COMMIT , ROLLBACK , START TRANSACTION , LOCK/UNLOCK TABLES , ALTER , CREATE , DROP ,  RENAME.
  • Use prepared statements such as 
    1
    PREPARE

    and 

    1
    EXECUTE

    .

  • Use dynamic SQL statements.

From MySQL version 5.1.4, a trigger can call a stored procedure or stored function, which was a limitation is the previous versions.

Create Trigger in MySQL

Summary: in this tutorial, you will learn how to create a trigger in MySQL using the CREATE TRIGGERstatement.

You should follow the introduction to SQL triggers and trigger implementation in MySQL first before going forward with this tutorial.

MySQL trigger syntax

In order to create a new trigger, you use the 

1
CREATE TRIGGER

 statement. The following illustrates the syntax of the 

1
CREATE TRIGGER

 statement:

Let’s examine the syntax above in more detail.

  • You put the trigger name after the 
    1
    CREATE TRIGGER

     statement. The trigger name should follow the naming convention 

    1
    [trigger time]_[table name]_[trigger event]

    , for example 

    1
    before_employees_update

    .

  • Trigger activation time can be 
    1
    BEFORE

     or 

    1
    AFTER

    . You must specify the activation time when you define a trigger. You use the 

    1
    BEFORE

     keyword if you want to process action prior to the change is made on the table and 

    1
    AFTER

     if you need to process action after the change is made.

  • The trigger event can be 
    1
    INSERT

    1
    UPDATE or 
    1
    DELETE

    . This event causes the trigger to be invoked. A trigger only can be invoked by one event. To define a trigger that is invoked by multiple events, you have to define multiple triggers, one for each event.

  • A trigger must be associated with a specific table. Without a table trigger would not exist therefore you have to specify the table name after the 
    1
    ON

     keyword.

  • You place the SQL statements between 
    1
    BEGIN

     and 

    1
    END

     block. This is where you define the logic for the trigger.

MySQL trigger example

Let’s start creating a trigger in MySQL to log the changes of the 

1
employees

 table.

Employee Table

First, create a new table named 

1
employees_audit

 to keep the changes of the 

1
employee

 table. The following statement creates the 

1
employee_audit

 table.

Next, create a 

1
BEFORE UPDATE

 trigger that is invoked before a change is made to the 

1
employees

 table.

Inside the body of the trigger, we used the 

1
OLD

 keyword to access 

1
employeeNumber

 and 

1
lastname

column of the row affected by the trigger.

Notice that in a trigger defined for INSERT, you can use 

1
NEW

 keyword only. You cannot use the 

1
OLD

keyword. However, in the trigger defined for DELETE, there is no new row so you can use the 

1
OLD

keyword only. In the UPDATE trigger, 

1
OLD

 refers to the row before it is updated and 

1
NEW

 refers to the row after it is updated.

Then, to view all triggers in the current database, you use 

1
SHOW TRIGGERS

 statement as follows:

MySQL SHOW TRIGGERS example

In addition, if you look at the schema using MySQL Workbench under the employees > triggers, you will see the 

1
before_employee_update

 trigger as shown in the screenshot below:

MySQL create trigger example

After that, update the 

1
employees

 table to check whether the trigger is invoked.

Finally, to check if the trigger was invoked by the 

1
UPDATE

 statement, you can query the 

1
employees_audit

 table using the following query:

The following is the output of the query:

MySQL Trigger log

As you see, the trigger was really invoked and it inserted a new row into the 

1
employees_audit

 table.

In this tutorial, you have learned how to create a trigger in MySQL. We also showed you how to develop a trigger to audit the changes of the 

1
employees

 table.

Create Multiple Triggers For The Same Trigger Event And Action Time

 

Summary: in this tutorial, you will learn how to create multiple triggers for the same event and action time in MySQL.

This tutorial is relevant to the MySQL version 5.7.2+. If you have an older version of MySQL, the statements in the tutorial will not work.

Before MySQL version 5.7.2, you can only create one trigger for an event in a table e.g., you can only create one trigger for the 

1
BEFORE UPDATE

 or 

1
AFTER UPDATE

 event. MySQL 5.7.2+ lifts this limitation and allows you to create multiple triggers for the same event and action time in a table. The triggers will activate sequentially when the event occurs.

The syntax for creating the first trigger remains the same. In case you have multiple triggers for the same event in a table, MySQL will invoke the triggers in the order that they were created. To change the order of triggers, you need to specify 

1
FOLLOWS

 or 

1
PRECEDES

 after the 

1
FOR EACH ROW

 clause.

  • The 
    1
    FOLLOWS

       option allows the new trigger to activate after the existing trigger.

  • The 
    1
    PRECEDES

      option allows the new trigger to activate before the existing trigger.

The following is the syntax of creating a new additional trigger with explicit order:

MySQL Multiple Triggers Example

Let’s look at an example of creating multiple triggers on the same event and action time in a table to get a better understanding.

We will use the 

1
products

 table in the sample database for the demonstration. Suppose, whenever we change the price of a product (column 

1
MSRP

 ), we want to log the old price in a separate table named 

1
price_logs

 .

First, we create a new 

1
price_logs

 table using the CREATE TABLE statement as follows:

Second, we create a new trigger that activates when the 

1
BEFORE UPDATE

 event of the 

1
products

 table occurs. The trigger’s name is 

1
before_products_update

 :

Third, we change the price of a product and query the 

1
price_logs

 table using the following UPDATEstatement:

mysql multiple triggers example

It works as expected.

Suppose we want to see not only the old price and when it was changed but also who changed it. We can add additional columns to the 

1
price_logs

 table. However, for the purpose of multiple triggers demonstration, we will create a new table to store the data of users who made the changes. The name of the new table is 

1
user_change_logs

 with the following structure:

Now, we create a second trigger that activates on the 

1
BEFORE UPDATE

 event of the 

1
products

 table. This trigger will update the 

1
user_change_logs

 table with the data of the user who made the changes. It is activated after the 

1
before_products_update

 trigger.

Let’s do a quick test.

First, we update the prices of the product using the UPDATE statement as follows:

Second, we query the data from both 

1
price_logs

 and 

1
user_change_logs

 tables:

mysql multiple triggers example 2

mysql multiple triggers: second trigger activates

As you can see, both triggers were activated in the order as expected.

Information On Triggers Order

If you use the 

1
SHOW TRIGGERS

 statement, you will not see the order that triggers activate for the same event and action timing in a table.

To find this information, you need to query the 

1
action_order

 column in the 

1
triggers

 table of the 

1
information_schema

 database as follows:

triggers order

Managing Triggers in MySQL

Summary: in this tutorial, you will learn how to manage triggers including displaying, modifying, and removing triggers in MySQL databases.

After creating a trigger, you can display its definition in the data folder, which contains trigger definition file. A trigger is stored as a plain text file in the following database folder:

MySQL provides you with an alternative way to display the trigger by querying the 

1
triggers

 table in the 

1
information_schema

 database as follows:

The statement allows you to view both content of the trigger and its metadata such as associated table name and definer, which is the name of MySQL user who created the trigger.

If you want to retrieve all triggers in a particular database, you need to query data from the triggers table in the 

1
information_schema

 database using the following SELECT statement:

To find all triggers associated with a particular table, you use the following query:

For example, the following statement returns all triggers associated with the 

1
employees

 table in the 

1
classicmodels

 database.

MySQL information_schema triggers table

MySQL SHOW TRIGGERS statement

Another quick way to display triggers in a particular database is to use 

1
SHOW TRIGGERS

 statement as follows:

For example, if you want to view all triggers in the current database, you can use the 

1
SHOW TRIGGERS

statement as follows:

To get all triggers in a specific database, you specify the database name in the 

1
SHOW TRIGGERS

 statement as the statement below:

It returns all triggers in the 

1
classicmodels 

database.

To get all the trigger associated with a specific table, you use the 

1
WHERE

 clause in the 

1
SHOW TRIGGERS

statement. The following statement returns all triggers associated with the 

1
employees

 table:

MySQL SHOW TRIGGERS statement example




Notice that we use backquote (`) to wrap the 

1
table

 column because 

1
table

 is a reserved keyword in MySQL.

MySQL returns the following columns when you execute the 

1
SHOW TRIGGERS

 statement.

  • Trigger: stores the name of the trigger e.g., before_employee_update trigger.
  • Event:  specifies the event e.g., INSERT, UPDATE, or DELETE that invokes the trigger.
  • Table: specifies the table where the trigger is associated with e.g. 
    1
    employees

     table.

  • Statement: stores the statement or compound statement that is going to execute when the trigger is invoked.
  • Timing: accepts two values: BEFORE and AFTER. It specifies the activation time of the trigger.
  • Created: logs the created time when you created the trigger.
  • sql_mode: specifies the SQL mode when the trigger executes.
  • Definer: logs the account who created the trigger.

Notice that to execute the 

1
SHOW TRIGGERS

 statement, you must have the 

1
SUPER

 privilege.

Removing a trigger

To remove an existing trigger, you use 

1
DROP TRIGGER

 statement as follows:

For example, if you want to remove the before_employees_update trigger associated with the 

1
employees

 table , you can execute the following statement:

To modify a trigger, you have to delete it first and recreate it with the new code. There is no such 

1
ALTER TRIGGER

 statement available in MySQL, therefore, you cannot modify an existing trigger like modifying other database objects such as tables, views, and stored procedures.

Working with MySQL Scheduled Event

Summary: in this tutorial, you will learn about MySQL event scheduler and how to create MySQL events to automate database tasks.

A MySQL event is a task that runs based on a predefined schedule therefore sometimes it is referred to as a scheduled event. MySQL event is also known as “temporal trigger” because it is triggered by time, not by table update like a trigger. A MySQL event is similar to a cron job in UNIX or a task scheduler in Windows.

You can use MySQL events in many cases such as optimizing database tables, cleaning up logs, archiving data, or generate complex reports during off-peak time.

MySQL event scheduler configuration

MySQL uses a special thread called event schedule thread to execute all scheduled events. You can see the status of event scheduler thread by executing the following command:

process list

By default, the event scheduler thread is not enabled. To enable and start the event scheduler thread, you need to execute the following command:

Now to see the status of event scheduler thread, you execute the  

1
SHOW PROCESSLIST

 command again.

process list with event scheduler thread

To disable and stop the event the event scheduler thread, you execute the SET GLOBAL command with value of the event_scheduler is OFF:

 Creating new MySQL events

Creating an event is similar to creating other database objects such as stored procedures or triggers. An event is a named object that contains SQL statements.

A stored procedure is only executed when it is invoked directly; a trigger is executed when an event associated with a table such as an insert, update, or delete  event occurs while an event can be executed at once or more regular intervals.

To create and schedule a new event, you use the  

1
CREATE EVENT

 statement as follows:

Let’s examine the statement in more detail.

  • First, you specify the event name after the  
    1
    CREATE EVENT

     clause. The event name must be unique within a database schema.

  • Second, you put a schedule after the  
    1
    ON SCHEDULE

     clause. If the event is a one-time event, you use the syntax:

    1
    AT timestamp [+ INTERVAL]

     If the event is a recurring event, you use the 

    1
    EVERY

    clause:

    1
    EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]
  • Third, you place the SQL statements after the 
    1
    DO

     keyword. It is important to notice that you can call a stored procedure inside the body of the event. In case you have compound SQL statements, you can wrap them in a  

    1
    BEGIN END

     block.

Let’s look at few examples of creating events to understand the syntax above.

To create and schedule a new one-time event that inserts a message into a table called messages you do the following steps:.

First, create a new table named 

1
messages

 by using the  

1
CREATE TABLE

 statement as follows:

Second, create an event by using the  

1
CREATE EVENT

 statement:

Third, check the 

1
messages

 table; you will see that we have 1 record. It means the event was executed when it is created.

mysql event log entry

To shows all events of a database schema, you use the following statement:

mysql event empty list

We don’t see any row returned because an event is automatically dropped when it is expired. In our case, it is a one-time event and expired when its execution completed.

To change this behavior, you can use the  

1
ON COMPLETION PRESERVE

 clause. The following statement creates another one-time event that is executed after its creation time 1 minute and not dropped after execution.

Wait for 1 minute, check the messages table, another record was added:

message table records

If we execute the  

1
SHOW EVENTS

 statement again, we see the event is there because the effect of the  

1
ON COMPLETION PRESERVE

 clause:

mysql event list

The following statement creates a recurring event that executes every minute and is expired in 1 hour from its creation time:

Notice that we used 

1
STARTS

 and 

1
ENDS

 clauses to define expiration period for the event. You can test this recurring event by waiting for few minutes and check the 

1
messages

 table.

messages table entries with recurring events

Drop MySQL events

To remove an existing event, you use the  

1
DROP EVENT

 statement as follows:

For example, to drop the  

1
test_event_03

 event, you use the following statement:

Modifying MySQL Events

Summary: this tutorial shows you how to modify existing MySQL events by using the  

1
ALTER EVENT

statement. After the tutorial, you will know how to modify the schedule of an event, how to enable or disable an event, and how to rename an event.

MySQL allows you to change various attributes of an existing event. To change existing events, you use the  

1
ALTER EVENT

 statement as follows:

Notice that the 

1
ALTER EVENT

 statement is only applied to an existing event. If you try to modify a nonexistent event, MySQL will issue an error message therefore, you should always use the  

1
SHOW EVENTS

 statement to check the event for its existence before changing it.

ALTER EVENT examples

Let’s create a sample event to demonstrate various features of the  

1
ALTER EVENT

 statement.

The following statement creates an event that inserts a new record every minute into the 

1
messages

table.

Changing schedules

To make the event run every 2 minutes, you use the following statement:

Changing event body

You can also change the event’s body by specifying the new logic as follows:

You can wait for 2 minutes and check the 

1
messages

 table again:

Disable events

To disable the event, you use the following statement:

You can check the status of the event by using the  

1
SHOW EVENTS

 statement as follows:

Enable events

To enable a disabled event, you use the 

1
ENABLE

 keyword after the  

1
ALTER EVENT

 statement as follows:

Rename events

MySQL does not provide you with the  

1
RENAME EVENT

 statement. Fortunately, you can use the  

1
ALTER EVENT

 to rename an existing event as follows:

Move events to another database

You can move an event from a database to another database by using the  

1
RENAME TO

 clause as follows:

It is assumed that the 

1
newdb

 database is available in your MySQL database server.

 

READ  How to Export Data to CSV File using PHP and MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *