Tags:concept database sql triggers sqltriggers Status:🟩

SQL Triggers

Summary

Triggers are automatically executed functions that respond to certain events like INSERT, UPDATE, or DELETE on a table (or view) in a database. They can be set to execute either before or after the event. Triggers are useful for automating actions such as logging changes, enforcing business rules, or maintaining data integrity.

Details

Triggers can be executed before or after database operations (INSERT, UPDATE, DELETE).
They are crucial for maintaining data integrity, enforcing business rules, or logging historical data changes.
Multiple triggers can exist on the same table for the same event, but they will be executed in alphabetical order.

Triggers in PostgreSQL

PostgreSQL supports multiple triggers per table per event.
Triggers can run either per row or per statement. The focus is often on “per row” triggers for individual records.
NEW and OLD variables are used in triggers to access the new and old data, respectively:
- NEW: Represents the new data in INSERT and UPDATE operations.
- OLD: Represents the old data in UPDATE and DELETE operations.
The variable TG_OP contains the name of the operation (e.g., INSERT, UPDATE, or DELETE).

Use Case Examples

Triggers can be used to:

Check value constraints before inserting or updating records.
Prevent unauthorized updates or deletions.
Automatically update related records when changes occur.
Log changes for auditing purposes.

Trigger executing SQL

CREATE TRIGGER update_student_grade
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    UPDATE students
    SET grade = grade + 1
    WHERE id = NEW.id;
END;
 
--- The trigger is set to execute after an `UPDATE` operation on the `students` table.
-- The trigger directly performs an `UPDATE` without using a function.

Checking Values

Triggers can ensure that inserted or updated values conform to business rules or constraints.

CREATE FUNCTION CheckResult()
RETURNS TRIGGER
AS $$ 
BEGIN
  IF (NEW.result < 0.0) THEN
    RAISE EXCEPTION 'CheckResult: Result must be positive'
    USING ERRCODE = '45000';
  END IF;
  RETURN NEW;
END; 
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER CheckResult
BEFORE INSERT OR UPDATE ON Results
FOR EACH ROW EXECUTE PROCEDURE CheckResult();

Ban Updates/Deletes

You can create triggers to prevent rows from being modified or deleted.

CREATE FUNCTION BanChanges()
RETURNS TRIGGER
AS $$ 
BEGIN
  RAISE EXCEPTION 'BanChanges: Cannot change results!' 
  USING ERRCODE = '45000';
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER BanChanges
BEFORE UPDATE OR DELETE ON Results
FOR EACH ROW EXECUTE PROCEDURE BanChanges();

Update Records Automatically

Triggers can automatically update a related table after an event (like updating a record).

CREATE FUNCTION UpdateRecord()
RETURNS TRIGGER
AS $$ 
BEGIN
  IF NEW.result > (SELECT s.record FROM Sports s WHERE s.id = NEW.sportID) THEN
    UPDATE Sports SET record = NEW.result WHERE s.id = NEW.sportID;
  END IF;
  RETURN NEW;
END; 
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER UpdateRecord
AFTER INSERT OR UPDATE ON Results
FOR EACH ROW EXECUTE PROCEDURE UpdateRecord();
 
-- The "RETURN NEW;" could also be RETURN NULL since result of AFTER triggers is ignored.

Logging Changes

Whenever an update occurs, it’s important to log the changes for auditing purposes. A trigger can automatically log these changes in a separate table.

CREATE TABLE ChangeLog (
    log_id SERIAL PRIMARY KEY,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    operation VARCHAR(10),
    old_result FLOAT,
    new_result FLOAT
);
 
CREATE FUNCTION LogChanges()
RETURNS TRIGGER
AS $$
BEGIN
  INSERT INTO ChangeLog (operation, old_result, new_result)
  VALUES (TG_OP, OLD.result, NEW.result);
  RETURN NEW;
END; 
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER LogResultsUpdate
AFTER UPDATE ON Results
FOR EACH ROW EXECUTE PROCEDURE LogChanges();

Merged Triggers

Instead of having multiple triggers for similar tasks, it’s possible to merge logic into one trigger. This helps avoid potential conflicts between separate triggers. However, depending on the complexity, splitting the logic into two triggers may sometimes be cleaner.

CREATE FUNCTION MergedTriggerLogic()
RETURNS TRIGGER
AS $$
BEGIN
  -- Check constraint
  IF (NEW.result < 0.0) THEN
    RAISE EXCEPTION 'Result must be positive' USING ERRCODE = '45000';
  END IF;
 
  -- Update related record
  IF NEW.result > (SELECT s.record FROM Sports s WHERE s.id = NEW.sportID) THEN
    UPDATE Sports SET record = NEW.result WHERE id = NEW.sportID;
  END IF;
 
  RETURN NEW;
END; 
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER MergedTrigger
BEFORE INSERT OR UPDATE ON Results
FOR EACH ROW EXECUTE PROCEDURE MergedTriggerLogic();

Before vs After

BEFORE triggers are useful when you need to validate or modify data before it is written to the database. For example, checking constraints or transforming values.
AFTER triggers are ideal for actions that rely on the data being committed to the database, such as logging or updating other tables.

Triggers Speed

Triggers can be faster than executing queries from an external client because:

Code may be pre-compiled and optimized.
- Repeated queries don’t need to go through the optimizer.
They run directly on the server, eliminating the need for data transfer to clients.
The server may be more efficient at handling these operations.

Pros & Cons

Pros

Code runs faster:
- No context switch between client and server.
- Pre-compiled code can offer performance gains.
- No need for data transfer to clients.
Enhances security:
- Wraps data and functionality in the same location.
- Same consistent behavior across all clients.
Centralized logic:
- Same logic applies to all applications interacting with the database.

Cons

Code is less visible:
- Triggers are often hidden in system tables, making them easy to forget or overlook.
Maintenance challenges:
- Version control is difficult.
- Changes in database schema using GUIs could affect triggers.
Portability issues:
- Triggers are generally system-specific and may not easily transfer across different database platforms.

INDBS Noter

Explorer