Skip to content

More on Triggers

Getting Internal Information

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

    BEGIN

        RAISE NOTICE 'TG_NAME: %', TG_NAME;
        RAISE NOTICE 'TG_RELNAME: %', TG_RELNAME;
        RAISE NOTICE 'TG_TABLE_SCHEMA: %', TG_TABLE_SCHEMA;
        RAISE NOTICE 'TG_WHEN: %', TG_WHEN;
        RAISE NOTICE 'TG_LEVEL: %', TG_LEVEL;
        RAISE NOTICE 'TG_OP: %', TG_OP;
        RAISE NOTICE 'TG_NARGS: %', TG_NARGS;
        RAISE NOTICE 'TG_ARGV: %', TG_NAME;

        RETURN NEW;
    END;
$$

CREATE TRIGGER trg_trigger_variables_display
    AFTER INSERT
    ON t_temperature_log
    FOR EACH ROW
    EXECUTE PROCEDURE fn_trigger_variables_display();

INSERT INTO t_temperature_log  ( add_date, temperature ) values ('2020-02-02', -40);

Disallow DELETE on table

-- create sample table

create table test_delete (
    id int
);

insert into test_delete (id ) values (1),(2),(3);

select * from test_delete;

-- creating function

create or replace function fn_generic_cancel_op()
returns trigger
language plpgsql
as
$$
    begin

    if TG_WHEN = 'AFTER' THEN
        raise exception 'you are not allowed to % rows in %.%', tg_op,tg_table_schema,tg_table_name;
    end if;

    raise notice '% on rows in %.% wont happen', tg_op, tg_table_schema, tg_table_name;
    return null; 

    end;
$$

-- creating trigger : AFTER

CREATE TRIGGER trg_disallow_delete
AFTER DELETE
ON test_delete
FOR EACH ROW
EXECUTE PROCEDURE fn_generic_cancel_op();

delete from test_delete where id = 1;

-- creating trigger : BEFORE

CREATE TRIGGER trg_disallow_delete_before
BEFORE DELETE
ON test_delete
FOR EACH ROW
EXECUTE PROCEDURE fn_generic_cancel_op();

delete from test_delete where id = 1;

-- checking

select * from test_delete;

Disallow truncating

CREATE TRIGGER trg_disallow_truncate_after
AFTER TRUNCATE
ON test_delete
FOR EACH STATEMENT
EXECUTE PROCEDURE fn_generic_cancel_op();


CREATE TRIGGER trg_disallow_truncate_beforE
BEFORE TRUNCATE
ON test_delete
FOR EACH STATEMENT
EXECUTE PROCEDURE fn_generic_cancel_op();

Creating Audit Trigger

  • To log data changes to tables in a consistent and transparent manner.
CREATE TABLE  audit (
    id INT
);

CREATE TABLE audit_log (
    username TEXT,
    add_time TIMESTAMP,
    table_name TEXT,
    operation TEXT,
    row_before JSON,
    row_after JSON
);
  • Please not that new OLD are not null for DELETE and INSERT triggers.
CREATE OR REPLACE FUNCTION fn_audit_trigger()
RETURNS TRIGGER 
LANGUAGE PLPGSQL
AS
$$
BEGIN

    DECLARE
        old_row json = NULL;
        new_row json = NULL;

    BEGIN

        IF TG_OP IN ('UPDATE','DELETE') THEN

            old_row = row_to_json(OLD);


        END IF;

        IF TG_OP IN ('INSERT','UPDATE') THEN

            new_row = row_to_json(NEW);

        END IF;

        INSERT INTO audit_log 
            ( username, add_time, table_name, operation, row_before, row_after )
        values
            (
                session_user,
                NOW(),
                TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
                TG_OP,
                old_row,
                new_row
            );

        RETURN NEW;
    END;    

END;
$$

-- bind trigger

CREATE TRIGGER trg_audit_trigger
AFTER INSERT OR UPDATE OR DELETE
ON audit
FOR EACH ROW
EXECUTE PROCEDURE fn_audit_trigger();

-- Queries

insert into audit(id) values (1),(2),(3);

update audit
set id = '100'
where id = 1;

delete from audit
where id = 2;

select * from audit;
select * from audit_log;

Creating Conditional Triggers

  • Created by using generic WHEN clause.
  • With a WHEN clause, you can write some conditions except a subquery
-- sample table

CREATE TABLE mytask (
    task_id SERIAL PRIMARY KEY,
    task text
);

-- trigger function

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

    RAISE EXCEPTION '%', TG_ARGV[0];

    RETURN NULL;

END;
$$

-- function binding to trigger

CREATE TRIGGER trg_no_update 
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE
ON mytask
FOR EACH STATEMENT
WHEN 
(
    EXTRACT ('DOW' FROM CURRENT_TIMESTAMP) = 5
    -- 5 means friday
    AND CURRENT_TIME > '12:00'
)
EXECUTE PROCEDURE fn_cancel_with_message('NO UPDATE ARE ALLOWED');

-- Queries

INSERT INTO mytask (task) values ('task 1'), ('task 2'), ('task 3');

Disallow updating Primary Key of table

create table pg_table(
    id serial primary key,
    t text
);

insert into pg_table(t) values ('t1'),('t2');

create trigger disallow_pk_change
after update of id
on pg_table
for each row
execute procedure fn_generic_cancel_op();

update pg_table 
set id = 10
where id = 1;

Event Triggers

  • Event triggers are data-specific and not bind or attached to a table
  • Unlike regular triggers they capture system level DLL events
  • Event triggers can be BEFORE or AFTER triggers
  • Trigger function can be written in any language except SQL
  • Event triggers are disabled in the single user mode and can only be created by a superuser

  • Syntax : CREATE EVENT TRIGGER trg_name

  • Before creating an event trigger, we must have a function that the trigger will execute

  • The function must return a specifi type called EVENT_TRIGGER

  • This function need not (and may not) return a valuel the return type serivces merely as s signal that the function is to be invoked as an event trigger.

  • Can we create conditional event trigger ? Yes, using the when clause

  • Event trigger cannot be executed in an aborted transaction

Event trigger events

when explaination
ddl_command_start This event occurs jsut BEFORE a CREATE, ALTER, or DROP DLL command is executed
ddl_command_end This event occurs just AFTER a create, alter, or drop command has finished executing
table_rewrite This event occurs just before a table is re written by some action of the commands ALTER TABLE and ALTER TYPE.
sql_drop This evetn occurs just before the ddl_command_end eevent for the commands that frop database objects

Event trigger variables

  • TG_TAG : this variable contains the 'TAG' or the command for which the trigger is executed.
  • TG_EVENT : This variable contains the event name, which can be ddl_command_start, ddl_comman_end, and sql_drop.

Creating an auditing event trigger

CREATE TABLE audit_dll (
    audit_ddl_id SERIAL PRIMARY KEY,
    username TEXT,
    ddl_event TEXT,
    ddl_command TEXT,
    ddl_add_time TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION fn_event_audit_ddl()
RETURNS EVENT_TRIGGER
LANGUAGE PLPGSQL
SECURITY DEFINER 
AS
$$
    BEGIN

        INSERT INTO public.audit_dll
        (username, ddl_event, ddl_command, ddl_add_time)
        VALUES 
        (session_user, TG_EVENT, TG_TAG, NOW());        

        RAISE NOTICE 'DDL activity is created';

    END;
$$

-- without condition
create event trigger trg_event_audit_ddl_no_cond
on ddl_command_start
execute procedure fn_event_audit_ddl();

-- with condition

create event trigger trg_event_audit_ddl
on ddl_command_start
when
 TAG IN ('CREATE TABLE')
execute procedure fn_event_audit_ddl();

Dont allow anyone to create table between time

CREATE OR REPLACE FUNCTION fn_event_abort_create_table_func()
RETURNS EVENT_TRIGGER
LANGUAGE PLPGSQL
SECURITY DEFINER
AS
$$
    DECLARE
        current_hour int = EXTRACT ('HOUR' FROM NOW());
    BEGIN
        IF current_hour between 4 and 16 then
            RAISE EXCEPTION 'tables are not allowed to be created during 9-4';
        END IF;
    END;
$$

CREATE EVENT TRIGGER trg_event_create_table_function
ON ddl_command_start 
WHEN
    TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE fn_event_abort_create_table_func();

Dropping event trigger

drop event trigger trg_name;