Action Automation with MySQL Triggers

Core points

• MySQL triggers simplify PHP projects with automated operations such as database queries, file operations, and data processing. They are automatically called before or after actions (insert, update, delete) on the table.
• Triggers were introduced in MySQL version 5.0.2 and require corresponding permissions to be created. They must have a unique name in the database where they are created and will only be fired when the original SQL statement is executed.
• Triggers help maintain the integrity of a set of tables, automatically increase or decrease statistical tables when new inserts/deletes, record changes to data within the database, and keep tables synchronized with other tables.
• MySQL triggers can positively impact website performance and allow developers to avoid writing a lot of PHP code to handle operations. They can be used to automatically perform tasks that respond to specific data changes.

Many of the code we write is to perform an operation. Whether it is database queries, file operations, data processing, etc., all of this is to enable our scripts to achieve their intended purpose. But have you noticed how much code you sometimes need to write to verify a previous operation? One of my recent projects involved a rather cumbersome issue, which led me to use countless queries just to make sure that all the data remains synchronized in my table after each operation. This is far from elegant, and it should have been a fairly simple script, but it has become a complex query page. From a maintenance point of view, this is not feasible, and it's a nightmare when I want to update a part of the page's feature. It is here that the MySQL trigger goes into my project. By letting MySQL do more work with triggers, the PHP side of my project is greatly simplified. Therefore, the purpose of this article is to give you a deeper understanding of the creation and use of MySQL triggers so that at the end of your reading, you can use them in your own project.

What is a MySQL trigger?

Triggers were introduced in MySQL version 5.0.2 and are just one of the added features of MySQL that can help us simplify our work as developers. They are automatically called before or after actions (insert, update, delete) on the table. You need to have the appropriate permissions to create a trigger. Before MySQL 5.1.6 you needed SUPER permissions, but in 5.1.6 this changed and you needed TRIGGER permissions. Typically, a shared hosting plan does not allow SUPER because it is easily abused, so you may only be able to use them on servers that you have more permissions, such as a (virtual) dedicated server or your localhost, depending on how you use MySQL version. Here are some other quick instructions on triggers:

• They must have a unique (case-insensitive) name in the database where they were created.
• Only one trigger with the same event (update/insert/delete) and time (before/after).
• When a table is deleted, the trigger associated with it is also deleted.
• You cannot explicitly change triggers (unlike events) using the ALTER statement. You need to delete the trigger and recreate it.
• Triggers will only be triggered when the original SQL statement is executed; for example, foreign key relationship deletion will not activate the trigger.

Let's now look at the basic syntax of the trigger by breaking it down into its original form:

CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END

When you create a trigger, you can choose whether it triggers before or after the action occurs; which one you choose will completely depend on how you use them. If you want to modify incoming data entering the database, you need BEFORE. However, if you are doing something because of a previous action, you should use the AFTER statement. The operation that will trigger the trigger can be INSERT, UPDATE, or DELETE, because these three statements are the only statements that will cause the data in the table to be modified.

Apply the trigger to the actual situation

Triggers are very useful in many cases. A well-known usage is to maintain the integrity of a set of tables by triggering the deletion of obsolete records when foreign keys are not used. They can also be used to automatically increase or decrease statistical tables when new inserts/deletes, record changes to data in the database, keep tables synchronized with other tables, and more. In this article, we will use them to preprocess some calculations. This is the case that we have a company that rents its halls for £30 per hour. They record the name, start and end time of each event held in the hall, and then calculate the time and expenses payable in the income table. The name and start time of the event are initially inserted into the event table to book the lobby, and then the rental cost is updated on that row only after the event is over. The duration of the event (in minutes) needs to be calculated, where the start time will be subtracted from the end time, and the rental fee will then be calculated by multiplying the total time by 0.5 (£30 per hour, 50 pence per minute). We can use PHP to perform calculations of event duration and expenses when updating event information (by selecting the inserted data, calculating duration and rental expenses, and then inserting or updating revenue lists), or we can simply use triggers to automate this Process and reduce some PHP code. Let's set up two basic tables and insert some virtual subscription data into the event to start the operation.

CREATE TABLE events (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_name VARCHAR(50) NOT NULL,
    event_start TIMESTAMP NOT NULL DEFAULT 0,
    event_end TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE revenue (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_id INTEGER NOT NULL,
    hire_time INTEGER NOT NULL,
    hire_fees FLOAT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE (event_id)
)ENGINE=INNODB;

INSERT INTO events VALUES
    (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0),
    (NULL, 'Wedding', '2012-12-02 13:00:00', 0);

After setting up the two tables, we can continue to create a trigger named CostCalc. The trigger is set to fire after an update occurs on the event table and then perform the aforementioned calculation. It then inserts or updates the revenue list (if the pre-existing event ID is set).

CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END

When creating a trigger (similar to events and stored routines), the first thing we need to do is specify a new delimiter that represents the end of the trigger. This is done using the DELIMITER keyword, followed by a custom symbol (or symbol) and requires the trigger to be executed as a whole, rather than MySQL just executes internal statements alone. We then specify the name of the trigger, its time, event, and the table on which it will be set to be triggered. In this example, we set the trigger's time to work after the UPDATE statement occurs, because we want to execute the trigger only after a successful update; otherwise, we will repeat the previous record of the event. Next, we use the BEGIN...END compound statement to accommodate the trigger's functionality. The body of the trigger first declares two variables: rows and time. We select the number of rows from the event table, where the ID refers to the row that has just been modified, and one (or two) of the event_start and event_end times have been modified, and the event_end time is not equal to zero. This is to clarify whether any action is required on the income statement, as the rental fee can only be calculated through these changes. Once we know we can calculate the time and expense, we set the time variable to equal the number of minutes from the beginning to the end of the column. By multiplying this number by 0.5, we can also get the rental cost. Since the event_id column is unique, we can only have one ID corresponding to the event table; therefore, we use REPLACE to update pre-existing rows in the table (if any) or insert new rows (if not). In the MySQL statement, you may also notice that the keywords OLD and NEW are used in the SELECT and REPLACE statements above and in the expressions of the time variable values. Your use of these two keywords will depend on the event in your situation and when the trigger is triggered.

• NEW keyword is used to access incoming data entering the database. This is only available in INSERT and UPDATE statements.
• OLD keyword is used to access the current data in the record before any modifications are made to the record. This is only available in UPDATE and DELETE statements.

The corresponding PHP script that will be used to start the trigger will include a class (called EventHandler), as well as our client calling code. The class will connect to our MySQL database via PDO and will contain a method updateEvent() that will be called when the event content needs to be updated.

CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END

We first create our EventHandler class, where the property $db is defined to hold an instance of the PDO class, which is set by a constructor method. Then, we proceed to make our updateEvent() method, where three parameters are defined. The first parameter specifies the column we want to update in the event table and allows one of three values: name, start, end. The second parameter holds the value to be inserted or the current value of the updated column; while the third parameter holds the ID of the tuple to be updated. After ensuring that the column name is valid, we query our table through parameterized query, and finally check whether any rows have been updated. After creating the class, we continue to call it. We pass an instance of the PDO object as a parameter to the EventHandler class. The updateEvent() method will then be called four times with different values ​​to show how our trigger will react to updates made on the event table. The first two updates will not cause our trigger to insert or update rows, as we still do not have the information needed to calculate the event duration and rental fees. All we did was update the event name and delay its start time by two days. However, the next two updates will require the functionality of our triggers, as the first update defines the end time, the second update redefines the end time as one hour later, resulting in a change in duration, so the rental Costs have also changed. This is where we need the REPLACE statement, because we impose constraints on the table when creating the table, and we can only have one record per event ID.

Conclusion

MySQL triggers, if used properly, can not only have a positive impact on the performance of the website, but also avoid writing a lot of PHP code to handle such operations. I hope you find them as useful in your projects as I do in my projects, so feel free to use triggers boldly! Pictures from Fotolia

FAQs on Automation of Operations with MySQL Triggers

What are MySQL triggers and how does it work?

MySQL trigger is a stored program that is automatically called in response to events occurring in a table (such as insertion, update, or delete). Triggers are used to maintain the integrity of information in the database and are automatically called when specific operations occur on the table. They can be executed before or after an event.

How to create MySQL trigger?

Creating a MySQL trigger involves a CREATE TRIGGER statement, which includes the name of the trigger, the trigger event, and the statement to be executed when the event occurs. Here is a basic example:

CREATE TABLE events (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_name VARCHAR(50) NOT NULL,
    event_start TIMESTAMP NOT NULL DEFAULT 0,
    event_end TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE revenue (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_id INTEGER NOT NULL,
    hire_time INTEGER NOT NULL,
    hire_fees FLOAT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE (event_id)
)ENGINE=INNODB;

INSERT INTO events VALUES
    (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0),
    (NULL, 'Wedding', '2012-12-02 13:00:00', 0);

Can I call a PHP script from a MySQL trigger?

cannot be called directly. MySQL does not support calling PHP scripts from triggers. However, you can do this indirectly by using UDF (user-defined functions) or using an external system to monitor changes in the database and then call a PHP script.

What is the syntax of MySQL trigger?

The syntax for creating a trigger in MySQL is as follows:

CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END

How to use MySQL to automate tasks?

Automation in MySQL can be achieved through triggers, stored procedures, and events. Triggers can be used to automate tasks that should be performed in response to specific data changes. Stored procedures allow you to encapsulate a series of commands into a single callable routine. Events are tasks that run according to schedule.

What are the limitations of MySQL triggers?

Some limitations of MySQL triggers include: they can only be associated with a single table, they cannot return a result set, they cannot accept parameters, and cannot be called directly like stored procedures.

How to debug MySQL triggers?

Debugging MySQL triggers can be challenging because there is no built-in debugger. However, you can use workarounds, such as inserting values ​​into separate tables to track execution flow, or using third-party tools like MySQL Debugger.

Can MySQL triggers call stored procedures?

Yes. However, you should be cautious when doing this, as it can lead to complex chains of events that are difficult to manage and debug.

How to delete MySQL trigger?

You can delete the MySQL trigger using the DROP TRIGGER statement followed by the name of the trigger. For example:

CREATE TABLE events (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_name VARCHAR(50) NOT NULL,
    event_start TIMESTAMP NOT NULL DEFAULT 0,
    event_end TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE revenue (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_id INTEGER NOT NULL,
    hire_time INTEGER NOT NULL,
    hire_fees FLOAT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE (event_id)
)ENGINE=INNODB;

INSERT INTO events VALUES
    (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0),
    (NULL, 'Wedding', '2012-12-02 13:00:00', 0);

Can MySQL triggers be used for data verification?

Yes. You can create a trigger to check if the data is inserted or updated into the table and take action accordingly.

The above is the detailed content of Action Automation with MySQL Triggers. For more information, please follow other related articles on the PHP Chinese website!