Thread: Triggers on SELECT?

Triggers on SELECT?

From
Josh Berkus
Date:
Folks,

Pardon me if this has already been discussed and rejected for good reason.

Now that we have Statement-level triggers, is there any reason we shouldn't
have triggers on SELECT?   Such triggers could be extremely useful for
databases with tight security audit requirements.

--
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Triggers on SELECT?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Now that we have Statement-level triggers, is there any reason we shouldn't 
> have triggers on SELECT?

Plenty, although I'm too tired to recall 'em all.  The fundamental
problem with this is that it turns SELECT into an operation with
side-effects.
        regards, tom lane


Re: Triggers on SELECT?

From
Jan Wieck
Date:
Tom Lane wrote:

> Josh Berkus <josh@agliodbs.com> writes:
>> Now that we have Statement-level triggers, is there any reason we shouldn't 
>> have triggers on SELECT?
> 
> Plenty, although I'm too tired to recall 'em all.  The fundamental
> problem with this is that it turns SELECT into an operation with
> side-effects.

The other fundamental problem is the definition of SELECT. To show just 
one of the 273 possible problems:
    UPDATE sometab SET foo = 'bar'        WHERE NOT EXISTS (SELECT 1 FROM othertab WHERE o_id = s_id);

Now explain in detail what the ON SELECT trigger on othertab will be 
fired for.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #