Advertisement
rafibatam

Creating First Trigger Postgres

Aug 2nd, 2019
122
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE SCHOOL (
  2.     ID          SERIAL      PRIMARY KEY,
  3.     First_name  VARCHAR(30) NOT NULL,
  4.     Last_name   VARCHAR(30) NOT NULL,
  5.     Jurusan     VARCHAR(20) NOT NULL
  6. );
  7.  
  8. -----------------------------------------------------------------------------------------
  9. CREATE TABLE SCHOOL_CLASS (
  10.     ID          SERIAL      PRIMARY KEY,
  11.     Absen       INTEGER     NOT NULL,
  12.     First_name  VARCHAR(30) NOT NULL,
  13.     Old_Jurusan VARCHAR(20) NOT NULL,
  14.     New_Jurusan VARCHAR(20) NOT NULL
  15. );
  16.  
  17. -----------------------------------------------------------------------------------------
  18. CREATE OR REPLACE FUNCTION Jurusan_changes()
  19.     RETURNS TRIGGER AS
  20. $BODY$
  21. BEGIN
  22.     IF NEW.Jurusan <> OLD.Jurusan THEN
  23.         INSERT INTO SCHOOL_CLASS(Absen, First_name, Old_Jurusan, New_Jurusan)
  24.         VALUES(OLD.ID, OLD.First_name, OLD.Jurusan, NEW.Jurusan);
  25.     END IF;
  26.  
  27.     RETURN NEW;
  28. END;
  29. $BODY$
  30.  
  31. LANGUAGE plpgsql;
  32.  
  33. -----------------------------------------------------------------------------------------
  34. CREATE TRIGGER Jurusan_change
  35.     BEFORE UPDATE
  36.     ON SCHOOL
  37.     FOR EACH ROW
  38.     EXECUTE PROCEDURE Jurusan_changes();
  39.  
  40. -----------------------------------------------------------------------------------------
  41. INSERT INTO SCHOOL (First_name, Last_name, Jurusan)
  42. VALUES ('Muhammad Dzaky', 'Bahrul Ilmi', 'IPA');
  43.  
  44. INSERT INTO SCHOOL (First_name, Last_name, Jurusan)
  45. VALUES ('Muhammad Rafi', 'Bahrur Rizki', 'IPA');
  46.  
  47. INSERT INTO SCHOOL (First_name, Last_name, Jurusan)
  48. VALUES ('Shafa Nurul', 'Ilyanti', 'IPA');
  49.  
  50. -----------------------------------------------------------------------------------------
  51. SELECT * FROM SCHOOL;
  52.  
  53. -----------------------------------------------------------------------------------------
  54. UPDATE SCHOOL
  55. SET Jurusan = 'Kedokteran Gigi'
  56. WHERE ID = 3;
  57.  
  58. -----------------------------------------------------------------------------------------
  59. SELECT * FROM SCHOOL_CLASS;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement