Triggers in databases are special procedures that are automatically executed in response to a specific event (INSERT, UPDATE, DELETE) on a table or view.
Let's walk through an example
Imagine we have a users table and a user_logs table. We can create a trigger that records an event whenever a new user is created. Instead of implementing this logic in the service, it is more reliable to use a trigger that runs at the database level.
To implement the trigger, we can use the following SQL code:
We create a trigger named after_user_insert that fires automatically after a new record is inserted into the users table.
AFTER INSERT ON users means that it runs after the INSERT operation on the users table.
FOR EACH ROW means that the trigger fires separately for each new row. Inside the trigger, we write data to user_logs.
NEW.id is the id value of the new user that was just added to the users table.
BEFORE and AFTER
As you can see from the SQL code above, we use AFTER, meaning the trigger runs after the INSERT operation. In general, a trigger can run either after a given operation or before it. To run it before the operation, you need to use the BEFORE keyword.