Triggers

A postgresql trigger is a functoin invoked automatically whenever 'an event' associated with a table occurs.
An event could be any of the following;
- INSERT
- UDPATE
- DELETE
- TRUNCATE
You can associate a trigger with a
- Table
- View
- Foreign Table
A trigger is a special 'user-defined function'
A trigger is automatically invoked
We can create a triger
- BEFORE
  - Trigger is fired before an event is about to happen
- AFTER
  - Trigger is fired after the event is completed
- INSTEAD
  - In case the event fails, trigger is fired
Cannot be fired manually
Fired in alphbetically order
DO Not change in primary key, foriegn key or unique key column
DO Not update records in the table that you normally read during the transaction
DO Not read data from a table that is updating during the same transaction
DO Not aggregate/summarized over the table that you are updating

Types of Triggers

Row level
- If row is marked for FOR EACH ROW, then trigger will be called for each row that is getting modfied by the event
Statement level
- The FOR EACH STATEMENT will call the trigger function only ONCE for each statement, regardless of the number of rows getting modified.

When	Event	Row-level	Statement-level
	INSERT/UDPATE/DELETE	Tables	Tables and view
before	Truncate
----------	--------------------	---------	---------------
	INSERT/UDPATE/DELETE	Tables	Tables and view
AFTER	Truncate
----------	--------------------	---------	---------------
	INSERT/UDPATE/DELETE	Views
INSTEAD OF	Truncate

Create your own Trigger in PostgreSQL

CREATE FUNCTION trigger_function( ) 
    RETURNS TRIGGER LANGUAGE PLPGSQL
AS $$
BEGIN
    -- TRIGGER LOGIC
END;
$$

Syntax

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

-- FOR EACH [ROW|STATEMENT]

Data Auditing with Triggers

Setup Example tables

CREATE TABLE players (
    player_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE player_audits (
    player_audit_id SERIAL PRIMARY KEY,
    player_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    new_name VARCHAR(100) NOT NULL,
    edit_date TIMESTAMP NOT NULL
);

Setup Function to TRIGGER

-- Function executed by TRIGGER

CREATE OR REPLACE FUNCTION fn_players_name_changes_log()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
    AS
$$
BEGIN

    IF NEW.name <> OLD.name THEN
        INSERT INTO player_audits
        ( player_id, name, new_name, edit_date ) 
        values ( OLD.player_id, OLD.name, NEW.name ,NOW() );
    END IF;

    RETURN NEW;
END;
$$

-- TRIGGER Definition

CREATE TRIGGER trg_players_name_changes
    BEFORE UPDATE 
    ON players
    FOR EACH ROW
    EXECUTE PROCEDURE fn_players_name_changes_log();

DML to fire above trigger

INSERT INTO players (name) VALUES ('UDAY'),('YADAV');

SELECT * FROM players;
select * from player_audits;

UPDATE players
SET name = 'UDAY 2'
WHERE player_id = 2;

Another Trigger Example

-- create table for example

CREATE TABLE t_temperature_log (
    id_temperature SERIAL PRIMARY KEY,
    add_date TIMESTAMP,
    temperature NUMERIC
);

-- create function for trigger to execute

CREATE OR REPLACE FUNCTION fn_temperature_value_check_at_insert()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN

    IF NEW.temperature < -30 then
        NEW.temperature = 0;
    END IF;

    RETURN NEW;

END;
$$

-- creating trigger

CREATE TRIGGER trg_temperature_value_check_at_insert
BEFORE INSERT 
ON t_temperature_log
FOR EACH ROW
EXECUTE PROCEDURE fn_temperature_value_check_at_insert();

-- Queries

INSERT INTO t_temperature_log ( add_date, temperature )
values ( '2020-10-01' , 10 );

select * from t_temperature_log;

INSERT INTO t_temperature_log ( add_date, temperature )
values ( '2020-10-01' , -33 );

select * from t_temperature_log;