TransWikia.com

Prevent deletes in a versioned table at the database level

Geographic Information Systems Asked on November 18, 2020

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?

2 Answers

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;

Correct answer by User1973 on November 18, 2020

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

Answered by S_K on November 18, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP