ITQuants blog

Oracle: implementation of a BEFORE COMMIT trigger

May 21

Written by:
5/21/2014 11:06 PM  RssIcon

For remember, on SQL databases, triggers permit to change the behaviour of insertions, modifications or deletions made by a program, by adding SQL notification callbacks. This permits to make complex check on field values, or to copy some record in audit tables for example.

On Oracle, a trigger can be implemented after or before the action. On before triggers, values can be changed by the notification callback. On after, we are sure that any modification done by the program or on before triggers is taken into account in the :new record.

 Sometimes, complex check should be made on several tables, during a transactional modification for example. In such a case, a BEFORE COMMIT trigger is mainly required. Even if it is more recommanded to do such a check in the client program, on third party databases, there is no choice, additional checks can be done only on the database server. Unfortunately, Oracle does not permit such a trigger implementation directly, a workaround has to be developped. This is the purpose of this post.

As sample, I will take a check that I've had to develop on Sophis databases, in order to forbid some cashflows insertions on swaps. The development of such a trigger relies on:

- creation of AFTER triggers on both tables on which checks have to be done,

- creation of a MATERIALIZED view, in order to use some specific parameters...,

- creation a trigger on this view, which will implement the expected code on BEFORE COMMIT trigger.

The MATERIALIZED VIEW will be created with the REFRESH FAST ON COMMIT property. This permits to trigger some action when a modification on a table that impacts this view is done. Such a property can be set only on "simple" views. By simple, it means that ther should be no complex query on the definition. If a the query is complex, intermediate tables with temporary records can be used.

Script for such an intermediate table:

CREATE TABLE MYBANK_CHECK_INVALID_BS(sicovam NUMBER(10));
  
CREATE UNIQUE INDEX MYBANK_INVALID_PK ON MYBANK_CHECK_INVALID_BS(SICOVAM);
  
ALTER TABLE MYBANK_CHECK_INVALID_BS
  ADD CONSTRAINT MYBANK_INVALID_BS_PK PRIMARY KEY(SICOVAM);

 

Trigger on the TITRES table:

CREATE OR REPLACE TRIGGER MYBANK_BS_CHECK1
AFTER INSERT OR UPDATE ON TITRES
FOR EACH ROW
DECLARE
  v_max_received DATE;
  v_max_paid DATE;
BEGIN
  IF :new.TYPE='S' AND :new.MODELE='Basket Swap' AND :new.DUREE1=5 AND :new.DUREE2<>5 THEN
    SELECT num_to_date(max(fin)) INTO v_max_paid FROM FLUXJAMBE f WHERE f.NATURE='P' and f.SICOVAM=:new.SICOVAM;
    SELECT num_to_date(max(paye)) INTO v_max_received FROM FLUXJAMBE f WHERE f.NATURE='R' AND f.SICOVAM=:new.SICOVAM;
    IF v_max_paid IS NULL AND v_max_received IS NULL THEN
        MERGE INTO MYBANK_INVALID_BS i USING(SELECT :new.SICOVAM AS SICOVAM FROM dual) s
        ON (i.SICOVAM=s.SICOVAM)
        WHEN NOT MATCHED THEN
              INSERT (i.SICOVAM) VALUES(:new.SICOVAM);
      END IF;
  END IF;
END MYBANK_BS_CHECK1;

 

Script for the materialized view:

CREATE MATERIALIZED VIEW LOG ON MYBANK_CHECK_INVALID_BS;
  
CREATE MATERIALIZED VIEW MYBANK_BS_CHECK_VW
REFRESH FAST ON COMMIT AS
  SELECT SICOVAM FROM MYBANK_BS_CHECK_VW;

 

Script for the final and equivalent BEFORE COMMIT trigger:

CREATE OR REPLACE TRIGGER MYBANK_BS_CHECK2
BEFORE INSERT OR UPDATE ON MYBANK_BS_CHECK_VW
FOR EACH ROW
BEGIN
    RAISE APPLICATION_ERROR(-20000,'Invalid Basket Swap, final<>final and no custom schedule');
END MYBANK_BS_CHECK2;

 

Supplementary check on FLUXJAMBE to delete cases when the financial instrument does not have custom cashflows:

CREATE OR REPLACE TRIGGER MYBANK_BS_CHECK3 
AFTER INSERT OR UPDATE ON FLUXJAMBE 
FOR EACH ROW 
BEGIN
  DELETE MYBANK_CHECK_INVALID_BS WHERE SICOVAM=:new.SICOVAM; 
END MYBANK_BS_CHECK3

 

As conclusion, on Sophis databases, it is better to use the Sophis toolkit and to add a derivated class of CSRInstrumentAction :-)

Tags:
Categories: Sophis, Oracle

Search blog