使用MySQL触发器的动作自动化

Much of the code we write is to perform an action. Whether it’s for database querying, file manipulation, data handling, etc., it’s all required to make our scripts function to their set purpose. But have you ever noticed the amount of code you are having to write out sometimes to verify some previous action?

我们编写的许多代码都是执行一个动作。 无论是用于数据库查询，文件操作，数据处理等，都需要使我们的脚本发挥其设定的作用。 但是，您是否曾经注意到有时为了验证某些先前操作而不得不写出的代码量？

One of my recent projects involved a rather cumbersome problem that led me to using countless queries just to make sure all of the data was up to sync throughout my tables after each action. It was far from elegant, and what should have been a rather simple script had turned into a convoluted page of queries. This was not viable from a maintenance standpoint and was a complete nightmare when I wanted to update part of the page’s functionality. It’s here where MySQL triggers came into my project.

我最近的一个项目涉及一个相当麻烦的问题，导致我使用无数查询，只是为了确保每次操作后所有数据都可以在整个表中同步。 它远非优雅，本来应该是一个相当简单的脚本却变成了复杂的查询页面。 从维护的角度来看，这是不可行的，并且当我想更新页面功能的一部分时，这完全是一场噩梦。 这是MySQL触发器进入我的项目的地方。

By making MySQL do more work through triggers, the PHP side of my project was greatly simplified. So, it is the intention of this article to give you some insight into the creation and usage of MySQL triggers, so that by the end of this reading you can make use of them in your own projects.

通过使MySQL通过触发器完成更多工作，我项目PHP方面得到了极大简化。 因此，本文旨在让您对MySQL触发器的创建和使用有一些了解，以便在阅读此书后，您可以在自己的项目中使用它们。

这些是什么？ (What are They?)

Triggers were introduced in MySQL version 5.0.2 and are just one of MySQL’s added functionality to help make our lives easier as developers. They’re automatically invoked before or after an action (insert, update, delete) has been executed on a table.

触发器是在MySQL版本5.0.2中引入的，并且只是MySQL的新增功能之一，有助于使我们的开发人员生活更加轻松。 在表上执行操作(插入，更新，删除)之前或之后，它们会自动被调用。

You’ll need to have the appropriate privileges to create triggers. Prior to MySQL 5.1.6, you needed the SUPER privilege, but this changed in 5.1.6, to where you’ll need the TRIGGER privilege. Generally no shared hosting plan will allow you SUPER because of how easily it can be abused, so you may only be able to use these on a server where you have more authority, like a (virtual) dedicated server or your localhost, depending on the version of MySQL you’re using.

您需要具有适当的特权才能创建触发器。 在MySQL 5.1.6之前，您需要SUPER特权，但是在5.1.6中已更改为需要TRIGGER特权。 通常，由于容易被滥用，没有共享的托管计划会允许您拥有SUPER，因此您只能在具有更多权限的服务器(例如(虚拟)专用服务器或本地主机)上使用它们，具体取决于您正在使用MySQL版本。

Here are some other quick notes regarding triggers:

以下是有关触发器的其他一些快速说明：

• They must have unique (case-insensitive) names in the database they have been created in.
  在创建它们的数据库中，它们必须具有唯一的名称(不区分大小写)。

• Only one trigger with the same event (update/insert/delete) and timing (before/after) is allowed per table.

  每个表仅允许一个具有相同事件(更新/插入/删除) 和定时(之前/之后)的触发器。

• Upon table deletion, the triggers associated with it are also dropped.
  删除表后，与此表关联的触发器也将被删除。

• You are unable to explicitly alter a trigger with an ALTER statement (unlike with events). You’ll need to drop the trigger and recreate it.

  您无法使用ALTER语句显式更改触发器(与事件不同)。 您需要删除触发器并重新创建它。

• Triggers are only fired when a raw SQL statement has been executed; foreign key relationship deletions, for example, will not activate a trigger.
  仅在执行原始SQL语句时才触发触发器； 例如，外键关系删除将不会激活触发器。

Now let’s take a closer look at the basic syntax of a trigger by stripping it down to its raw form:

现在，通过将触发器分解为原始格式来进一步了解触发器的基本语法：

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

When creating a trigger, you can choose whether it will fire off before or after an action has occurred; which one you choose will be entirely dependent upon the situation you’re using them in. If you are looking to modify the incoming data going into your database, then BEFORE is needed. If however you’re looking to perform an action because of a previous one, then the AFTER statement should be used. The action that will fire off a trigger can either be INSERT, UPDATE, or DELETE because these are the only three statements that will cause modification to the data inside the table.

创建触发器时，您可以选择是在操作发生之前还是之后将其触发。 你选择哪一个将完全取决于你使用他们的情况。如果你正在寻找修改输入的数据进入数据库，再BEFORE是必要的。 但是，如果您由于前一个动作而希望执行某个动作，则应使用AFTER语句。 触发触发器的操作可以是INSERT ， UPDATE或DELETE因为这是将导致对表内数据进行修改的仅有的三个语句。

将触发器应用于情境 (Applying a Trigger to a Situation)

There are a number of situations where triggers can come in handy. One well-known usage is to maintain data integrity throughout a set of tables with the triggered deletion of stale records when a foreign key has not been used. They can also be used for auto-incrementing or decrementing a stats table upon a new insertion/deletion, logging changes made to data inside a database, keeping tables in sync with other tables, and so on.

在许多情况下，触发器可以派上用场。 一种众所周知的用法是在未使用外键时通过触发过期记录的删除来维护整个表集中的数据完整性。 它们还可以用于在新插入/删除操作时自动递增或递减stats表，记录对数据库内部数据所做的更改，使表与其他表保持同步等等。

For this article we’ll be using them to preprocess some calculations. The scenario is that we have a company which rents its hall at a price of £30 per hour. They log the name, start time, and end time of each event held there inside of the events table, and then calculate the time and fees due in the revenue table.

在本文中，我们将使用它们来预处理一些计算。 情况是，我们有一家公司以每小时30英镑的价格出租其大厅。 他们将保存在events表中的每个事件的名称，开始时间和结束时间记录在events表中，然后在revenue表中计算到期的时间和费用。

The event’s name and start time is inserted into the events table initially to pre-book the hall, and then it’s only after the event has finished that the rental cost is updated upon the row. The duration time of the event needs to be calculated (in minutes), where the start time will be subtracted from the end time, and then the rental fees will be calculated by multiplying the total time by 0.5 (£30 per hour is 50p per minute).

最初将事件的名称和开始时间插入到events表中以预先预订大厅，然后仅在事件完成后，在该行上更新租金。 需要计算活动的持续时间(以分钟为单位)，将从结束时间中减去开始时间，然后通过将总时间乘以0.5来计算租赁费用(每小时30英镑，即每次50便士)分钟)。

We can either perform the calculations for the event duration and fees with PHP (by selecting the inserted data, calculating the duration time and rental fees, and then inserting into or updating the revenues table) upon updating the event’s information, or we can simply use a trigger to automate this process and cut out some PHP.

更新事件信息后，我们可以使用PHP执行事件持续时间和费用的计算(通过选择插入的数据，计算持续时间和租金，然后插入或更新收益表)，或者我们可以简单地使用触发该过程自动化并切出一些PHP的触发器。

Let’s set up the two basic tables and insert some dummy booking data into events to start things off.

让我们设置两个基本表，并将一些虚拟预订数据插入events以开始工作。

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);

Once we have setup the two tables, we can move on to creating the trigger which we’ll call CostCalc. The trigger is set to fire after an update on the events table has occurred which will then perform the calculation stated earlier. It then inserts into, or updates (if a pre-existing event ID is already set) the revenue table.

一旦设置了两个表，就可以继续创建触发器，我们将其称为CostCalc 。 在对events表进行更新之后，将触发器设置为触发，然后将执行前面所述的计算。 然后，它插入或更新(如果已经设置了预先存在的事件ID) revenue表。

DELIMITER ^^
CREATE TRIGGER CostCalc AFTER UPDATE ON events
FOR EACH ROW
BEGIN
    DECLARE rows BOOL DEFAULT 0;
    DECLARE time INT DEFAULT 0;

    SELECT COUNT(id) INTO rows FROM events
        WHERE id = NEW.id
        AND (OLD.event_start != NEW.event_start
            OR OLD.event_end != NEW.event_end)
        AND NEW.event_end != 0;

    IF(rows = 1) THEN
        SET time = TIMESTAMPDIFF(MINUTE, NEW.event_start, NEW.event_end);
        REPLACE INTO revenue VALUES (NULL, NEW.id, time, time * 0.5);
    END IF;
END
^^

The first thing we need to do when creating a trigger (likewise with events and stored routines) is to specify a new delimiter which signifies the end of the trigger. This is done with the DELIMITER keyword, followed by a custom symbol (or symbols), and is required to execute the trigger as a whole rather than MySQL just executing the statements inside individually.

创建触发器时(与事件和存储的例程类似)，我们要做的第一件事是指定一个新的分隔符，该分隔符表示触发器的结束。 这是通过DELIMITER关键字后跟一个自定义符号(或多个符号)完成的，并且需要整体执行触发器，而不是仅在内部单独执行语句MySQL。

We then specify the trigger name, its timing, event, and upon what table it’ll be set up to fire on. In this example, we timed the trigger to act AFTER the UPDATE statement has occurred because we only want to execute the trigger after a successful update; otherwise we’d be duplicating the previous record of that event. Next we use the BEGIN...END compound statement to house the trigger’s functionality.

然后，我们指定触发器名称，其时间，事件以及将在哪个表上触发的触发器。 在这个例子中，我们定时触发行动AFTER的UPDATE发生语句，因为我们只想要一个成功更新后执行触发器; 否则，我们将复制该事件的先前记录。 接下来，我们使用BEGIN...END复合语句来容纳触发器的功能。

The trigger’s body starts by declaring two variables: rows and time. We select the number of rows from the events table where the ID references the row that has just been modified, and where either (or both) of the event_start and event_end times have been modified, as well as where the event end time does not equal zero. This is to clarify whether the updated row’s information actually needs anything done to the revenue table because it’s only with these changes that the rental fees are able to be calculated. Once we know that we have the go ahead to calculate the time and fees, we set the time variable to equal the number of minutes from the event’s start to finish columns. By multiplying this number by 0.5 we also get the cost of the rental. Because the event_id column is unique, we are only able to have one ID corresponding to the events table; and so we use REPLACE to update either a pre-existing row in the table with the new data, or insert the new row if it doesn’t exist already.

触发器的主体从声明两个变量开始： rows和time 。 我们从events表中选择行数，其中ID引用刚修改的行， event_start和event_end时间中的一个(或两者)都被修改，以及事件结束时间不相等的地方零。 这是为了阐明更新后的行的信息是否实际上需要对revenue表进行任何操作，因为只有这些更改才可以计算出租金。 一旦知道可以继续计算时间和费用，就将时间变量设置为等于从事件开始到结束列的分钟数。 通过将该数字乘以0.5，我们还可以获得租金。 因为event_id列是唯一的，所以我们只能有一个与事件表相对应的ID。 因此，我们使用REPLACE用新数据更新表中的现有行，或者如果尚不存在则插入新行。

Inside the MySQL statements, you may also have noticed the keywords OLD and NEW being used in the above SELECT and REPLACE statements, as well as in the expression for the time variable’s value. When you use the two will depend upon the event of your situation, as well as the timing of your trigger to fire at.

在MySQL语句中，您可能还注意到上述SELECT和REPLACE语句以及time变量值的表达式中使用了关键字OLD和NEW 。 当您使用两者时，将取决于您所处的情况以及触发时机。

• The NEW keyword is used to access incoming data into the database. This is only available on INSERT and UPDATE statements.

  NEW关键字用于访问进入数据库的数据。 仅在INSERT和UPDATE语句上可用。

• The OLD keyword is used to access the current data inside the record before any modifications have been made to it. This is only available on UPDATE and DELETE statements.

  OLD关键字用于在对记录进行任何修改之前访问记录中的当前数据。 仅在UPDATE和DELETE语句上可用。

The corresponding PHP script that will be used to set off our trigger will include a class (called EventHandler), and our client calling code. The class will connect to our MySQL database via PDO, and will contain one method, updateEvent(), which will be invoked when an event’s content needs to be updated.

用于触发触发器的相应PHP脚本将包括一个类(称为EventHandler )和我们的客户端调用代码。 该类将通过PDO连接到我们MySQL数据库，并将包含一个方法updateEvent() ，当需要更新事件的内容时将调用该方法。

<?php
class EventHandler
{
    protected $db;

    public function __construct(PDO $db) {
        $this->db = $db;
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function updateEvent($type, $param, $id) {
        if (!in_array($type, array('name', 'start', 'end'), TRUE)) {
            throw new InvalidArgumentException(
                'No such column type exists.'
            );
        }

        $query = $this->db->prepare(
            "UPDATE events SET event_{$type} = :param WHERE id = :ID"
        );
        $query->bindParam(':param', $param, PDO::PARAM_STR);
        $query->bindParam(':ID', $id, PDO::PARAM_INT);
        $query->execute();

        if($query->rowCount() !== 1) {
            throw new InvalidArgumentException(
                'No such event ID exists.'
            );
        }
    }
}

$dsn = 'mysql:dbname=events';
$dbuser = 'dbuser';
$passwd = 'dbpassword';
$settings = array(PDO::MYSQL_ATTR_FOUND_ROWS => TRUE);

$eventHandler = new EventHandler(new PDO($dsn, $dbuser, $passwd, $settings));

// will not cause any inserts or updates to the revenue table
$eventHandler->updateEvent('name', 'Auction', 1);
$eventHandler->updateEvent('start', '2012-11-10 14:30:00', 1);

// causes a new insertion into the revenue table
$eventHandler->updateEvent('end', '2012-12-02 20:30:00', 2);
// causes an update on the revenue table
$eventHandler->updateEvent('end', '2012-12-02 21:30:00', 2);

We begin by creating our EventHandler class, where the property $db is defined to hold an instance of the PDO class which is set through the constructor method. We then move on to making our updateEvent() method with three defined arguments. The first argument specifies the column we are wanting to update in our events table, and is allowed one of three values: name, start, end. The second argument holds the value to insert into, or update the current value of the column; whilst the third argument holds the ID of the tuple to update. After ensuring the column name is valid, we query our table through parametrized queries, and finally check if there are any rows updated.

我们从创建EventHandler类开始，其中定义了$db属性来保存通过构造函数方法设置的PDO类的实例。 然后，我们继续使用三个已定义的参数制作updateEvent()方法。 第一个参数指定我们要在events表中更新的列，并允许使用以下三个值之一：name，start，end。 第二个参数保存要插入或更新列的当前值的值； 而第三个参数保存要更新的元组的ID。 确保列名有效后，我们通过参数化查询查询表，最后检查是否有任何更新的行。

After creating the class, we go on to calling it. We pass the instantiation of the PDO object through to the EventHandler class as a parameter. The updateEvent() method is then called four times with varying values to show how our trigger will react to the updates made on our events table. The former two updates will not cause our trigger to insert or update a row because we still don’t have the required information to calculate the event’s duration time and cost of renting fees. All we’ve done is update the event name and postponed its start time by two days. The next two updates however will need our trigger’s functionality because the first update defines an end time, and the second update re-defines the end time to one hour later, causing a change in the duration time and therefore in the rent fees. This is where our REPLACE statement is required, because of the constraint we’ve placed onto the table upon creation, we can only have one record per event ID.

创建完类后，我们继续调用它。 我们将PDO对象的实例作为参数传递给EventHandler类。 然后，使用不同的值调用updateEvent()方法四次，以显示我们的触发器如何对events表上的更新做出React。 前两次更新不会导致触发器插入或更新行，因为我们仍然没有所需的信息来计算事件的持续时间和租金费用。 我们要做的就是更新事件名称，并将其开始时间推迟两天。 但是，接下来的两个更新将需要触发器的功能，因为第一个更新定义了结束时间，第二个更新将结束时间重新定义为一个小时后，从而导致持续时间和租金发生变化。 这是我们需要REPLACE语句的地方，由于在创建时已将约束置于表上，因此每个事件ID只能有一条记录。

结束语 (Closing Remarks)

When put to good use, MySQL triggers can have not only a positive impact on your site’s performance, but will also save you from writing out numerous lines of PHP to handle such actions. I hope you find them as useful in your projects as I have in mine, so feel free to get trigger happy!

如果使用得当，MySQL触发器不仅可以对您的网站的性能产生积极的影响，还可以使您免于编写大量PHP来处理此类操作。 希望您发现它们在您的项目中像在我的项目中一样有用，因此随时高兴地触发吧！

Image via Fotolia

图片来自Fotolia

翻译自: https://www.sitepoint.com/action-automation-with-mysql-triggers/