Re: Table Updatable By Trigger Only

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Tue, 20 Jun 2017 14:58:21 +0300



    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