On 11/15/2012 03:44 PM, Darren Duncan wrote:
> As they currently exist, triggers always fire based on certain SQL
> syntax used, rather than on the semantics of what is actually going on.
>
That's not quite right. COPY fires INSERT triggers, despite never using
an explicit INSERT statement. There are probably other examples.
> As a simple example, I'd like to be able to define a trigger like
> "AFTER DELETE ON foo FOR EACH ROW" and have that trigger be invoked
> not only by a DELETE on foo but also by a TRUNCATE on foo. So I would
> like to do some auditing action when a row of foo is deleted, no
> matter how it happens.
TRUNCATE triggers already exist.
If you wanted you could have a BEFORE TRUNCATE trigger SELECT each row
and invoke a function on each row, same as a FOR EACH ROW .. DELETE
trigger. So you can already achieve what you want to do with the
currently available features.
> The reason this particular example in particular is important is that
> TRUNCATE is documented as a data-manipulation action semantically
> equivalent to an unqualified DELETE in its effects, primarily.
I'd say we should just change the docs to note that "TRUNCATE does not
act on each row individually, so it does not fire DELETE triggers. There
is a TRUNCATE trigger you can use to monitor and control TRUNCATE
behaviour."
>
> So, I'm partly proposing a specific narrow new feature, "TRUNCATE FOR
> EACH ROW", but I'm also proposing the ability to generally define
> triggers based not on the syntax used but the actual action requested.
That already exists. Maybe you're using some old version?
regress=> \h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } {
event [ OR ... ] } ON table_name
...
where event can be one of:
INSERT UPDATE [ OF column_name [, ... ] ] DELETE TRUNCATE
>
> A tangential feature request is to provide a runtime config option
> that can cause TRUNCATE to always behave as unqualified DELETE FROM
> regardless of any triggers, as if it were just a syntactic shorthand.
Please, no!
If you want to prevent TRUNCATE, deny the privilege or add a trigger
that aborts the command.
TRUNCATE should do what it says, not magically be changed to do
something else by a GUC.
> Or alternately/also provide extra syntax to TRUNCATE itself where one
> can specify which behavior to have, and both options can be given
> explicitly to override any config option.
What advantage would that have over using DELETE when you want DELETE,
and TRUNCATE when you want TRUNCATE?
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
