Re: Table Updatable By Trigger Only

Hi Achilleas,

The pg_trigger_depth() function solved the issue. Thanks a lot, you made my
day.

On Tue, Jun 20, 2017 at 12:58 PM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> On 20/06/2017 14:43, Osahon Oduware wrote:
>
> Hi All,
>
> I have a trigger on a PostGIS table (say table A) that automatically
> updates another PostGIS table (say table B). Also, users connect to these
> tables (table A and B) using QGIS. However, I want the updates to table B
> to be done by the trigger only (i.e. I don't want table B to be updated
> from QGIS).
>
> I have tried revoking UPDATE permissions on table B, but this prevents the
> trigger from updating the table also as the trigger has to work with the
> permissions of the user.
>
> *Is there a way of making table B updatable by the trigger only?*
>
>
> Write an ON UPDATE trigger on table B, and inside the code check for
> pg_trigger_depth() . If this is == 1 (called by user UPDATE) then RAISE an
> exception. If it is >1 then it is called by the other trigger,
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>