postgres tutorial 14 trigger

The INSTEAD OF is used only for INSERT, UPDATE, or DELETE on the views.

CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
   ON table_name
   [FOR [EACH] {ROW | STATEMENT}]
   EXECUTE PROCEDURE trigger_function
 
checks if the last name of employee changes, it will insert the old last name into the employee_audits table including employee id, last name, and the time of change.

CREATE TABLE employees(
   id serial primary key,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL
);

CREATE TABLE employee_audits (
   id serial primary key,
   employee_id int4 NOT NULL,
   last_name varchar(40) NOT NULL,
   changed_on timestamp(6) NOT NULL
)

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS trigger AS $$
BEGIN
 IF NEW.last_name <> OLD.last_name THEN
 INSERT INTO employee_audits(employee_id,last_name,changed_on)
 VALUES(OLD.id,OLD.last_name,now());
 END IF;

 RETURN NEW;
END; $$
LANGUAGE plpgsql;

bind the trigger function to the employees table.

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

INSERT INTO employees (first_name, last_name)
VALUES ('Lily', 'Bush');

employees

UPDATE employees
SET last_name = 'Brown'
WHERE ID = 2;

employees

employee audit

ALTER TRIGGER last_name_changes ON employees
RENAME TO log_last_name_changes;

ALTER TABLE employees
DISABLE TRIGGER log_last_name_changes;

ALTER TABLE employees
DISABLE TRIGGER ALL;

DROP TRIGGER log_last_name_changes ON employees;