PL/pgSQL TRUNCATE Triggers and DROP TRIGGER

Master both TRUNCATE triggers (statement-level only, no NEW/OLD) and the DROP TRIGGER statement for permanent removal. Critical correction: original used RAISE NOTICE then claimed it 'aborts the operation' — NOTICE doesn't abort, RAISE EXCEPTION does. Also covers why ON table_name is required (trigger names are local to tables in PG), idempotent drops with IF EXISTS, and the DROP vs DISABLE decision.

This page covers two related topics: triggers that fire on TRUNCATE events, and the DROP TRIGGER statement for removing triggers altogether. Both are about lifecycle — TRUNCATE triggers respond to a wholesale data lifecycle event; DROP TRIGGER ends a trigger's own lifecycle.

TRUNCATE TABLE is the fast bulk-delete operation in PostgreSQL — it removes all rows from a table without scanning them, by simply re-initializing the underlying file. It's much faster than DELETE on a large table. But that speed comes with constraints:

The big takeaway: TRUNCATE does not fire DELETE triggers. If you have audit-logging on DELETE, a TRUNCATE bypasses it entirely — you'll lose all rows with no audit trail. To handle TRUNCATE you need a separate BEFORE TRUNCATE or AFTER TRUNCATE trigger.

DROP TABLE IF EXISTS truncate_audit;
DROP TABLE IF EXISTS companies;

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

CREATE TABLE truncate_audit (
    id           SERIAL PRIMARY KEY,
    table_name   TEXT,
    truncated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    truncated_by TEXT      DEFAULT CURRENT_USER
);

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

The protective pattern: prevent applications from truncating a table at all. Use RAISE EXCEPTION to abort:

CREATE OR REPLACE FUNCTION refuse_truncate()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION 'Truncating the % table is not allowed', TG_TABLE_NAME;
    RETURN NULL;       -- never reached, but keeps the function valid
END;
$$;

CREATE TRIGGER companies_no_truncate
    BEFORE TRUNCATE ON companies
    FOR EACH STATEMENT
    EXECUTE FUNCTION refuse_truncate();

-- Try it
TRUNCATE TABLE companies;

A more practical pattern: don't refuse the truncation, but log it before it happens. The companies table will still be emptied, but we'll have a record:

DROP TRIGGER IF EXISTS companies_no_truncate ON companies;

CREATE OR REPLACE FUNCTION log_truncate()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO truncate_audit (table_name)
    VALUES (TG_TABLE_NAME);
    RETURN NULL;
END;
$$;

CREATE TRIGGER companies_log_truncate
    BEFORE TRUNCATE ON companies
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_truncate();

-- Truncate the table
TRUNCATE TABLE companies;

-- Verify both: companies is empty, audit has a record
SELECT * FROM companies;
SELECT * FROM truncate_audit;

Companies table emptied, but the audit table holds a record of when and by whom. Note that we don't have row-level data to log here — TRUNCATE doesn't expose individual rows to its triggers.

To remove a trigger entirely, use DROP TRIGGER. Unlike disabling, this is permanent — the trigger definition is gone from the database.

DROP TABLE IF EXISTS staff;

CREATE TABLE staff (
    staff_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email    VARCHAR(100)
);

-- Validator function: refuse usernames shorter than 8 characters
CREATE OR REPLACE FUNCTION check_staff_username()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Check NULL first; otherwise length(NULL) returns NULL and the comparison silently fails
    IF NEW.username IS NULL THEN
        RAISE EXCEPTION 'username cannot be NULL';
    END IF;

    IF length(NEW.username) < 8 THEN
        RAISE EXCEPTION 'username must be at least 8 characters (got %)', NEW.username;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER username_check
    BEFORE INSERT OR UPDATE ON staff
    FOR EACH ROW
    EXECUTE FUNCTION check_staff_username();

DROP TRIGGER username_check ON staff;

Trigger removed. The function (check_staff_username) still exists — dropping a trigger doesn't drop the function it called. To remove the function too, use DROP FUNCTION check_staff_username(); separately.

Deployment scripts should never error on the first run when the trigger doesn't exist yet. Use IF EXISTS:

DROP TRIGGER IF EXISTS username_check ON staff;
DROP TRIGGER IF EXISTS username_check ON staff;   -- run twice; second is a no-op

Each run emits a NOTICE but doesn't error — the script can run repeatedly without breaking.

If you find yourself disabling a trigger and never re-enabling it, drop it instead. A permanently-disabled trigger sits in the schema confusing readers ("does this run? why is it here?") — clarity is better than archeology.

1. TRUNCATE triggers must be statement-level. No FOR EACH ROW, no NEW or OLD records.
2. Use RAISE EXCEPTION, not RAISE NOTICE, when you mean to abort. NOTICE just prints a message — execution continues.
3. TRUNCATE doesn't fire DELETE triggers. If you have audit-on-DELETE logic, add a separate TRUNCATE trigger or your audit will have gaps.
4. Use IF EXISTS in DROP TRIGGER scripts — first runs won't error; reruns won't break.
5. Always specify ON table_name when dropping. Trigger names are scoped to their table.
6. Check NULL before length-based comparisons. length(NULL) returns NULL, which makes length(NEW.col) < N OR NEW.col IS NULL silently fail to catch NULL inputs.
7. Drop a trigger when you mean to remove it permanently. Disable for temporary suspension; drop when the trigger has no future.

• TRUNCATE triggers fire on TRUNCATE TABLE. They're statement-level only — no NEW or OLD, no row-level scope.
• BEFORE TRUNCATE can refuse the operation (RAISE EXCEPTION) or do bookkeeping like audit logging or copying rows to an archive.
• AFTER TRUNCATE fires after the rows are gone — useful for reset-of-related-counters logic.
• TRUNCATE does not fire DELETE triggers. Plan for it explicitly.
• DROP TRIGGER name ON table permanently removes a trigger. The function it called is unaffected — drop separately if needed.
• Use IF EXISTS in deployment scripts for idempotency.
• Trigger names in PostgreSQL are local to their table — the ON table_name clause is required when dropping.