SQL Interview Questions

Triggers in SQL are special types of stored procedures that automatically execute, or "trigger," in response to specific events on a table or view. These events can include actions like INSERT, UPDATE, or DELETE. Triggers are used to enforce business rules, maintain data integrity, and synchronize tables, among other things.

Characteristics of Triggers

1. Automatic Execution: Triggers are automatically invoked by the database engine when the specified event occurs.
2. Event-Driven: Triggers are defined to respond to specific events such as INSERT, UPDATE, or DELETE.
3. Associated with Tables/Views: Triggers are tied to a specific table or view and operate in the context of that object.

Types of Triggers

1. Before Triggers: Execute before the triggering event takes place.
2. After Triggers: Execute after the triggering event has taken place.
3. Instead Of Triggers: Used in views to perform actions instead of the triggering event.

Use Cases for Triggers

1. Data Validation and Integrity: Ensure that data meets certain criteria before being committed to the database.
2. Auditing and Logging: Automatically record changes to data for audit trails.
3. Enforcing Business Rules: Implement business logic that needs to be automatically enforced.
4. Synchronizing Tables: Keep related tables in sync by automatically updating or inserting related records.

Example of a Trigger

Before Insert Trigger

This example demonstrates a BEFORE INSERT trigger that checks if the value being inserted into a column meets a certain condition.

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary cannot be negative';
  END IF;
END;

• Event: BEFORE INSERT on the employees table.
• Action: If the new salary (NEW.salary) is less than 0, the trigger raises an error.

After Insert Trigger

This example demonstrates an AFTER INSERT trigger that logs changes to an audit table.

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_audit (employee_id, action, action_time)
  VALUES (NEW.employee_id, 'INSERT', NOW());
END;

• Event: AFTER INSERT on the employees table.
• Action: Insert a record into the employee_audit table with the employee ID, action type, and timestamp.

Advantages of Triggers

1. Automatic Enforcement: Automatically enforce business rules and data integrity constraints.
2. Consistency: Ensure that related actions are performed consistently.
3. Transparency: Operate transparently to the application, ensuring that certain actions always occur.

Disadvantages of Triggers

1. Complexity: Can add complexity to the database schema and make debugging more difficult.
2. Performance Impact: May introduce performance overhead, especially if the trigger logic is complex or if triggers are nested.
3. Hidden Logic: Business logic embedded in triggers can be harder to find and maintain compared to application-level logic.

Managing Triggers

• Creating Triggers: Use the CREATE TRIGGER statement to define a new trigger.
• Viewing Triggers: Use database-specific commands to list triggers (e.g., SHOW TRIGGERS in MySQL).
• Dropping Triggers: Use the DROP TRIGGER statement to remove a trigger.

-- Dropping a trigger
DROP TRIGGER check_salary;

Conclusion

Triggers are a powerful feature in SQL that enable automatic execution of specified actions in response to certain events on a table or view. They are particularly useful for enforcing data integrity, auditing changes, and implementing business rules. However, they should be used judiciously to avoid adding unnecessary complexity and performance overhead to the database system.