Oracle: implementation of a BEFORE COMMIT trigger
May
21
Written by:
5/21/2014 11:06 PM
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 :-)