This topic describes how to create a trigger.
Prerequisites
The user creating the trigger must have the following privileges:
Privileges on the table associated with the trigger, including
SELECT,INSERT,UPDATE, andDELETE.Privileges on the statements to be executed when the trigger is activated.
Syntax
You can use the CREATE statement to create a trigger.
The SQL syntax for creating a trigger is as follows:
CREATE
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_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:
The trigger name
trigger_namemust be unique.tbl_name: the name of the table on which to create the trigger.BEFORE | AFTER: the timing of the trigger action. This specifies whether the trigger activates before or after each row is inserted into the table.INSERT | UPDATE | DELETE: the triggering event, which is the type of operation that activates the trigger.FOR EACH ROW: defines the body of the trigger. This statement is executed each time the trigger is activated, once for each row affected by the triggering event.FOLLOWS | PRECEDES: defines the firing sequence of triggers. In the current version of OceanBase AI Database, you can define multiple triggers on the same table with the same triggering event and action time. By default, triggers with the same triggering event and action time are activated in the order they are created. To influence the firing sequence, you can useFOLLOWSandPRECEDES. SpecifyingFOLLOWSmeans the new trigger is activated after existing triggers; specifyingPRECEDESmeans the new trigger is activated before existing triggers.
OceanBase AI Database also defines NEW.columnName and OLD.columnName:
In an
INSERTtrigger,NEW.columnNamerepresents the new data that is about to be (in aBEFOREtrigger) or has been (in anAFTERtrigger) inserted.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.columnNamerepresents the original data that is about to be or has been deleted.OLD.columnNameis read-only, whileNEW.columnNamecan be assigned a value usingSETwithin the trigger.
Additionally, if the trigger to be defined contains multiple execution statements, you can use the BEGIN ... END statement to enclose the entire code block, indicating its start and end.
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, and each statement in the list must end with a semicolon (;). In SQL statements, a semicolon (;) is an identifier that marks the end of a statement. When a semicolon is encountered, it indicates that the current block of statements has ended, and the system proceeds to execute the next block. This can lead to errors during execution if the interpreter cannot find an END that matches the BEGIN. To avoid such errors, you can use the DELIMITER command to modify the statement terminator.
An example of the DELIMITER command is shown below:
DELIMITER new_delimiter
Here, new_delimiter can be set to one or more symbols of different lengths. The default is the semicolon (;), but you can change it to other symbols, such as #.
After adding the DELIMITER command, statements that follow it will no longer cause an error when they use a semicolon, until the specified terminator (#) is encountered, at which point the statement is considered finished.
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.
Examples
Create a trigger named
test_trgand associate it with the tabletestto 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 affectedCreate a trigger with multiple statements.
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; temporary tables do not support triggers.
Triggers cannot use
CALLstatements to invoke stored procedures that return data to the client or use dynamic SQL. However, stored procedures or functions are allowed to return data to the trigger through parameters of theOUTorINOUTtype.Triggers cannot contain segments that start or end transactions, such as
START TRANSACTION,COMMIT, orROLLBACK. However, rolling back to a savepoint is allowed because this does not terminate the transaction.Foreign keys do not activate triggers.
Triggers do not allow return values; therefore, they cannot contain return statements. If you need to immediately stop a trigger, you must use the
LEAVEstatement.