2

I have a table in an Oracle 18c/10.7.1 geodatabase. The table is versioned with the option to move edits to base.

As far as I can tell, it's not possible to prevent ArcGIS Desktop users from deleting records, while still allowing inserts and updates, if the table is registered as versioned.

Question:

Is there a way to prevent deletes in a versioned table, while still allowing inserts and updates, by implementing something directly in the DB?

6
  • 1
    Just last month I created a trigger to cause INSERTs to fail (all INSERTs, by any user), but the feature class wasn't versioned. You'd have to prevent INSERTs to the Deletes table that didn't involve an insert to the Adds table. That could mess up the versioning model in a number of ways, and it might not even be possible. BTW: substitute "would likely not" with "would not even on a cold day in hell".
    – Vince
    Aug 17 '20 at 0:25
  • It's possible with PostgreSQL so I assume Oracle isn't much different, revoke the DELETE permission resources.arcgis.com/en/help/main/10.2/index.html#//…, assuming you still want users to add/modify just not delete existing rows, if you don't want the user to be able to modify the database then only grant SELECT permission, this can be done for individual users or group roles. You can do this in ArcCatalog by right clicking on the feature dataset you want to protect then select Manage::Privileges then untick the Delete box; you must use the super user account for this. Aug 17 '20 at 3:45
  • @MichaelStimson Yeah, I'm familiar with the process for unversioned data. But for versioned data, if I recall, it's either all (INSERT/UPDATE/DELETE) or nothing. At least in Oracle it's that way.
    – User1973
    Aug 17 '20 at 6:13
  • You're right, even though the 3 boxes exist if you untick one the other two untick, if you select one all three become selected. Your choices are no access, select access and full access. You might need to post this question on dba.stackexchange.com as I'm fairly confident permission to remove rows can be blocked on a database level, I don't know enough about the back end of enterprise databases to say how. This post may help community.oracle.com/thread/1073623?start=15&tstart=0 but I'm not sure it would place nice with ArcGIS. Aug 17 '20 at 6:36
  • @Vince Is there a reason why REVOKE DELETE can't be performed on the base feature class? (registered with as versioned with the option to move edits to base)
    – User1973
    Aug 23 '20 at 2:43
1

You can try to put the trigger on D table of the feature class, however this logically should prevent the update as well.

1
  • Great, hope it did the trick
    – S_K
    Aug 31 '20 at 11:29
1

This trigger on the DELETES table seems to work.

CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE 
    v_upd_row NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_upd_row 
    FROM A202574
    WHERE objectid = :new.sde_deletes_row_id
    AND sde_state_id = :new.deleted_at;

    IF v_upd_row = 0 THEN
         raise_application_error(-20001, 'Deleting has been disabled via a custom trigger.');
    END IF;
END ;

Edit:

For bonus points, I only prevented deletes if the record was created before a certain date (last Monday at 12:00 AM).

I did this because my data is synced to an external system on weekends. I want to prevent users from deleting records that have already been synced to the external system.

create or replace trigger v202577_delete_custom
    before insert
    on d202577
    for each row
declare
    v_upd_row       number;
    v_cre_date_row   number;
begin
    select
        count (1)
        into v_upd_row
        from a202577
     where
        objectid = :new.sde_deletes_row_id
        and sde_state_id = :new.deleted_at;
 
    select
        count (1)
        into v_cre_date_row
    from
        a_test_fc a
     where
        a.objectid = :new.sde_deletes_row_id
        and a.created_date < trunc(next_day (sysdate - 6, 'monday'));
 
    if v_upd_row = 0 and v_cre_date_row > 0
    then
        raise_application_error ( -20001, 'Deleting has been disabled via a custom trigger.');
    end if;
end;
2
  • This should also prevent UPDATE (Edits), since they also generate DELETE rows.
    – Vince
    Sep 2 '20 at 3:19
  • @Vince For the UPDATE scenario, the trigger looks for related records in the ADDS table. It only prevents deletes if it doesn't find a corresponding record in the ADDS table. When I tested it, it did not prevent updates. Yes, there are probably scenarios where the logic will fail. But so far, so good.
    – User1973
    Sep 2 '20 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.