feature proposal - triggers by semantics - Mailing list pgsql-hackers

I have a feature request, which at one level should require little code change, 
but at another level may require more.

Since Postgres 9.3 is going to be doing some significant feature additions for 
triggers, I'd like to see some more.

As they currently exist, triggers always fire based on certain SQL syntax used, 
rather than on the semantics of what is actually going on.

I would like to see a new class of triggers that fire when particular database 
operations happen regardless of what SQL syntax was used.

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.

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.  As such, I would expect the same 
triggers to fire as would for an unqualified DELETE.

The reason I propose it be a new kind of trigger is so that then we also retain 
the ability to declare triggers that fire on DELETE and not on TRUNCATE.

Less important, but also nice at least from the ability to be less verbose, is 
that said trigger could also run when an UPDATE happens, optionally, since an 
UPDATE can be considered semantically a DELETE+INSERT.  But adding the TRUNCATE 
support is most important because it simply doesn't exist now, while UPDATE you 
can get just by adding "or update".

I suggest that the simplest way to add this feature is to just extend the 
existing syntax for defining a FOR EACH ROW so that TRUNCATE is also an option, 
besides INSERT/UPDATE/DELETE.

In that case, the semantics of the TRUNCATE statement could be altered as 
follows:  Iff "TRUNCATE foo" is invoked and foo has an "TRUNCATE FOR EACH ROW" 
trigger defined on it, then an unqualified "DELETE FROM foo" will be performed 
instead with its usual semantics.  If such a trigger is not defined on foo, then 
the old TRUNCATE semantics happen.

As such, this case of the feature can be added without breaking anything legacy.

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.

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

-- Darren Duncan