Advertisement
MagnusArias

DB2 | Trigger 2

Apr 16th, 2018
342
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 2.16 KB | None | 0 0
  1. --<ScriptOptions statementTerminator="@"/>
  2.  
  3. CREATE TABLE APN.AK49 (
  4.         ID_STUDENTA INTEGER NOT NULL,
  5.         OCENA_OLD DB2ADMIN.OCENA1 NOT NULL,
  6.         OCENA_NEW DB2ADMIN.OCENA1 NOT NULL,
  7.         KTO VARCHAR(250) NULL,
  8.         KIEDY VARCHAR(250) NULL
  9.     )@
  10.  
  11. 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))@
  12.  
  13. ALTER TABLE APN.AK49 ADD CONSTRAINT FK_AK49_STUD FOREIGN KEY
  14.     (ID_STUDENTA)
  15.     REFERENCES APN.STUDENCI
  16.     (ID_STUDENTA)@
  17.  
  18.  
  19. CREATE TRIGGER APN.OCENY_AK49
  20. AFTER UPDATE OF OCENA
  21. ON APN.OCENY
  22. REFERENCING NEW AS NEW
  23. OLD AS OLD
  24. FOR EACH ROW
  25. MODE DB2SQL NOT SECURED
  26. BEGIN
  27.     INSERT INTO APN.AK49(ID_STUDENTA, OCENA_OLD, OCENA_NEW, KTO, KIEDY)
  28.     VALUES ( OLD.ID_STUDENTA, OLD.OCENA, NEW.OCENA, USER, CURRENT_TIMESTAMP);
  29. END@
  30.  
  31.  
  32. --<ScriptOptions statementTerminator=";"/>
  33. SELECT *
  34. FROM APN.OCENY;
  35.  
  36. --<ScriptOptions statementTerminator=";"/>
  37. SELECT *
  38. FROM APN.AK49;
  39.  
  40. --<ScriptOptions statementTerminator=";"/>
  41. UPDATE APN.OCENY SET OCENA=3.5 WHERE ID_STUDENTA=2;
  42.  
  43.  
  44.  
  45.  
  46.  
  47.  
  48. -- <ScriptOptions statementTerminator="@" />
  49. CREATE OR REPLACE TRIGGER DB2ADMIN.OCENY_WSTAW_WIDOK
  50.   INSTEAD OF INSERT ON DB2ADMIN.SREDNIE
  51.   REFERENCING NEW AS N
  52.   FOR EACH ROW
  53.   MODE DB2SQL
  54.   NOT SECURED
  55.   BEGIN ATOMIC
  56.     DECLARE IDS INTEGER;
  57.    
  58.     SET IDS=(SELECT COUNT(S.ID_STUDENTA) FROM APN.STUDENCI S
  59.     WHERE S.IMIE = N.IMIE AND S.NAZWISKO=N.NAZWISKO);
  60.    
  61.      IF (IDS<1) THEN
  62.         INSERT INTO APN.STUDENCI (NAZWISKO, IMIE) VALUES (N.NAZWISKO, N.IMIE);
  63.         SET IDS = (
  64.             SELECT S.ID_STUDENTA
  65.                 FROM APN.STUDENCI S
  66.                 WHERE S.IMIE = N.IMIE AND S.NAZWISKO=N.NAZWISKO
  67.                 ORDER BY 1
  68.                 FETCH FIRST 1 ROW ONLY);
  69.      ELSE
  70.         SET IDS = (
  71.             SELECT S.ID_STUDENTA
  72.                 FROM APN.STUDENCI S
  73.                 WHERE S.IMIE = N.IMIE AND S.NAZWISKO=N.NAZWISKO
  74.                 ORDER BY 1
  75.                 FETCH FIRST 1 ROW ONLY);
  76.      END IF;
  77.      
  78.      INSERT INTO APN.OCENY(ID_STUDENTA, OCENA)
  79.      VALUES (IDS, N.SREDNIA);
  80.   END @
  81.  
  82.  
  83. SELECT * FROM DB2ADMIN.SREDNIE
  84.  SELECT * FROM APN.STUDENCI
  85.  SELECT * FROM APN.OCENY
  86.  
  87.   INSERT INTO DB2ADMIN.SREDNIE(NAZWISKO, IMIE, SREDNIA) VALUES('ZIOMEK','DOBRY', 4.0)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement