Hello to all
A general question:
having various source to populate-insert rows in tables, for example (pl / sql, dev tools etc etc)
I would like to ask the community if having to insert check on value, some controls, during DML (Ins, Upd, del) the approach by before trigger
was suitable and which pros and cons must be paid. for example (PRAGMA AUTONOMOUS_TRANSACTION;)
I done
for UPDATE if not exists same table this following
create or replace trigger BEF_UPD_time
BEFORE UPDATE OF onecol on MyTable FOR EACH ROW
when ( TO_CHAR( trunc(new.onecol 'MI') ,'HH24MI' ) <> TO_CHAR( trunc(old.onecol , 'MI') ,'HH24MI' )
and (new.twocol = 'FK' or new.twocol = '11') )
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
declare qty integer := 0;
begin
select count(*) into qty from MyTable B
WHERE one_condition
AND TO_CHAR( trunc( :new.onecol , 'MI') ,'HH24MI')
between
TO_CHAR( trunc(B.onecol, 'MI'),'HH24MI') and
TO_CHAR( trunc(B.onecol, 'MI') + NUMTODSINTERVAL (B.minute_calc, 'MINUTE') ,'HH24MI');
if qty > 0 then
raise_application_error(-20634, 'This time is duplicate.');
-- dbms_output.put('This time s.');
end if;
:new.4col := trunc(:new.onecol, 'MI');
end;
end;
Thanks in advance for your worthy suggestions and time