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?
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP