Introduction to the PostgreSQL BEFORE TRUNCATE trigger

ATRUNCATE TABLE statement removes all from a table without creating any logs, making it faster than aDELETE operation.

PostgreSQL allows you tocreate a trigger that fires before aTRUNCATE event occurs.

ABEFORE TRUNCATE trigger is a statement-level trigger because theTRUNCATE statement deletes all the rows from the table, not individual rows.

Although theTRUNCATE operation deletes rows from a table, it does not activate theDELETE trigger includingBEFORE andAFTER DELETE triggers.

Here’s the step for creating aBEFORE TRUNCATE trigger:

First,define a user-defined function that will execute before theTRUNCATE event:

CREATE OR REPLACE FUNCTION trigger_function_name()RETURNS TRIGGERAS$$BEGIN    -- This logic will be executed before the TRUNCATE operation    -- ...    RETURN NULL;END;$$LANGUAGE plpgsql;

The function returnsNULL indicating that the trigger doesn’t return additional output.

Second, create aBEFORE TRUNCATE trigger and associate the function with it:

CREATE TRIGGER trigger_nameBEFORE TRUNCATE ON table_nameFOR EACHSTATEMENTEXECUTE FUNCTION trigger_function_name();

Since theBEFORE TRUNCATE trigger is a statement-level trigger, you need to specify theFOR EACH STATEMENT clause in theCREATE TRIGGER statement.

UnlikeINSERT,UPDATE, orDELETE, which supportBEFORE andAFTER trigger types,TRUNCATE only supportsBEFORE triggers.

PostgreSQL BEFORE TRUNCATE trigger example

We’ll create aBEFORE TRUNCATE trigger to prevent applications from truncating a table.

First,create a new table calledcompanies to store company data:

CREATE TABLE companies(   idSERIAL PRIMARY KEY,   name VARCHAR(255)NOT NULL);

Second,insert some rows into thecompanies table:

INSERT INTO companies(name)VALUES ('Apple'),       ('Microsoft'),       ('Google')RETURNING*;

Output:

id |   name----+-----------  1 | Apple  2 | Microsoft  3 | Google(3 rows)

Third, define a function that will execute when aTRUNCATE event occurs:

CREATE OR REPLACE FUNCTION before_truncate_companies()RETURNS TRIGGERAS$$BEGIN    RAISE NOTICE'Truncating the companies table is not allowed';    RETURN NULL;END;$$LANGUAGE plpgsql;

Fourth, create a trigger that fires before aTRUNCATE event occurs:

CREATE TRIGGER before_truncate_companies_triggerBEFORE TRUNCATE ON companiesFOR EACHSTATEMENTEXECUTE FUNCTION before_truncate_companies();

Fifth, attempt to truncate thecompanies table:

TRUNCATE TABLE companies;

Output:

NOTICE:  Truncating the companiestable is not allowedTRUNCATE TABLE

The output indicates that theBEFORE TRUNCATE trigger fires, raising an exception that aborts theTRUNCATE operation.

Summary

• ABEFORE TRUNCATE trigger is a statement-level trigger.
• Create aBEFORE TRUNCATE trigger to fire before aTRUNCATE event.

Last updated onMarch 29, 2024

On this page

• Introduction to the PostgreSQL BEFORE TRUNCATE trigger
• PostgreSQL BEFORE TRUNCATE trigger example
• Summary