O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required