Play Store Application link – SQL in 18 steps – App on Google Play

In the world of databases, sometimes we need to perform specific actions automatically when certain events occur. This is where SQL Triggers come into play. Think of them as “database alarms” that get triggered before or after a particular action (like inserting, updating, or deleting a record).

What are SQL Triggers?

A trigger is a piece of SQL code that executes automatically in response to certain events on a table. These events can be:

• INSERT (When a new record is added)
• UPDATE (When a record is modified)
• DELETE (When a record is removed)

Triggers help in enforcing business rules, maintaining audit logs, and automating tasks like updating a related table when a change occurs.

Types of SQL Triggers

Triggers can be executed before or after a specific event:

1. BEFORE INSERT Trigger

Executes before a new record is inserted into the table. Useful for validation or setting default values.

Example: Automatically capitalize names before inserting into a users table (MySQL):

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

2. AFTER INSERT Trigger

Executes after a new record is inserted. Useful for logging or updating another table.

Example: Log new user registrations (MySQL):

CREATE TRIGGER after_insert_users
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO user_logs(user_id, action, timestamp)
VALUES (NEW.id, 'User Registered', NOW());

3. BEFORE UPDATE Trigger

Executes before a record is updated. Useful for tracking changes.

Example: Ensure salary never decreases (MySQL):

CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
IF NEW.salary < OLD.salary THEN
  SET NEW.salary = OLD.salary;
END IF;

4. AFTER UPDATE Trigger

Executes after a record is updated. Useful for maintaining audit logs.

Example: Track salary updates (Oracle PL/SQL):

CREATE OR REPLACE TRIGGER after_update_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)
  VALUES (:OLD.id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

5. BEFORE DELETE Trigger

Executes before a record is deleted. Useful for preventing accidental deletions.

Example: Prevent deleting admin users (MySQL):

CREATE TRIGGER before_delete_admins
BEFORE DELETE ON users
FOR EACH ROW
IF OLD.role = 'admin' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin users cannot be deleted';
END IF;

6. AFTER DELETE Trigger

Executes after a record is deleted. Useful for logging deletions.

Example: Log deleted orders (SQL Server T-SQL):

CREATE TRIGGER after_delete_orders
ON orders
AFTER DELETE
AS
INSERT INTO order_logs(order_id, action, deleted_at)
SELECT id, 'Order Deleted', GETDATE() FROM deleted;

Differences in Trigger Syntax (MySQL vs Oracle)

When to Use SQL Triggers?

✅ Enforcing business rules automatically (e.g., preventing negative balances in a bank account). ✅ Logging changes for auditing purposes. ✅ Maintaining data consistency across multiple tables. ✅ Automating workflows, like sending emails when new orders are placed.

🚨 Avoid Overusing Triggers! Too many triggers can slow down database performance and make debugging difficult.

Conclusion

SQL Triggers are powerful tools for automating actions in a database. They help maintain consistency, enforce rules, and track changes. However, they should be used wisely to avoid performance issues. By understanding when and how to use them effectively, you can make your database operations more efficient!