Triggers to prevent insertions, updates, and/or deletions on a table in PostgreSQL

lesson_plan.md

Introduction

In relational databases, it is possible to request that the database engine executes a function when an event is about to take place within a table. The events that can trigger such a function are INSERT, UPDATE and DELETE.

In the case of triggers called before these operations, database engines give us an additional power: the power to intervene, and possibly prevent the operation from taking place.

This is the core concept of this lesson.

Trigger Syntax in SQL

The process of installing a trigger on a relational table using SQL is two-fold:

1. You need to define a function with a special syntax (RETURNS TRIGGER) that is invoked by the trigger
2. You need to define a trigger to execute the function at the right time on the table

Trigger PL/pgSQL Function

This function is written using a special language: procedural PostgreSQL, abbreviated as PL/pgSQL.

create or replace function trigger_function_prevent()
    returns trigger language plpgsql 
    immutable leakproof
as $$ 
begin 
    if TG_WHEN = 'BEFORE' and TG_LEVEL = 'ROW' then
        raise warning '% ON %.% PROHIBITED.',
            (case TG_OP
                when 'INSERT' then 'INSERTIONS'
                when 'UPDATE' then 'UPDATES'
                when 'DELETE' then 'DELETIONS'
                else 'OPERATIONS'
            end), TG_TABLE_SCHEMA, TG_TABLE_NAME;
        return null; 
    else
        raise warning 'PROHIBITIONS ON %.% MISCONFIGURED.',
            TG_TABLE_SCHEMA, TG_TABLE_NAME;
    end if;
end $$;

Usage

-- HOW TO USE FOR PREVENTING INSERTIONS ON TABLE my_table
create or replace trigger trigger_no_insertions
    before insert
    on my_table for each row
    execute function trigger_function_prevent();

-- HOW TO USE FOR PREVENTING UPDATES ON TABLE my_table
-- (this makes my_table immutable, which is useful)
create or replace trigger trigger_no_updates
    before update
    on my_table for each row
    execute function trigger_function_prevent();

-- HOW TO USE FOR PREVENTING DELETIONS ON TABLE my_table
create or replace trigger trigger_no_deletions
    before delete
    on my_table for each row
    execute function trigger_function_prevent();

Example

This example also contains a simple lesson plan for how this can be applied and tested, using a temporary table in PostgreSQL.

Hint: temporary tables are only visible to the psql session from which they were created, and are conveniently dropped at the end of the session, making them ideal for learning and experimentation.

-- create a temporary table for this class
create temp table my_table (
    id smallserial,
    label text
);

-- inserts work OK
insert into my_table (label) 
    values ('some'), ('things'), ('dont'), ('change'), ('oops');
select * from my_table order by id;

-- updates work OK
update my_table set label = 'don''t' where label = 'dont';
select * from my_table order by id;

-- deletes work OK
delete from my_table where label = 'oops';
select * from my_table order by id;

-- let's prohibit insertions on my_table
create or replace trigger trigger_no_insertions
    before insert
    on my_table for each row
    execute function trigger_function_prevent();

-- insertions now FAIL
insert into my_table (label) 
    values ('but...'), ('wait...');
select * from my_table order by id;

-- let's prohibit updates on my_table
create or replace trigger trigger_no_updates
    before update
    on my_table for each row
    execute function trigger_function_prevent();

-- updates now FAIL
update my_table set label = 'do' where label = 'don''t';
select * from my_table order by id;

-- let's prohibit deletions on my_table
create or replace trigger trigger_no_deletions
    before delete
    on my_table for each row
    execute function trigger_function_prevent();

-- deletions now FAIL
delete from my_table where label = 'don''t';
select * from my_table order by id;

Appendix: Empty Table

If you want to define a table in a relational database to serve as a data type for inheritance purposes only, which must remain empty (i.e. no insertions, updates, deletions, etc.), the approach above might be overkill.

To achieve this, an easier alternative is available, using an impossible table constraint:

    constraint abstract_no_data check(false) no inherit

This will make any data added to the table illegal, and hence will ensure that the table remains empty. The no inherit clause allows you to still use this table as a template (using the keyword inherits) without transferring this impossible constraint to the child table.

An example that illustrates this can be found below:

-- Create an abstract base table that must remain empty
create temp table base_entity (
    id serial primary key,
    created_at timestamp default now(),
    constraint abstract_no_data check(false) no inherit
);

-- Create a concrete table that inherits the structure
create temp table users (
    name text not null,
    email text unique
) inherits (base_entity);

-- This works
insert into users (name, email) values ('Alice', 'alice@example.com');
select * from users order by id;

-- This fails
insert into base_entity (id) values (1);
select * from base_entity order by id;

Do you like this lesson plan? would like a video version of it?