Learning of SQL Day 32

 

Day 32: Triggers

Introduction:

• Triggers are database objects that automatically execute a specified set of SQL statements when certain events occur, such as INSERT, UPDATE, or DELETE operations. They are used to enforce rules, automate tasks, and maintain data integrity.

Key Concepts:

• Trigger: A set of SQL statements that automatically executes in response to specific events.

• CREATE TRIGGER: The SQL statement used to create a trigger.

• BEFORE and AFTER: Specifies when the trigger should execute relative to the triggering event.

• FOR EACH ROW: Indicates that the trigger operates on each row affected by the event.

SQL Commands and Examples:

1. Creating a Trigger:

   • Define a trigger that executes before an INSERT operation.

sql

-- Create a trigger that logs new student insertions
CREATE TRIGGER log_student_insert
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO StudentLogs (StudentID, Action, ActionTime)
    VALUES (NEW.StudentID, 'INSERT', NOW());
END;

2. Trigger with UPDATE Operation:

   • Define a trigger that executes after an UPDATE operation.

sql

-- Create a trigger that logs student updates
CREATE TRIGGER log_student_update
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO StudentLogs (StudentID, Action, ActionTime)
    VALUES (NEW.StudentID, 'UPDATE', NOW());
END;

3. Trigger with DELETE Operation:

   • Define a trigger that executes before a DELETE operation.

sql

-- Create a trigger that logs student deletions
CREATE TRIGGER log_student_delete
BEFORE DELETE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO StudentLogs (StudentID, Action, ActionTime)
    VALUES (OLD.StudentID, 'DELETE', NOW());
END;

Practice Exercise:

1. Use the Students table and create a new StudentLogs table with columns LogID, StudentID, Action, ActionTime.

2. Write a query to create a trigger that logs new student insertions.

3. Write a query to create a trigger that logs student updates.

4. Write a query to create a trigger that logs student deletions.

sql

-- Create the StudentLogs table
CREATE TABLE StudentLogs (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    Action VARCHAR(50),
    ActionTime DATETIME
);

-- Create a trigger that logs new student insertions
CREATE TRIGGER log_student_insert
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO StudentLogs (StudentID, Action, ActionTime)
    VALUES (NEW.StudentID, 'INSERT', NOW());
END;

-- Create a trigger that logs student updates
CREATE TRIGGER log_student_update
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO StudentLogs (StudentID, Action, ActionTime)
    VALUES (NEW.StudentID, 'UPDATE', NOW());
END;

-- Create a trigger that logs student deletions
CREATE TRIGGER log_student_delete
BEFORE DELETE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO StudentLogs (StudentID, Action, ActionTime)
    VALUES (OLD.StudentID, 'DELETE', NOW());
END;

Important Tips:

• Triggers can help maintain data integrity and enforce business rules automatically.

• Be cautious with triggers, as they can introduce complexity and affect performance.

• Use appropriate trigger types (BEFORE or AFTER) based on your requirements.

Understanding triggers allows you to automate tasks and enforce rules at the database level.