Create a DML trigger

Create a trigger

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

To create a trigger, you must have the following privileges:

• Privileges on the table associated with the trigger, such as SELECT, INSERT, UPDATE, and DELETE

• CREATE TRIGGER privilege

• Privileges on statements to be executed after the trigger fires

Syntax:

CREATE
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

Parameters:

• trigger_name: the name of the trigger. The name must be unique.

• tbl_name: the name of the table for which the trigger is created.

• BEFORE or AFTER: specifies whether the trigger fires before or after the triggering event. For example, whether the trigger fires before or after each row is inserted into the associated table.

• INSERT, UPDATE, or DELETE: the type of operation for which the trigger fires.

• FOR EACH ROW: the trigger entity. This statement is executed when the trigger fires and is executed for each row affected by the trigger event.

OceanBase Database also supports NEW.columnName and OLD.columnName.

• For an INSERT trigger, NEW.columnName specifies new data that is inserted in a BEFORE scenario or was inserted in an AFTER scenario.

  columnName indicates a column name in the corresponding data table.

• For an UPDATE trigger, OLD.columnName specifies existing data that is updated. NEW.columnName specifies new data after the update.

• For a DELETE trigger, OLD.columnName specifies existing data that is deleted.

• Values in OLD.columnName are read-only, whereas values in NEW.columnName can be specified by using SET statements.

Example:

Create a trigger named test_trg, associate it with the test table, and fire the INSERT trigger. The trigger serves as an accumulator to calculate the sum of the values of columns inserted into the table.

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

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

In addition, if you want to create a trigger that contains multiple statements, you can use the BEGIN … END statement to specify the start and end of the entire code block.

BEGIN … END syntax:

BEGIN
[statement_list]
END

In the preceding syntax, statement_list specifies one or a list of statements to be executed. Each statement in the list must end with a semicolon (;). A semicolon (;) indicates the end of an SQL statement. When the system detects a semicolon (;), the system determines that a statement ends, and then starts to execute the statement. In this case, the interpreter fails to find the END keyword that matches the BEGIN keyword during SQL execution. Therefore, an error is reported. To prevent the error, you can use a DELIMITER command to change the delimiter of a statement.

Sample DELIMITER command:

DELIMITER new_delemiter

new_delemiter specifies the delimiter of a statement. A delimiter can be a sign of one or more bytes in length. The default delimiter is a semicolon (;). You can change the semicolon (;) to another delimiter, such as a number sign (#).

After the DELIMITER command is added to the syntax, statements with semicolons (;) following the command can be executed without an error reported. This is because the system does not consider that a statement ends until it detects the specified delimiter, such as a number sign (#).

Notice

If you use the DELIMITER command to change the delimiter, make sure that you reset the delimiter to the semicolon (;) after the statements are executed.

Example:

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

obclient>DELIMITER #

obclient>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

obclient>DELIMITER;

Limitations on triggers

Triggers in MySQL mode have the following limitations:

• Multiple triggers that are associated with the same table cannot share the triggering event or firing time.

• You can create a trigger only for a permanent table, but not for a temporary table.

• Triggers cannot use the CALL statement to return data to clients or use the stored procedures of dynamic SQL statements. However, stored procedures and functions can use OUT or INOUT to return data to triggers.

• Triggers cannot use clauses to start or end transactions, such as START TRANSACTION for starting a transaction, COMMIT for committing a transaction, and ROLLBACK for rolling back a transaction. However, triggers can roll back a transaction to a specific savepoint because this operation does not end the transaction.

• A foreign key action cannot fire a trigger.

• A trigger cannot return a value. Make sure that the trigger does not contain a statement that is used to return values. If you want to instantly stop a trigger, use a LEAVE statement.