Knowledge base

Oracle trigger

The examples given below are based on a minimum Oracle version of 7.2. Use SQL-Plus to create the tables and triggers.

Everything put between <> should be converted to the name relevant in your application.

Creating HEAD and TRB

CREATE TABLE HEAD (
                    HEADER NUMBER NOT NULL,
                    MAXI NUMBER NOT NULL,
                    PRIMARY KEY (HEADER));

INSERT INTO HEAD VALUES(1,100);

CREATE TABLE TRB (
                    IND NUMBER NOT NULL,
                    TABL VARCHAR2(50) NOT NULL,
                    EVENT VARCHAR2(1) NOT NULL,
                    SK1 VARCHAR2(50) NULL,
                    SK2 VARCHAR2(50) NULL,
                    SK3 VARCHAR2(50) NULL,
                    SK4 VARCHAR2(50) NULL,
                    SK5 VARCHAR2(50) NULL,
                    NK1 NUMBER NULL,
                    NK2 NUMBER NULL,
                    NK3 NUMBER NULL,
                    NK4 NUMBER NULL,
                    NK5 NUMBER NULL,
                    FLD VARCHAR2(50) NULL,
                    PRIMARY KEY (IND));

INSERT INTO TRB (IND,TABL,EVENT,SK1) VALUES (1,'Promes','1','Promes');

Remark: The value 100 in the MAXI field of HEAD should be the real maximum of your circular buffer. Most likely this is even more then 10000. The insert in TRB is needed because the trigger would fail to run the first time.

Creating an insert trigger

CREATE OR REPLACE TRIGGER <Name of trigger>
            after INSERT on <Table name>
for each row
DECLARE
    realhead NUMBER(5);
    in_table NUMBER(5);
    maximum NUMBER(5);
BEGIN
    SELECT HEADER,MAXI INTO realhead,maximum FROM HEAD;
    SELECT MAX(IND) INTO in_table FROM TRB;
    realhead:=realhead+1;
    IF realhead>maximum THEN 
        realhead:=0;
    END IF; 
    IF in_table<realhead THEN
        INSERT INTO TRB (IND,TABL,EVENT,SK1,SK2,SK3,SK4,SK5,NK1,NK2,NK3,NK4,NK5) values
        (realhead,'<Table name>','1',:new.<Alpha Field 1>,:new.<Alpha Field 2>,NULL,NULL,NULL,:new.<Numeric field 1>,NULL,NULL,NULL,NULL);
        UPDATE HEAD SET HEADER=realhead;
    ELSE
        UPDATE TRB SET IND=realhead,TABL='<Table name>',EVENT='1',SK1=:new.<Alpha Field 1>,SK2=:new.<Alpha Field 2>,SK3=NULL,SK4=NULL,SK5=NULL,
        NK1=:new.<Numeric Field 1>,NK2=NULL,NK3=NULL,NK4=NULL,NK5=NULL WHERE IND=realhead;
        UPDATE HEAD SET HEADER=realhead;
    END IF;
END;
/

Creating an update trigger

CREATE OR REPLACE TRIGGER <Name of trigger>
            after UPDATE of <Field 1>,<Field 2>,<...> on <Table name>
for each row
DECLARE
    realhead NUMBER(5);
    in_table NUMBER(5);
    maximum NUMBER(5);
BEGIN
    SELECT HEADER,MAXI INTO realhead,maximum FROM HEAD;
    SELECT MAX(IND) INTO in_table FROM TRB;
    realhead:=realhead+1;
    IF realhead>maximum THEN 
        realhead:=0;
    END IF; 
    IF in_table<realhead THEN
        INSERT INTO TRB (IND,TABL,EVENT,SK1,SK2,SK3,SK4,SK5,NK1,NK2,NK3,NK4,NK5) values
        (realhead,'<Table name>','2',:new.<Alpha Field 1>,:new.<Alpha Field 2>,NULL,NULL,NULL,:new.<Numeric field 1>,NULL,NULL,NULL,NULL);
        UPDATE HEAD SET HEADER=realhead;
    ELSE
        UPDATE TRB SET IND=realhead,TABL='<Table name>',EVENT='2',SK1=:new.<Alpha Field 1>,SK2=:new.<Alpha Field 2>,SK3=NULL,SK4=NULL,SK5=NULL,
        NK1=:new.<Numeric Field 1>,NK2=NULL,NK3=NULL,NK4=NULL,NK5=NULL WHERE IND=realhead;
        UPDATE HEAD SET HEADER=realhead;
    END IF;
END;
/

Creating a delete trigger

CREATE OR REPLACE TRIGGER <Name of trigger>
            after DELETE on <Table name>
for each row
DECLARE
    realhead NUMBER(5);
    in_table NUMBER(5);
    maximum NUMBER(5);
BEGIN
    SELECT HEADER,MAXI INTO realhead,maximum FROM HEAD;
    SELECT MAX(IND) INTO in_table FROM TRB;
    realhead:=realhead+1;
    IF realhead>maximum THEN 
        realhead:=0;
    END IF; 
    IF in_table<realhead THEN
        INSERT INTO TRB (IND,TABL,EVENT,SK1,SK2,SK3,SK4,SK5,NK1,NK2,NK3,NK4,NK5) values
        (realhead,'<Table name>','3',:old.<Alpha Field 1>,:old.<Alpha Field 2>,NULL,NULL,NULL,:old.<Numeric field 1>,NULL,NULL,NULL,NULL);
        UPDATE HEAD SET HEADER=realhead;
    ELSE
        UPDATE TRB SET IND=realhead,TABL='<Table name>',EVENT='3',SK1=:old.<Alpha Field 1>,SK2=:old.<Alpha Field 2>,SK3=NULL,SK4=NULL,SK5=NULL,
        NK1=:old.<Numeric Field 1>,NK2=NULL,NK3=NULL,NK4=NULL,NK5=NULL WHERE IND=realhead;
        UPDATE HEAD SET HEADER=realhead;
    END IF;
END;
/