SQL Triggers

Basic Concepts of SQL Triggers

• Trigger Event: The event that causes a trigger to be executed, such as INSERT, UPDATE, or DELETE statements.
• Trigger Action: The action performed by the trigger, typically involving modifications to one or more tables.
• Triggering Event: The occurrence that initiates the trigger's execution. This includes data manipulation operations like inserting, updating, or deleting records in a table.
• Triggering Table: The table on which the trigger event is defined, such as the table being modified or accessed.

How SQL Triggers Work

SQL Triggers Types and Use Cases

Before and After Triggers

• Before Triggers (BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE): These triggers execute before the triggering event. They can be used to modify the data before it is saved in the database, which is useful for data validation or cleanup.
• After Triggers (AFTER INSERT, AFTER UPDATE, AFTER DELETE): These triggers execute after the triggering event. They can be useful for actions that depend on the changes made by the event, such as maintaining a record of changes or updating related tables.

Row-Level and Statement-Level Triggers

• Row-Level Triggers (FOR EACH ROW): These triggers are executed once for each row affected by the triggering event. They are useful for operations that need to be applied to individual records, such as maintaining a history of changes for each row.
• Statement-Level Triggers: These triggers are executed once for each triggering event, regardless of the number of rows affected. They are suited for operations that need to be performed once per event, such as maintaining summary or audit tables.

Working with SQL Triggers Examples

Syntax for Creating an SQL Trigger

  CREATE TRIGGER trigger_name
  {BEFORE|AFTER} {INSERT|UPDATE|DELETE}
  ON table_name
  [FOR EACH ROW]
  trigger_body;

• CREATE TRIGGER: This is the command used to create a new trigger.
• trigger_name: A unique name for the trigger.
• BEFORE|AFTER: The trigger timing, determining when the trigger action is executed with respect to the triggering event.
• INSERT|UPDATE|DELETE: The triggering event that activates the trigger.
• ON table_name: Specifies the table on which to create the trigger.
• FOR EACH ROW: Optional modifier for row-level triggers.
• trigger_body: The code that will be executed when the trigger is activated.

Practical Use Case: Creating a Trigger

  CREATE TRIGGER trg_salary_history
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
  INSERT INTO employees_salary_history (employee_id, old_salary, new_salary, changed_on)
  VALUES (:old.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP);

Modifying and Deleting SQL Triggers

Altering an SQL Trigger Example

  ALTER TRIGGER trg_salary_history
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
  INSERT INTO employees_salary_history (employee_id, old_salary, new_salary, changed_on, updated_by)
  VALUES (:old.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP, USER);

Dropping an SQL Trigger Example

  DROP TRIGGER trg_salary_history;

This code removes the 'trg_salary_history' trigger from the database. Note that dropping a trigger does not affect the table or the data in the table, it only removes the trigger functionality.

SQL Triggers vs Functions

When to Use Triggers and When to Use Functions

• Use Triggers: Choose triggers when you want to automate tasks in response to data manipulation events, perform cross-table consistency checks, or maintain historical records.
• Use Functions: Consider functions when you need to encapsulate reusable logic, perform complex calculations, or manipulate data without triggering an event. Functions should be used when logic requires flexibility and doesn't rely on specific table events.

Performance Considerations

• Triggers: Since triggers execute automatically, they may introduce processing overhead and slow down data manipulation operations. Overuse of triggers or implementing complex logic within triggers can negatively impact database performance.
• Functions: Functions can be optimized for performance as they are explicitly called, and their execution time is generally faster. However, poorly written functions can also impact the system's overall performance if they are resource-intensive or called frequently within data manipulation operations.

Recommendations for Using Triggers and Functions

• Triggers:
  1. Avoid using excessive triggers, as this may hinder database performance.
  2. Keep trigger logic simple, clean, and focused on a single purpose.
  3. Use triggers mainly for enforcing data integrity and consistency between related tables.
• Functions:
  1. Optimize functions for performance by minimizing resource consumption and avoiding nested function calls.
  2. Encapsulate complex computation and data manipulation logic within functions for better code organization and reusability.
  3. Make use of built-in functions whenever possible, as they are typically more efficient than user-defined functions.

Working with SQL Triggers Inserted Tables

Purpose of the Inserted Table

• Storing new values for inserted or updated rows, allowing the trigger to reference or compare these values.
• Facilitating the implementation of business logic, constraint enforcement, and auditing features by providing a means to examine the changes being made.
• Enhancing data integrity by enabling the trigger to detect discrepancies between the old and new data values and apply appropriate actions before committing the changes.

How SQL Triggers Inserted Table is Used

• INSERT operations: The inserted table contains all the newly added rows.
• UPDATE operations: The inserted table contains the updated rows with their new values.
• DELETE operations: The inserted table is not used, as the deleted rows are stored in another temporary table called the "deleted" table.

Practical Examples with SQL Triggers Inserted Tables

Here are some examples of SQL Triggers being used.

Example: Logging Changes to a Table

  CREATE TRIGGER trg_salary_log
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
  BEGIN
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, modified_at)
    VALUES (:old.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP);
  END;

Example: Ensuring Data Integrity with SQL Triggers Inserted

  CREATE TRIGGER trg_validate_order
  BEFORE INSERT ON orders
  FOR EACH ROW
  BEGIN
    DECLARE @stock INT;
    SELECT stock INTO @stock FROM products WHERE product_id = :new.product_id;
    IF @stock < :new.quantity THEN
       ROLLBACK; -- If the requested quantity is more than available stock, roll back the transaction
       RETURN;
    END IF;
  END;