Database Reference
In-Depth Information
Disallowing TRUNCATE
You may have noticed that the preceding trigger can easily be bypassed for
DELETE
if you delete everything using
TRUNCATE
.
While you cannot simply skip
TRUNCATE
by returning
NULL
(this works only for
row-level
BEFORE
triggers), you still can make it impossible by raising an error if
TRUNCATE
is attempted. Create an
AFTER
trigger using the same function used pre-
viously for
DELETE
:
CREATE TRIGGER disallow_truncate
AFTER TRUNCATE ON delete_test1
FOR EACH STATEMENT
EXECUTE PROCEDURE cancel_op();
And here you are, with no more
TRUNCATE
:
postgres=# TRUNCATE delete_test1;
ERROR: YOU ARE NOT ALLOWED TO TRUNCATE ROWS IN
public.delete_test1
Of course, you could also raise the error in a
BEFORE
trigger, but then you would need
to write your own unconditional raise-error trigger function instead of
cancel_op()
.
