Skip to main content

Triggers

A trigger is a database object associated with a table. A trigger can be activated when a statement inserts, updates, or deletes rows in the associated table. You can also set a trigger to be activated before or after a trigger event.

For example, you can insert rows by using the INSERT or LOAD DATA statement. The INSERT trigger is activated for each row that is inserted. If you insert two rows in a batch, the INSERT trigger is activated twice. You can also activate a trigger before each row is inserted into the table or after each row is updated.

Types of triggers​

The current version of seekdb supports the following types of triggers:

  • INSERT triggers: These triggers are activated when a row is inserted. You can activate an INSERT trigger by using the INSERT, LOAD DATA, or REPLACE statement.

  • UPDATE triggers: These triggers are activated when a row is updated. You can activate an UPDATE trigger by using the UPDATE statement.

  • DELETE triggers: These triggers are activated when a row is deleted. You can activate a DELETE trigger by using the DELETE or REPLACE statement.

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is a special case. For each row, an BEFORE INSERT trigger is activated. Then, an AFTER INSERT trigger is activated if the row does not have a duplicate key. Otherwise, a BEFORE UPDATE trigger is activated, followed by an AFTER UPDATE trigger.

Create a trigger​

You can use the CREATE TRIGGER statement to create a trigger.

The user who creates a trigger must have the following privileges:

  • Privileges on the table associated with the trigger, including the SELECT, INSERT, UPDATE, and DELETE privileges.

  • The CREATE privilege.

  • Privileges on the statements to be executed after the trigger is activated.

The syntax for creating a trigger is as follows:

CREATE
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

The syntax is described as follows:

  • IF NOT EXISTS: If the trigger to be created already exists and the IF NOT EXISTS option is not specified, an error message is returned. If the IF NOT EXISTS option is specified, a warning message is returned instead of an error message.

  • The trigger_name must be unique.

  • table_name specifies the name of the table to which the trigger is associated.

  • BEFORE or AFTER specifies the time when the trigger is activated. For example, the trigger is activated before or after a row is inserted into the table.

  • INSERT, UPDATE, or DELETE specifies the trigger event, that is, the operation that activates the trigger.

  • FOR EACH ROW specifies the body of the trigger. The statement specified by FOR EACH ROW is executed each time the trigger is activated. The statement is executed for each row affected by the trigger event.

seekdb also defines NEW.columnName and OLD.columnName:

  • In an INSERT trigger, NEW.columnName specifies the new data to be inserted (BEFORE) or the data that has been inserted (AFTER). Here, columnName is the name of a column in the corresponding data table.

  • In an UPDATE trigger, OLD.columnName specifies the original data to be modified or that has been modified, and NEW.columnName specifies the new data to be inserted (BEFORE) or the data that has been inserted (AFTER).

  • In a DELETE trigger, OLD.columnName specifies the original data to be deleted or that has been deleted.

  • OLD.columnName is read-only. You can use SET to assign a value to NEW.columnName in a trigger.

Example 1: Create a trigger named test_trg and associate it with the test table. The trigger is activated when an INSERT operation is performed. The trigger acts as an accumulator that sums the values of the columns in the table.

CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
Query OK, 0 rows affected

CREATE TRIGGER test_trg BEFORE INSERT ON test
       FOR EACH ROW SET @sum = @sum + NEW.user_num;
Query OK, 0 rows affected

Example 2: Create triggers named trg2_t and trg3_t to be executed after the trigger named trg1_t. Create a trigger named trg4_t to be executed before the trigger named trg1_t.

CREATE TABLE t(c1 INT);
CREATE TABLE msg(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(100));
CREATE TRIGGER trg1_t BEFORE INSERT ON t FOR EACH ROW
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg1_t');
END;
/
CREATE TRIGGER trg2_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg2_t');
END;
/
CREATE TRIGGER trg3_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg3_t');
END;
/

CREATE TRIGGER trg4_t BEFORE INSERT ON t FOR EACH ROW PRECEDES trg1_t
BEGIN
  INSERT INTO msg(c2) VALUES ('BEFORE INSERT trg4_t');
END;
/
INSERT INTO t VALUES (1);

SELECT * FROM msg;

The return result is as follows:

+----+----------------------+
| c1 | c2                   |
+----+----------------------+
|  1 | BEFORE INSERT trg4_t |
|  2 | BEFORE INSERT trg1_t |
|  3 | BEFORE INSERT trg3_t |
|  4 | BEFORE INSERT trg2_t |
+----+----------------------+
4 rows in set

If a trigger contains multiple statements, you can use the BEGIN ... END statement to specify the start and end of the code block.

The syntax for the BEGIN ... END statement is as follows:

BEGIN
[statement_list]
END

Here, statement_list specifies a list of one or more statements. Each statement in the list must end with a semicolon (;). In SQL statements, a semicolon (;) is the delimiter of a statement. When a semicolon is encountered, the system assumes that the statement has ended and starts executing it. However, during execution, the interpreter may fail to find an END that matches the BEGIN and report an error. To avoid this error, you can use the DELIMITER command to change the delimiter.

Here is an example of the DELIMITER command:

DELIMITER new_delemiter

Here, new_delimiter can be a symbol of 1 or more bytes. The default delimiter is a semicolon (;). You can change the delimiter to another symbol, such as #.

After you add the DELIMITER command, a semicolon in the statements after the DELIMITER command does not cause an error until the delimiter set by the DELIMITER command (#) is encountered.

tip

After you change the delimiter by using the DELIMITER command, change the delimiter back to the default symbol, that is, the semicolon (;), after the statements are executed.

Here is an example:

CREATE TABLE test (user_id INT, user_num DECIMAL(10,2));
Query OK, 0 rows affected

DELIMITER //

CREATE TRIGGER test_trg BEFORE UPDATE ON test
               FOR EACH ROW
               BEGIN
               IF NEW.user_num < 1 THEN
              SET NEW.user_num  = 1;
              ELSEIF NEW.user_num > 45 THEN
              SET NEW.user_num= 45;
             END IF;
             END //
Query OK, 0 rows affected

DELIMITER ;

Limitations​

seekdb triggers have the following limitations:

  • You can create a trigger only on a permanent table. You cannot create a trigger on a temporary table.

  • You cannot use the CALL statement to return data to a client or to a stored procedure that contains dynamic SQL. However, a stored procedure or function can return data to a trigger by using an OUT or IN OUT parameter.

  • You cannot use a transaction control statement in a trigger. For example, you cannot use the START TRANSACTION, COMMIT, or ROLLBACK statement in a trigger. However, you can use the ROLLBACK TO SAVEPOINT statement to roll back to a savepoint. This is allowed because rolling back to a savepoint does not end the transaction.

  • Foreign keys do not activate triggers.

  • A trigger cannot return a value. Therefore, a trigger cannot contain a return statement. To immediately stop a trigger, you must use the LEAVE statement.

View trigger metadata​

To obtain metadata of a trigger, perform the following operations:

Contents

Edit this page