A trigger is a database object associated with a table. It can be activated when a statement inserts, updates, or deletes rows in the associated table, and can also be configured to activate before or after the triggering event.
For example, you can use the INSERT or LOAD DATA statement to insert rows. Each row insertion activates the INSERT trigger once; inserting two rows in a batch triggers the trigger twice. You can activate the trigger before each row is inserted into the table or after each row is updated.
Trigger types
The current version of OceanBase AI Database supports the following types of triggers:
INSERTtriggers: activated when a row is inserted. Can be triggered byINSERT,LOAD DATA, andREPLACEstatements.UPDATEtriggers: activated when a row is updated, and can be triggered by anUPDATEstatement.DELETEtriggers: activated when a row is deleted. Can be triggered byDELETEorREPLACEstatements.
The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is special. Each row activates a BEFORE INSERT trigger, followed by either an AFTER INSERT trigger or both a BEFORE UPDATE and AFTER UPDATE trigger. Whether an AFTER INSERT trigger or both BEFORE UPDATE and AFTER UPDATE triggers are fired depends on whether the row has a duplicate key.
Create a trigger
You can use the CREATE TRIGGER statement to create a trigger.
The user who creates the trigger must have the following privileges:
The privileges on the tables associated with the current trigger, including
SELECT,INSERT,UPDATE, andDELETE.Trigger privileges, namely the
CREATEprivilege.The privileges on the statements to be executed after the trigger is activated.
The SQL 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 as follows:
IF NOT EXISTS: If the trigger name to be created already exists and theIF NOT EXISTSoption is not used, an error message is triggered. IfIF NOT EXISTSis specified, no error is triggered; only a warning message is generated.The trigger name
trigger_namemust be unique.table_namespecifies the name of the table on which to create the trigger.BEFOREorAFTERspecifies when the action is triggered. For example, it indicates whether the trigger is activated before or after each row is inserted into the table.INSERT,UPDATE, orDELETEindicates the triggering event, which is the type of operation that activates the trigger.FOR EACH ROWdefines the body of the trigger. This statement is executed each time the trigger is activated, once for each row affected by the triggering event.
OceanBase AI Database also defines NEW.columnName and OLD.columnName:
In an
INSERTtrigger,NEW.columnNamerefers to the new data that is about to be inserted (BEFORE) or has already been inserted (AFTER). Here,columnNameis the name of a column in the corresponding table.In an
UPDATEtrigger,OLD.columnNamerepresents the original data that is about to be or has been modified, andNEW.columnNamerepresents the new data that is about to be or has been modified.In a
DELETEtrigger,OLD.columnNamerefers to the original data that is about to be deleted or has already been deleted.OLD.columnNameis read-only, whereasNEW.columnNamecan be assigned a value usingSETin the trigger.
Example 1: Create a trigger named test_trg and associate it with the test table to activate INSERT operations. The trigger acts as an accumulator, summing the values inserted into columns of 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
Example 2: Create triggers trg2_t and trg3_t to execute after trigger trg1_t, and create trigger trg4_t to execute before trigger 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);
obclient> 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
Additionally, if a trigger requires multiple execution statements, you can use the BEGIN ... END statement to enclose the entire code block, indicating its start and end respectively.
The syntax for the BEGIN ... END statement is as follows:
BEGIN
[statement_list]
END
Here, statement_list represents a list of one or more statements. Each statement in the list must end with a semicolon (;). In SQL statements, a semicolon (;) is the identifier that marks the end of a statement. When a semicolon is encountered, it indicates that the current statement has ended, and the system proceeds to execute the next statement. As a result, during execution, the interpreter may report an error because it cannot find an END that matches the BEGIN. To avoid such errors, you can use the DELIMITER command to modify the statement terminator.
The following example shows the usage of the DELIMITER statement:
DELIMITER new_delemiter
The new_delemiter can be set to a single character or multiple characters, with a default value of a semicolon (;). You can change it to other characters, for example, #.
After adding the DELIMITER statement, statements that follow it will not report an error if they use a semicolon; instead, the statement is considered complete only when the specified terminator (#) is encountered.
Notice
After you use the DELIMITER statement to modify the statement terminator, you must change it back to the default semicolon (;) after the statement execution is complete.
Here is an 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 have the following limitations:
Triggers can only be created on permanent tables, not on temporary tables.
Triggers cannot use the
CALLstatement to return data to the client or contain stored procedures with dynamic SQL. However, stored procedures or functions are allowed to return data to the trigger through parameters of theOUTorIN OUTtype.You cannot use statements that start or end a transaction in a trigger. For example, you cannot use
START TRANSACTION,COMMIT, orROLLBACK. However, you can roll back to a savepoint because rolling back to a savepoint does not end the transaction.A foreign key does not activate a trigger.
Triggers do not support return values; therefore, they cannot contain return statements. If you need to immediately stop a trigger, use the
LEAVEstatement.
View trigger metadata
To obtain metadata related to a trigger, perform the following operation:
Query the
TRIGGERStable in theINFORMATION_SCHEMAdatabase. For more information, see INFORMATION_SCHEMA TRIGGERS.The
SHOW CREATE TRIGGERstatement. For more information, see SHOW CREATE TRIGGER.The
SHOW TRIGGERSstatement. For more information, see SHOW TRIGGERS.