Creating Triggers to Monitor Table Opreations

This tutorial demonstrates how to implement triggers in MySQL to automatically log operations performed on a table. We'll create triggers that fire on INSERT, UPDATE, and DELETE operations, capturing each action in a separate tracking table.

Dataabse Schema Setup

First, let's create two tables: one for storing product information and another for recording opeartions performed on that table.

Creating the items table:

mysql> CREATE TABLE items (
    ->     item_id INT(10) NOT NULL PRIMARY KEY UNIQUE COMMENT 'Item ID',
    ->     item_name VARCHAR(20) NOT NULL COMMENT 'Item Name',
    ->     description VARCHAR(50) COMMENT 'Description',
    ->     vendor VARCHAR(20) NOT NULL COMMENT 'Vendor',
    ->     location VARCHAR(20) COMMENT 'Location');
Query OK, 0 rows affected, 1 warning (0.03 sec)

Creating the audit_log table:

mysql> CREATE TABLE audit_log (
    ->     log_id INT(10) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'Log ID',
    ->     operation_type VARCHAR(20) COMMENT 'Operation Type',
    ->     operation_timestamp VARCHAR(20) NOT NULL COMMENT 'Operation Time');
Query OK, 0 rows affected, 1 warning (0.03 sec)

Trigger Implementation

Now we'll create three triggers to automatically record operations on the items table. Each trigger will insert a record into the audit_log table when its corresponding operation occurs.

Creating the BEFORE INSERT trigger:

mysql> CREATE TRIGGER item_before_insert
    -> BEFORE INSERT ON items FOR EACH ROW
    -> INSERT INTO audit_log VALUES (NULL, 'insert item', NOW());
Query OK, 0 rows affected (0.01 sec)

Creating the AFTER UPDATE trigger:

mysql> CREATE TRIGGER item_after_update AFTER UPDATE
    -> ON items FOR EACH ROW
    -> INSERT INTO audit_log VALUES (NULL, 'update item', NOW());
Query OK, 0 rows affected (0.01 sec)

Creating the AFTER DELETE trigger:

mysql> CREATE TRIGGER item_after_delete AFTER DELETE
    -> ON items FOR EACH ROW
    -> INSERT INTO audit_log VALUES (NULL, 'delete item', NOW());
Query OK, 0 rows affected (0.01 sec)

Testing the Triggers

Let's verify that the triggers work correctly by performing various operations on the items table.

Inserting a new record:

mysql> INSERT INTO items VALUES (1, 'Electronics', 'Smart Device', 'TechCorp', 'Building A');
Query OK, 1 row affected (0.01 sec)

Updating an existing record:

mysql> UPDATE items SET location = 'Building B'
    -> WHERE item_id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Deleting a record:

mysql> DELETE FROM items WHERE item_id = 1;
Query OK, 1 row affected (0.00 sec)

Each of these operations automatically creates an entry in the audit_log table, capturing the operation type and timestamp.

Removing Triggers

When triggers are no longer needed, they can be removed using the DROP TRIGGER statement. Below we demonstrate removing two of the three triggers:

mysql> DROP TRIGGER item_before_insert;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TRIGGER item_after_update;
Query OK, 0 rows affected (0.01 sec)

After executing these commands, only the item_after_delete trigger remains active on the items table.