postgres trigger - ghdrako/doc_snipets GitHub Wiki

Using Trigers we can manage the following events:

  • BEFORE INSERT/UPDATE/DELETE/TRUNCATE

  • AFTER INSERT/UPDATE/DELETE/TRUNCATE

  • INSTEAD OF INSERT/UPDATE/DELETE

  • If triggers and rules are simultaneously present on the same event in a table, the rules always fire before the triggers.

  • If there are multiple triggers on the same event of a table (for example, BEFORE INSERT), triggers are executed in alphabetical order.

Trigger execution order

PostgreSQL first executes statement-level BEFORE triggers, then row-level BEFORE triggers, then row-level AFTER triggers and finally statement-level AFTER triggers. If more than one trigger in the same category exists, they are executed in alphabetical order according to the trigger name.

Trigger

Triger special variables

  • NEW
  • OLD
  • TG_OP - tells us from which event the trigger is fired. The possible values of the TG_OP variable are INSERT, DELETE, UPDATE, and TRUNCATE.

Trigger managament

ALTER TABLE employees ENABLE TRIGGER salary_before_update; -- enable single trigger
ALTER TABLE employees DISABLE TRIGGER log_last_name_changes;
ALTER TABLE employees ENABLE TRIGGER ALL;                  -- enable all triggers on table
ALTER TABLE employees DISABLE TRIGGER ALL;
ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;   -- renaming trigger
ALTER TRIGGER emp_stamp ON emp DEPENDS ON EXTENSION emplib; -- Marking a Trigger as being dependent on an Extension

CREATE TABLE employees(
   id INT GENERATED ALWAYS AS IDENTITY,
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   department TEXT NOT NULL,  
   salary INT NOT NULL,
   PRIMARY KEY(id)
);

INSERT INTO employees (first_name, last_name, department, salary)
VALUES 
('Alice', 'Smith', 'Engineering', 125000),
('Bob', 'Baker', 'Sales', 85000);

SELECT * FROM employees;

# Now let's create a entry table where we audit new employees

CREATE TABLE new_employee_logs (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  joining_date date NOT NULL
);

Create a function

Trigger function - a user-defined function that doesn’t take any arguments. The function returns a value of the type trigger.

The function invoked by the trigger must be defined in a particular way, as shown in the prototype here:

CREATE OR REPLACE FUNCTION function_name RETURNS trigger as
$$
DECLARE
....
BEGIN
RETURN
END;
$$
LANGUAGE 'plpgsql';

The functions that are called by the triggers are functions that have no input parameters and must return a TRIGGER type; these functions take the parameters from the NEW/OLD records.

CREATE OR REPLACE FUNCTION new_employee_joining_func() RETURNS TRIGGER AS $new_employee_trigger$
   BEGIN
      INSERT INTO new_employee_logs(first_name, last_name, joining_date)
      VALUES (new.first_name, new.last_name, current_timestamp);
      RETURN NEW;
   END;
$new_employee_trigger$ LANGUAGE plpgsql;

Create a trigger for this function (note that this is an AFTER INSERT and a row-level trigger)

CREATE TRIGGER trigger_name 
   {BEFORE | AFTER |INSTEAD OF} { event_name }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

CREATE TRIGGER new_employee_trigger 
AFTER INSERT ON employees
FOR EACH ROW 
EXECUTE PROCEDURE new_employee_joining_func();

# Let's insert some values within employees table and notice how trigger works

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Watson', 'Sales', 65000);
SELECT * FROM employees;
# We see the new entry is added

# Now check the emp_joining_logs table and we see the
# The name and time on which the new value was added
SELECT * from new_employee_logs;

create a trigger when a value in a table is updated

# Create another table for auditing salary increment

CREATE TABLE employee_salary_logs (
   id INT GENERATED ALWAYS AS IDENTITY,
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   old_salary INT NOT NULL,
   incremented_salary INT NOT NULL,
   incremented_on DATE NOT NULL
);


# Now let's create a function
# If the salary is incremented, then let's aduit the last name and the time we incremented the salary and what the old salary and new salary is

CREATE OR REPLACE FUNCTION employee_salary_update_func()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  IF NEW.salary <> OLD.salary THEN
     INSERT INTO employee_salary_logs(first_name, last_name, old_salary, incremented_salary, incremented_on)
     VALUES(OLD.first_name, OLD.last_name, OLD.salary, NEW.salary, now());
  END IF;

  RETURN NEW;
END;
$$


# Let's create the trigger

CREATE TRIGGER employee_salary_update_trigger
  AFTER UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE employee_salary_update_func();


# Now let's view the employees table entires

SELECT * FROM employees;

# Let's update the salary of an employee

UPDATE employees
SET salary = 75000
WHERE last_name = 'Watson'; 


SELECT * FROM employees;

# We see the value is changed
# Now let's observe the audits table

SELECT * FROM employee_salary_logs;

# We see the old salary, new salary and the time on which we changed the salary


# One more update, increment all salaries by 10%

UPDATE employees
SET salary = 1.1 * salary

# Again check (there should be a total of 4 entries, 2 for Watson)

SELECT * FROM employee_salary_logs;

Create a table which tracks operations performed on other tables

CREATE TABLE table_changed_logs (
  change_type TEXT NOT NULL,  
  changed_table_name TEXT NOT NULL, 
  changed_on date NOT NULL
);


# Function to track what changes were made to a table (INSERT, UPDATE, DELETE)

CREATE OR REPLACE FUNCTION table_changed_logs_func() RETURNS TRIGGER AS $table_changed_trigger$
   BEGIN
      INSERT INTO table_changed_logs(change_type, changed_table_name, changed_on)
      VALUES (TG_OP, TG_TABLE_NAME, current_timestamp);
      RETURN NEW;
   END;
$table_changed_trigger$ LANGUAGE plpgsql;


# Create two triggers using the same function

CREATE TRIGGER employees_inserted_trigger 
AFTER INSERT ON employees
EXECUTE PROCEDURE table_changed_logs_func();

CREATE TRIGGER employees_updated_trigger 
AFTER UPDATE ON employees
EXECUTE PROCEDURE table_changed_logs_func();


# Insert a new record into the employees table

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Julia', 'Dennis', 'Engineering', 80000);


# Table update tracked and other trigger also fired

SELECT * FROM table_changed_logs;


SELECT * from new_employee_logs;

# Update employee salaries

UPDATE employees
SET salary = 1.05 * salary
WHERE salary < 85000


# Both triggers would have been fired

SELECT * FROM table_changed_logs;

SELECT * FROM employee_salary_logs;

We can see the trigger using the following query (should be 4 triggers)

SELECT tgname FROM pg_trigger;

Drop triggers

DROP TRIGGER employees_inserted_trigger ON employees;

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];


testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

Drop trigger

DROP TRIGGER trigger_name;

list all triggers in the current database

SELECT * FROM pg_trigger;

list the triggers on a particular table

SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='table_name';

Event trigger

Event triggers can only be created by super users and will be called for all DDL statements, executed by any user.

Example using event trigger to enforce naming:

CREATE EVENT TRIGGER enforce_naming_conventions
ON ddl_command_end
EXECUTE FUNCTION check_object_names();

The check_object_names() function can then access the details of newly created objects using a query like this

SELECT object_identity
FROM pg_event_trigger_ddl_command()
WHERE NOT in_extension
   AND command_tage LIKE 'CREATE%';