Thread: Triggers on TRUNCATE?
G'day folks, Forgive this if it's totally "out there" but I was wondering why there's no support for triggers on a TRUNCATE operation. I note that a COPY IN fires an INSERT trigger (if defined). The only real use I can see for a TRUNCATE trigger would be for automatic audit trail generation - I usually have all operations on "core" tables audited. FYI, I'm using 7.4.3. As always, thanks for your time. Ciao. -- -------------------------------------------------------+--------------------- Daniel Baldoni BAppSc, PGradDipCompSci | Technical Director require 'std/disclaimer.pl' | LcdS Pty. Ltd. -------------------------------------------------------+ 856B Canning Hwy Phone/FAX: +61-8-9364-8171 | Applecross Mobile: 041-888-9794 | WA 6153 URL: http://www.lcds.com.au/ | Australia -------------------------------------------------------+--------------------- "Any time there's something so ridiculous that no rational systems programmerwould even consider trying it, they send forme."; paraphrased from "King OfThe Murgos" by David Eddings. (I'm not good, just crazy)
Daniel Baldoni <pgsql@lcds.com.au> writes:
> Forgive this if it's totally "out there" but I was wondering why there's no
> support for triggers on a TRUNCATE operation.
Because the entire point of TRUNCATE is not to scan the table contents.
If you want to fire triggers, just use "DELETE FROM foo".
regards, tom lane
On E, 2004-07-26 at 17:59, Tom Lane wrote: > Daniel Baldoni <pgsql@lcds.com.au> writes: > > Forgive this if it's totally "out there" but I was wondering why there's no > > support for triggers on a TRUNCATE operation. > > Because the entire point of TRUNCATE is not to scan the table contents. Maybe he was after statement level triggers ? We don't support them, and one also can't define a RULE for TRUNCATE - so the advice would be to use TRUNCATE very sparingly ;) Btw, does the right to TRUNCATE come with GRANT DELETE, or must one be a superuser to TRUNCATE ? --------------- Hannu
Hannu Krosing <hannu@tm.ee> writes:
> Btw, does the right to TRUNCATE come with GRANT DELETE, or must one be a
> superuser to TRUNCATE ?
You have to own the table (or be superuser of course). This implies the
right to drop its triggers, so TRUNCATE could be seen as dropping
triggers, DELETE, recreate triggers.
regards, tom lane
G'day folks, Tom Lane wrote: > Daniel Baldoni <pgsql@lcds.com.au> writes: >> Forgive this if it's totally "out there" but I was wondering why >> there's no support for triggers on a TRUNCATE operation. > > Because the entire point of TRUNCATE is not to scan the table contents. > > If you want to fire triggers, just use "DELETE FROM foo". Personally, I've never used TRUNCATE within a production system (I can't even recall using an unqualified DELETE). How do you then audit a TRUNCATE performed by somebody else (who, for "political" reasons, has superuser access)? Such actions aren't limited to attacks - but may simply be the result of "I thought it was a good idea at the time". :-( Ciao. -- -------------------------------------------------------+--------------------- Daniel Baldoni BAppSc, PGradDipCompSci | Technical Director require 'std/disclaimer.pl' | LcdS Pty. Ltd. -------------------------------------------------------+ 856B Canning Hwy Phone/FAX: +61-8-9364-8171 | Applecross Mobile: 041-888-9794 | WA 6153 URL: http://www.lcds.com.au/ | Australia -------------------------------------------------------+--------------------- "Any time there's something so ridiculous that no rational systems programmerwould even consider trying it, they send forme."; paraphrased from "King OfThe Murgos" by David Eddings. (I'm not good, just crazy)
> How do you then audit a TRUNCATE performed by somebody else (who, for > "political" reasons, has superuser access)? Such actions aren't limited to > attacks - but may simply be the result of "I thought it was a good idea at > the time". :-( Easily enough, have the logs record the pid, connection startup, timestamps, statement,etc. That should give everything required to track down a unique user who performed random actions.