Create and drop a TRIGGER

Overview

Triggers are special types of stored procedures that automatically executes or fires in response to certain events on a table or view in a database. Understanding how to create and manage triggers is essential for implementing complex business logic directly on the database level to ensure data integrity. It is also used to automate tasks such as auditing changes, enforcing constraints, or updating related tables.

Set up tables for adding a trigger

Before you create a trigger, it is important to set up your tables properly.

Follow instructions in Create a TABLE to create some tables and insert data into them.

Create a trigger

Creating a trigger involves specifying the timing (BEFORE or AFTER the event), the event (INSERT, UPDATE, or DELETE) that triggers it, and the operations to be performed. We will cover two of the most common types of SQL triggers: BEFORE INSERT and AFTER INSERT.

BEFORE INSERT

The BEFORE INSERT trigger is set up to automatically run special checks or tasks right before a new entry is added to a database table. This ensures that any data being added follows specific rules we've set in the trigger, maintaining consistency and quality.

Execute the command below to create a trigger:
```
CREATE TRIGGER <TriggerName>
BEFORE INSERT ON <TableName>
FOR EACH ROW
-- Trigger logic goes here
```
An example of the Trigger logic could be:
```
SET NEW.<ColumnName> = LOWER(NEW.<ColumnName>);
```
The CREATE TRIGGER clause initiates an automatic action in response to specific events on a table or view.

The BEFORE INSERT clause triggers actions to occur before a new record is inserted into the table.

The FOR EACH ROW clause specifies that the trigger operates on each row affected by the event individually.

The SET keyword is used within triggers to assign new values to specific columns or variables.

The NEW keyword refers to the new row data in insert or update operations.

The LOWER clause calls a function that converts text to lowercase.
Click the refresh button to see your trigger appear on the schema pane:

Success
Verify the trigger by performing the action it is designed to respond to.

For example, after setting up a BEFORE INSERT trigger which converts a column's data to lowercase before insertion, you can test the trigger's functionality by attempting to insert a record into the designated table using uppercase letters in the targeted column. Execute the following command to observe the trigger in action:
```
INSERT INTO <TableName> (<ColumnName>, ...)
VALUES ('UPPERCASE Value', ...);
```
Verify that the correct data has been inserted by running the following command for the table updated in step 2:
```
SELECT * FROM <TableName>;
```
Success

AFTER INSERT

The AFTER INSERT trigger is designed to perform certain actions or validations right after a new entry has been successfully added to a database table. This allows for further processing of the newly inserted data, such as updating related records or logging changes, ensuring that our data handling meets established criteria.

Execute the command below to create a trigger:
```
CREATE TRIGGER <TriggerName>
AFTER INSERT ON <TableName>
FOR EACH ROW
-- Trigger logic goes here
```
An example of trigger logic could be:
```
INSERT INTO <LogTableName> (<ForeignKeyColumn>, <TimestampColumn>)
VALUES (NEW.<ForeignKeyValue>, NOW());
```
The AFTER INSERT keyword triggers actions following a successful record insertion.

Note

For this example, ensure you have created a log table named <LogTableName> alongside your main table <TableName>. For guidance on creating a new table, please refer to Create a TABLE.

A log table in MySQL records changes or events that occur within the database, providing a historical record of transactions, system activities, or errors for monitoring, auditing, and troubleshooting purposes.
Click the refresh button to see your trigger appear on the schema pane:

Success
Verify the trigger by performing the action it is designed to respond to.

For example, after setting up a AFTER INSERT trigger which logs a record in the log table after insertion, you can test the trigger's functionality by attempting to insert a record into the designated table and note the change in the log table. Execute the following command to observe the trigger in action:
```
INSERT INTO <TableName> (<ColumnName>, ...)
VALUES ('anything', ...);
```
Verify that a log record has been created successfully by running the following command for the log table:
```
SELECT * FROM <LogTableName>;
```
Success

Drop a trigger

Finally, We will go over how to drop the trigger.

Execute the following command to drop the trigger in database:
```
DROP TRIGGER <TriggerName>;
```
The DROP TRIGGER statement is used to delete an existing trigger from the database.

You should see the trigger disappear from the schema pane when it is successfully dropped:

Success
Repeat step 2 and 3 from BEFORE INSERT or AFTER INSERT to verify if trigger has been dropped:

Success

Conclusion

By the end of this section, you have gained knowledge on the following tasks:

Understanding the purpose and functionality of triggers
Creating BEFORE INSERT triggers
Creating AFTER INSERT triggers
Dropping triggers