Re: feature proposal - triggers by semantics

On 15 November 2012 05:25, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> On 11/15/2012 09:48 AM, Craig Ringer wrote:
>>
>> If you want to prevent TRUNCATE, deny the privilege or add a trigger
>> that aborts the command.
>
> You can abort the transaction but not skip action as currently it is only
> possible to skip in ROW level triggers.
>
> So I'd modify this request to allow BEFORE EACH STATEMENT triggers
> to also be able to silently skip current action like BEFORE EACH ROW
> triggers can.

+1

> Then this request would simply be satisfied by a simple trigger which
> rewrites TRUNCATE into DELETE .

However, we don't need to do that to make this work.

Just create a BEFORE EACH STATEMENT trigger on TRUNCATE that issues a
DELETE from table.
Then have a EACH ROW trigger on DELETE.

That way each row gets logged.

The TRUNCATE still occurs, but semantically does nothing, though
physically deletes the space used by the already deleted rows.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services