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 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().