SQL Triggers Intro 🎯

Welcome to our SQL Triggers tutorial! Today, we'll delve into the world of database automation with SQL Triggers. By the end of this lesson, you'll be able to create, modify, and manage triggers that react to specific events in your databases.

What are SQL Triggers? 📝

Triggers are special database objects that automatically execute a predefined SQL statement (also known as a stored procedure) when an event occurs in the database. They help maintain data integrity and consistency, making your databases more robust and efficient.

Why use SQL Triggers? 💡

Triggers are useful in various scenarios:

Enforcing data validation rules
Auditing changes to specific tables
Implementing complex business rules
Automating data manipulation tasks

Basic Trigger Structure 🎯

A trigger consists of three main components:

Event: An event that triggers the trigger, such as INSERT, UPDATE, or DELETE.
Action: The SQL statement(s) that are executed when the event occurs.
Target table: The table where the event takes place.

Creating a Basic Trigger 📝

Here's an example of creating a trigger that fires whenever a new row is inserted into the employees table:

sql

CREATE TRIGGER new_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (employee_id, action_type)
    VALUES (NEW.employee_id, 'Insert');
END;

In this example:

new_employee_insert is the trigger name.
AFTER INSERT ON employees specifies the event and target table.
FOR EACH ROW indicates that the action will be performed for each row inserted.
The stored procedure inside the BEGIN and END keywords inserts a new row into the employee_log table each time a new employee is added.

Testing the Trigger 🎯

Let's insert a new employee to test our trigger:

sql

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1001, 'John', 'Doe');

Check the employee_log table:

sql

SELECT * FROM employee_log;

You should see a new row with employee_id 1001 and action_type 'Insert'.

Trigger Types 📝

Triggers can be of two types:

Immediate triggers: These triggers execute immediately after the event occurs.
Deferred triggers: These triggers execute after the transaction that caused the event is committed, ensuring better transaction handling.

Quiz 🎯

By understanding SQL Triggers, you'll be able to create more robust and efficient databases. Stay tuned for more advanced topics on SQL Triggers! 🚀