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