Example of CREATE TRIGGER Statement
To understand How to Delete a Trigger in MariaDB we need tables on which we will perform various operations and queries. Here we will consider a table called products and inventory_log where the products table contains id, name, price, stock and created_at as Columns. Also The inventory_log table contains id, product_id, action, quantity and timestamp as Columns.
The Below example shows the creation of triggers for all the three operations INSERT, UPDATE and DELETE.
The following query creates a table products with multiple columns having respective data types and constraints.
Structure of products table looks below:
Structure of inventory_log table looks below:
Explanation: The query creates a table named as inventory_log with id, product_id, action, quantity and timestamp with respective constraints.
Let’s Inserts some sample records to the products table.
INSERT INTO products (name, price, stock)
VALUES ('T-shirt', 19.99, 50),
('Mug', 9.99, 20),
('Hat', 14.99, 30);
Output:
Explanation: The query inserts 3 rows to the products table with values in the name, price and stock column. The id column auto increments and the created_at auto enters the defaults value which is the current timestamp of insertion.
Example 1: Creating trigger for INSERT operation
The query creates a trigger named as track_inventory_insert for the INSERT operation in the products table.
CREATE TRIGGER track_inventory_insert AFTER INSERT ON products
FOR EACH ROW
INSERT INTO inventory_log (product_id, action, quantity, timestamp)
VALUES (NEW.id, 'added', NEW.stock, NOW());
Output:
Explanation: The query creates a trigger named as track_inventory_insert after the INSERT event in the products table. The statement carried out by the triggers is a INSERT operation to the inventory_log table with values in the product_id, action, quantity and timestamp columns.
Let’s Testing a Trigger with an INSERT Operation
The following query inserts a new row to the products table.
INSERT INTO products (name, price, stock) VALUES ('Pen', 2.99, 100);
Output:
Explanation: As we see in the above image the new record in the product table. Also we have seen in the below image that reflect that the one record has been reflect in inventory_log Trigger.
Explanation: The INSERT operation in products table executed the track_inventory_insert trigger which ran the respective INSERT operation in inventory_log table.
Example 2: Creating trigger for UPDATE operation
The query creates a trigger named as track_inventory_update for the UPDATE operation in the products table.
CREATE TRIGGER track_inventory_update BEFORE UPDATE ON products
FOR EACH ROW
INSERT INTO inventory_log (product_id, action, quantity, timestamp)
VALUES (NEW.id, 'updated', NEW.stock - OLD.stock, NOW());
Output:
Explanation: The query creates a trigger named as track_inventory_update before the UPDATE event in the products table. The statement carried out by the triggers is a INSERT operation to the inventory_log table with values in the product_id, action, quantity and timestamp columns.
Let’s Testing a Trigger with an UPDATE Operation
The following query updates a row in the products table.
UPDATE products SET stock = 75 WHERE name = 'T-shirt';
Output:
Explanation: As we see in the above image the UPDATE Operation is performed to update record in the product table. Also we have seen it reflect in the below image in inventory_log table.
Explanation: The UPDATE operation in products table executed the track_inventory_update trigger which ran the respective INSERT operation in inventory_log table.
Example 3: Creating trigger for DELETE operation
The query creates a trigger named as track_inventory_delete for the DELETE operation in the products table.
CREATE TRIGGER track_inventory_delete BEFORE DELETE ON products
FOR EACH ROW
INSERT INTO inventory_log (product_id, action, quantity, timestamp)
VALUES (1, 'removed', OLD.stock, NOW())
Output
Explanation: The query creates a trigger named as track_inventory_delete before the DELETE event in the products table. The statement carried out by the triggers is a INSERT operation to the inventory_log table with values in the product_id, action, quantity and timestamp columns. The product_id value is hard coded to 1 due to foreign constraints which can be removed or modified to allow NULL values
Testing a Trigger with an DELETE Operation
The following query deletes a row in the products table.
DELETE FROM products WHERE name = 'Hat';
Output:
Explanation: After perform DELETE Operation on the products table, we have seen that the reflect also saw in the inventory_log table.
Explanation: The DELETE operation in products table executed the track_inventory_delete trigger which ran the respective INSERT operation in inventory_log table. The product_id value is hard coded to 1 due to foreign constraints which can be removed or modified to allow NULL values
MariaDB Create Triggers
Triggers are a very useful and powerful feature of MariaDB. It is a database object associated with a table that activates if an INSERT, UPDATE or DELETE operations are performed. The name itself reflects their action as they run immediately without any human intervention when the respective operations are performed.
In this article, you will learn about how to create triggers and use them easily so that we can automate complex tasks and perform data management easily.
Contact Us