sqloracle-databasedatatables

Creating triggers with table referencing

I'm working on project with triggers that reference multiple tables.

Tables structure:

CREATE TABLE TabelaA
(
    id NUMBER NOT NULL,
    naziv VARCHAR2(20),
    datum DATE,
    cijeliBroj NUMBER,
    realniBroj NUMBER,

    CONSTRAINT PK_TabelaA PRIMARY KEY(id),
    CONSTRAINT realniBrojOgranicenjeA CHECK(realniBroj >5),
    CONSTRAINT cijeliBrojOgranicenjeA CHECK(cijeliBroj NOT BETWEEN 5 AND 15) 
);

CREATE TABLE TabelaB
(
    id NUMBER,
    naziv VARCHAR2(20),
    datum DATE,
    cijeliBroj NUMBER,
    realniBroj NUMBER,
    FKTabelaA NUMBER,

    CONSTRAINT PK_TabelaB PRIMARY KEY(id),
    CONSTRAINT FK_TabelaA 
        FOREIGN KEY(FKTabelaA) REFERENCES TabelaA(id),
    CONSTRAINT cijeliBrojOgranicenjeB UNIQUE(cijeliBroj)
);

CREATE TABLE TabelaC
(
    id NUMBER,
    naziv VARCHAR2(20) NOT NULL,
    datum DATE,
    cijeliBroj NUMBER NOT NULL,
    realniBroj NUMBER,
    FKTabelaB NUMBER,

    CONSTRAINT PK_TabelaC PRIMARY KEY(id),
    CONSTRAINT FkCnst 
        FOREIGN KEY(FKTabelaB) REFERENCES TabelaB(id)
);

enter image description here

fill tables

Create a trigger t1 that, after adding each new row to the TableB table, increases the value of its real number in the corresponding row of the TableA table by 25% if the integer value of the new row of the TableB table is less than 50, and otherwise decreases the value of its real number by 25%.

Create a trigger t2 that, before adding or changing each row in the TableC table, adds a new row to the TableB table. The new row should reference the same row of the TableA table as the corresponding row of the TableB table to which the given row of the TableC table refers (in case of change, use the new value). Set today's date for the new row, double the maximum integer value from the TableB table for the integer, and set the next value of the counterB sequence used for the initial entry of rows 1-5 into the TableB table for the id. Set the other column values ​​to null. If you did not create the counterB sequence before entering the rows, create it later with the appropriate settings. For students using SQLTools, it is necessary that the sequence counterB has a minimum value and starts from the value 0, and for students using Oracle Live, it is necessary that the sequence counterB starts from the value 1.

Create an empty backup table TableABekap. The TableABekap table is identical to the TableA table, except that it has additional columns integerB (of type INTEGER) and sequence (of type INTEGER).

Create a trigger t3 that populates the backup table TableABekap. After adding any new row to the TableB table, the referenced row of the TableA table is saved to the backup table. In this process, the integerB column is filled with the integer from the entered row of the TableB table. If a row with the same primary key has already been entered into the TableABekap table, it is only necessary to increase the integerB column by the value of the integerB column of the entered row of the TableB table. In the sequence column it is necessary to enter the next value of the seq sequence that was created before the trigger itself. This sequence takes values ​​between 1 and 10 in circular order. For all students it is necessary that the sequence seq has minimum value and starts from value 1 (no value 0 should be used for this sequence).

CREATE TRIGGER t1
AFTER INSERT 
ON TabelaB
FOR EACH ROW
BEGIN
    IF(:NEW.cijeliBroj < 50) 
    THEN 
        UPDATE TabelaA
        SET realniBroj = realniBroj * 1.25
        WHERE id = :NEW.FKTabelaA;
    ELSE 
        UPDATE TabelaA
        SET realniBroj = realniBroj * 0.75
        WHERE id = :NEW.FKTabelaA;
    END IF;
END;
/

CREATE SEQUENCE brojacB
MINVALUE 0
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE TRIGGER t2
BEFORE INSERT OR UPDATE OR DELETE 
ON TabelaC
FOR EACH ROW
    DECLARE maxCijeliBroj NUMBER;
BEGIN
    SELECT MAX(cijeliBroj) 
    INTO maxCijeliBroj 
    FROM TabelaB;

    maxCijeliBroj := maxCijeliBroj * 2;

    INSERT INTO TabelaB
    VALUES (brojacB.NEXTVAL, NULL, SYSDATE, maxCijeliBroj, NULL, :NEW.FKTabelaB);
END;
/

CREATE TABLE TabelaABekap 
AS 
  SELECT * FROM TabelaA;

ALTER TABLE TabelaABekap
ADD (cijeliBrojB NUMBER, sekvenca NUMBER);

CREATE SEQUENCE seq
MINVALUE 1
MAXVALUE 10
START WITH 1
INCREMENT BY 1
CYCLE
CACHE 9;

CREATE TRIGGER t3
AFTER INSERT 
ON TabelaB
FOR EACH ROW
BEGIN
    INSERT INTO TabelaABekap (id, naziv, datum, cijeliBroj, realniBroj)
        SELECT id, naziv, datum, cijeliBroj, realniBroj
        FROM TabelaA
        WHERE id = :NEW.FKTabelaA;

    DECLARE brojac INTEGER;
BEGIN
    SELECT COUNT(*) 
    INTO brojac
    FROM TabelaABekap
    WHERE id = :NEW.FKTabelaA;

    IF brojac > 0 
    THEN
        UPDATE TabelaABekap
        SET cijeliBrojB = cijeliBrojB + :NEW.cijeliBroj
        WHERE id = :NEW.FKTabelaA;
    ELSE 
        INSERT INTO TabelaABekap(id, naziv, datum, cijeliBroj, realniBroj, cijeliBrojB)
            SELECT id, naziv, datum, cijeliBroj, realniBroj, :NEW.CijeliBroj
            FROM TabelaA
            WHERE id = :NEW.FKTabelaA;
    END IF;
END;

UPDATE TabelaABekap
SET sekvenca = seq.NEXTVAL;

END;
/

TEST CASES: after calling these insert commands:

INSERT INTO TabelaB (id, naziv, datum, cijeliBroj, realniBroj, FkTabelaA) VALUES (brojacB.nextval, null,null, 2, null, 1);
INSERT INTO TabelaB (id, naziv, datum, cijeliBroj, realniBroj, FkTabelaA) VALUES (brojacB.nextval, null,null, 4, null, 2);
INSERT INTO TabelaB (id, naziv, datum, cijeliBroj, realniBroj, FkTabelaA) VALUES (brojacB.nextval, null, null, 8, null, 1);
INSERT INTO TabelaC (id, naziv, datum, cijeliBroj, realniBroj, FkTabelaB) VALUES (4, 'NO', null, 5, null, 3);
INSERT INTO TabelaC (id, naziv, datum, cijeliBroj, realniBroj, FkTabelaB) VALUES (5, 'YES', null, 7, null,3);
INSERT INTO TabelaC (id, naziv, datum, cijeliBroj, realniBroj, FkTabelaB) VALUES (6, 'NO', null, 9, null, 2);
UPDATE TabelaC SET cijeliBroj = 10 WHERE id = 2;
DELETE FROM TabelaB WHERE id NOT IN (SELECT FkTabelaB FROM TabelaC);
DELETE FROM TabelaA WHERE id IN (3, 4, 6);

After these inserts test results should be:

SELECT SUM(id*3 + cijeliBrojB*3) FROM TabelaABekap;--Result: 2031
SELECT SUM(id*3 + cijeliBroj*3) FROM TabelaC;--Result: 420
SELECT SUM(MOD(id,10)*3) FROM TabelaB;--Result: 30 
SELECT SUM(id + realniBroj)*10 FROM TabelaA;--Result: 264

I cannot execute insert commands. I get this error and similar error that are related to referencing multiple tables, parent-child relation and constraints.

1 1 ORA-02291: integrity constraint (USER.FK_TABELAA) violated - parent key not found

Database used: Oracle

Is there any way to fix this? I would be very thankful if you could help with this.

Solution

  • Your first insert to TabelaB uses a value of 1 for FkTabelaA.
    The definition of TabelaB mentions that FkTabelaA must correspond to an existing entry in TabelaA.
    But entry 1 of TabelaA is not mentionned in the SQL you posted directly:
    so if you played only that SQL, that explains why you can't insert into B without the corresponding A having been inserted before.

    However, you posted a "fill table" link to a pastebin containing initial entries for tables A, B and C:
    and for sure, if you play the pastebin's INSERTs between your first block of code (the CREATE TABLEs) and your second block (the CREATE TRIGGERs),
    then your post-trigger INSERTs will work.

    Then you'll be able to tune your CREATE TRIGGERs to your homework's goal (but that's another story, for now you just asked for help into unblocking the initialization of your project).

    … OK, a first hint to your personal work part:
    as the pastebin INSERTs will have inserted entries 1 to 5 into B,
    you may want to start your brojacB sequence at 6 to avoid the sequence-based INSERTs (those after the triggers) conflict with the hardcoded ones (those from the pastebin).