I have a problem with a compound trigger not working consistently. It seems to work when running tests in SQL Plus and sometimes it works when multiple people are processing but mostly it fails to prevent data corruption.
I have two tables:
SALES_HDR
SALES_PK
PRICE
SALES_DTL
SALES_DTL_PK
SALES_PK
AMOUNT
The amount in SALES_DTL for a SALES_PK cannot be less than zero or greater than the PRICE in SALES_HDR. I use a compound trigger to get SALES_PK on each row then after the statement I look to see if the amount is out of range.
Amount is only inserted, not updated.
If I run it from SQL it works.
When I have two people process the same rows I get problems.
I saw two records, one inserted 3 seconds after the first. Yet somehow it did not raise an exception.
SALES_HDR
Price 65
SALES_DTL
Record 1 65
Record 2 -65
Record 3 -65
Record 3 should have been rejected and is if I am in SQL Plus.
When is the trigger executed? On Commit? What if two people are updating the same sales header but have not committed yet? I thought on commit the first would succeed and the second would fail.
Here is the trigger:
create or replace trigger AI_SALES_DTL
FOR INSERT ON SALES_DTL
COMPOUND TRIGGER
-- Global declaration.
n_sales_pk SALES_HDR.sales_pk%type;
n_amount number;
n_sale_price number;
e_over_refunded exception;
e_over_charged exception;
pragma exception_init(e_over_refunded , -20000);
pragma exception_init(e_over_charged , -20001);
p_type varchar2(1);
p_table_desc varchar2(100);
TYPE t_pk_tab IS TABLE OF VARCHAR2(50);
l_pk_tab t_pk_tab := t_pk_tab();
TYPE t_amount_tab is table of number;
l_amt_tab t_amount_tab := t_amount_tab();
l_amt_inserting number;
BEFORE EACH ROW IS
BEGIN
:NEW.Created_Date := sysdate;
:NEW.Created_By := NVL(v('APP_USER'), USER);
CASE
WHEN INSERTING THEN
l_pk_tab.extend;
l_amt_tab.extend;
l_pk_tab(l_pk_tab.last) := :NEW.SALES_PK;
l_amt_tab(l_amt_tab.last) := :NEW.AMOUNT;
END CASE;
if :NEW."SALES_DTL_PK" is null then
select "SALES_DTL_SEQ".nextval into :NEW."SALES_DTL_PK" from dual;
end if;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR i IN l_pk_tab.first .. l_pk_tab.last LOOP
select nvl(sum(nvl(amount,0)),0) into n_amount
from sales_dtl where sales_pk=l_pk_tab(i);
if n_amount<0 then
n_sales_pk := l_pk_tab(i);
l_amt_inserting := l_amt_tab(i);
raise e_over_refunded;
end if;
select nvl(sale_price,0) into n_sale_price
from sales_hdr where sales_pk=l_pk_tab(i);
if n_amount > n_sale_price then
n_sales_pk := l_pk_tab(i);
l_amt_inserting := l_amt_tab(i);
raise e_over_charged;
end if;
END LOOP;
l_pk_tab.delete;
EXCEPTION
when e_over_refunded then
exception_handler_PKG.log_error('AI_SALES_DTL',...
raise_application_error(-20000,'Statement failed, sales_pk '||n_sales_pk||' had too much applied to refund');
when e_over_charged then
exception_handler_PKG.log_error('AI_SALES_DTL',...
raise_application_error(-20001,'Statement failed, sales_pk '||n_sales_pk||' had too much too much charged to passenger');
END AFTER STATEMENT;
END AI_SALES_DTL;