DB2 | Trigger 2

--<ScriptOptions statementTerminator="@"/>

CREATE TABLE APN.AK49 (
        ID_STUDENTA INTEGER NOT NULL,
        OCENA_OLD DB2ADMIN.OCENA1 NOT NULL,
        OCENA_NEW DB2ADMIN.OCENA1 NOT NULL,
        KTO VARCHAR(250) NULL,
        KIEDY VARCHAR(250) NULL
    )@

ALTER TABLE APN.AK49 ADD CONSTRAINT SPR_OCENY_II CHECK (CAST(OCENA_OLD AS DECIMAL(5,1)) IN (2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0))@

ALTER TABLE APN.AK49 ADD CONSTRAINT FK_AK49_STUD FOREIGN KEY
    (ID_STUDENTA)
    REFERENCES APN.STUDENCI
    (ID_STUDENTA)@


CREATE TRIGGER APN.OCENY_AK49
AFTER UPDATE OF OCENA
ON APN.OCENY
REFERENCING NEW AS NEW
OLD AS OLD
FOR EACH ROW
MODE DB2SQL NOT SECURED
BEGIN
    INSERT INTO APN.AK49(ID_STUDENTA, OCENA_OLD, OCENA_NEW, KTO, KIEDY)
    VALUES ( OLD.ID_STUDENTA, OLD.OCENA, NEW.OCENA, USER, CURRENT_TIMESTAMP);
END@


--<ScriptOptions statementTerminator=";"/>
SELECT *
FROM APN.OCENY;

--<ScriptOptions statementTerminator=";"/>
SELECT *
FROM APN.AK49;

--<ScriptOptions statementTerminator=";"/>
UPDATE APN.OCENY SET OCENA=3.5 WHERE ID_STUDENTA=2;






-- <ScriptOptions statementTerminator="@" />
CREATE OR REPLACE TRIGGER DB2ADMIN.OCENY_WSTAW_WIDOK
  INSTEAD OF INSERT ON DB2ADMIN.SREDNIE
  REFERENCING NEW AS N
  FOR EACH ROW
  MODE DB2SQL
  NOT SECURED
  BEGIN ATOMIC
    DECLARE IDS INTEGER;

    SET IDS=(SELECT COUNT(S.ID_STUDENTA) FROM APN.STUDENCI S
    WHERE S.IMIE = N.IMIE AND S.NAZWISKO=N.NAZWISKO);

     IF (IDS<1) THEN
        INSERT INTO APN.STUDENCI (NAZWISKO, IMIE) VALUES (N.NAZWISKO, N.IMIE);
        SET IDS = (
            SELECT S.ID_STUDENTA
                FROM APN.STUDENCI S
                WHERE S.IMIE = N.IMIE AND S.NAZWISKO=N.NAZWISKO
                ORDER BY 1
                FETCH FIRST 1 ROW ONLY);
     ELSE
        SET IDS = (
            SELECT S.ID_STUDENTA
                FROM APN.STUDENCI S
                WHERE S.IMIE = N.IMIE AND S.NAZWISKO=N.NAZWISKO
                ORDER BY 1
                FETCH FIRST 1 ROW ONLY);
     END IF;

     INSERT INTO APN.OCENY(ID_STUDENTA, OCENA)
     VALUES (IDS, N.SREDNIA);
  END @


SELECT * FROM DB2ADMIN.SREDNIE
 SELECT * FROM APN.STUDENCI
 SELECT * FROM APN.OCENY

  INSERT INTO DB2ADMIN.SREDNIE(NAZWISKO, IMIE, SREDNIA) VALUES('ZIOMEK','DOBRY', 4.0)