ruk·si

🐘 PostgreSQL
Triggers

Updated at 2015-11-19 10:32

Trigger functions can be written in most available procedural languages e.g. PL/pgSQL, PL/Tcl, PL/Python like other PostgreSQL functions.

Trigger functions must be defined before the trigger itself can be created. It must be declared as a function taking no arguments and returning type trigger.

Trigger is the binding between function call and an event. Trigger is a specification that database should automatically execute a particular function whenever certain type of operation is performed.

-- Creating the function we are going to call.
-- DROP FUNCTION trigger_refresh_updated_at();
CREATE OR REPLACE FUNCTION trigger_refresh_updated_at()
RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
    NEW.updated_at := clock_timestamp();
    RETURN NEW;
END;
$$;

-- Defining that the function should be called after updates.
-- DROP TRIGGER users_after_update ON users;
CREATE TRIGGER users_after_update
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE PROCEDURE trigger_refresh_updated_at();

SELECT * FROM users;
UPDATE users SET name = 'Jane Doe' WHERE name = 'John Doe';
SELECT * FROM users;
-- You can see that the `updated_at` has changed

Note that a single trigger function can be used in multiple triggers.

CREATE TRIGGER table_name_before_all
BEFORE
  UPDATE OR DELETE
  ON users
FOR EACH ROW EXECUTE PROCEDURE trigger_refresh_updated_at();

CREATE TRIGGER table_name_after_all
AFTER
  INSERT OR UPDATE
  ON users
FOR EACH ROW EXECUTE PROCEDURE trigger_refresh_updated_at();

Triggers can be per-row or per-statement:

  • Per-row means that trigger function is invoked once for each row.
  • Per-statement means that trigger functions is invoked for each statement.

Triggers are BEFORE or AFTER:

  • Before triggers are invoked before triggering operation takes place.
  • After triggers are invoked after triggering operation has taken place.

Sources