A MariaDB TRIGGER is a special type of stored procedure that is associated with a table, and that executes automatically when a particular event occurs for that table. A trigger must be associated with a permanent table. It can not be associated with a TEMPORARY table or a view.
Trigger names exist in the database namespace, meaning that all triggers must have unique names within a database. Triggers in different databases can have the same name.Note: The main difference between the trigger and procedure is that a trigger is called automatically when a particular event occurs for the table. In contrast, a stored procedure must be called explicitly.
CREATE TRIGGER Statement
The MariaDB CREATE TRIGGER statement is used to create a trigger. CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger.
The syntax of creating a trigger in MariaDB is given below:
CREATE TRIGGER trigger_name (AFTER | BEFORE) (INSERT | UPDATE | DELETE) ON table_name FOR EACH ROW BEGIN --variable declarations --trigger code END;
The above statement creates a trigger named trigger_name that is associated with table named table_name. The keyword AFTER or BEFORE indicates the trigger action time. The keyword INSERT, UPDATE or DELETE indicates triggering event. The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event.
Types of Triggers in MariaDB
There are six types of triggers in MariaDB:
|Before Insert Trigger||Activates before the insertion of data into the table|
|After Insert Trigger||Activates after the insertion of data into the table|
|Before Update Trigger||Activates before the update of data in the table|
|After Update Trigger||Activates after the update of the data in the table|
|Before Delete Trigger||Activates before the data is removed from the table|
|After Delete Trigger||Activates after the deletion of data from the table|
DROP TRIGGER Statement
Once a trigger is created in MariaDB, it can be removed by using DROP TRIGGER statement. The DROP TRIGGER statement requires the TRIGGER privilege for the table associated with the trigger. Triggers for a table are also dropped if you drop the table.
The syntax of removing a trigger in MariaDB is given below:
DROP TRIGGER [IF EXISTS] [database.]trigger_name;
The IF EXISTS is an optional parameter that conditionally removes trigger only if it exists on the database. If a trigger is deleted which does not exist, it will raise an error.
The database name is optional. If it is omitted, the trigger is dropped from the default database. The above statement removes a trigger named trigger_name.
To drop a trigger named sales_order_insert from default database, the following statement can be used:
DROP TRIGGER sales_order_insert;