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
as opposed to the row-level BEFORE
triggers, you can still make it impossible by raising an error if TRUNCATE
is attempted. Create an AFTER
trigger using the same function as the one used previously for DELETE
:
CREATE TRIGGER disallow_truncate AFTER TRUNCATE ON delete_test1 FOR EACH STATEMENT EXECUTE PROCEDURE cancel_op();
Here you are, without TRUNCATE
:
postgres=# TRUNCATE delete_test1; ERROR: YOU ARE NOT ALLOWED TO TRUNCATE ROWS IN public.delete_test1
Of course, you can also raise the error in a BEFORE
trigger, but in that case you will need to write your own unconditional raise-error trigger function instead of cancel_op()
.