Thread: Triggers on TRUNCATE?

Triggers on TRUNCATE?

From
Daniel Baldoni
Date:
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)
 


Re: Triggers on TRUNCATE?

From
Tom Lane
Date:
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


Re: Triggers on TRUNCATE?

From
Hannu Krosing
Date:
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



Re: Triggers on TRUNCATE?

From
Tom Lane
Date:
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


Re: Triggers on TRUNCATE?

From
Daniel Baldoni
Date:
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)
 


Re: Triggers on TRUNCATE?

From
Rod Taylor
Date:
> 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.