Database_Faculty_Portal/history.sql at master · sid230798/Database_Faculty_Portal · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
-- ****************************************************************************

CREATE OR REPLACE FUNCTION T_hod_delete() RETURNS trigger AS
$$
DECLARE
    var RECORD;
BEGIN
    INSERT INTO HOD_History(dept_id, faculty_id, start_date, end_date, rem_date) VALUES(OLD.dept_id, OLD.faculty_id, OLD.start_date, OLD.end_date, now());
    SELECT into var * FROM Positions WHERE LOWER(name) = 'faculty';
    IF var IS NOT NULL THEN
        UPDATE Faculty_Position SET position_id = var.id WHERE faculty_id = OLD.faculty_id;
    END IF;

    DELETE FROM HOD WHERE dept_id = OLD.dept_id;

    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_delete_hod
AFTER DELETE ON HOD
FOR EACH ROW
EXECUTE PROCEDURE T_hod_delete();


-- ****************************************************************************


-- ALLOW UPDATE TO DATES ONLY
CREATE OR REPLACE FUNCTION T_hod_update() RETURNS trigger AS
$$
DECLARE
    var RECORD;
    var2 RECORD;
BEGIN
    -- INSERT INTO HOD_History(dept_id, faculty_id, start_date, end_date, rem_date) VALUES(OLD.dept_id, OLD.faculty_id, OLD.start_date, OLD.end_date, now());

    INSERT INTO HOD_History(dept_id, faculty_id, start_date, end_date, rem_date) VALUES(OLD.dept_id, OLD.faculty_id, OLD.start_date, OLD.end_date, now());

    IF NEW.faculty_id <> OLD.faculty_id THEN
        SELECT into var * FROM Positions WHERE LOWER(name) = 'faculty';
        SELECT into var2 * FROM Positions WHERE LOWER(name) = 'hod';
        IF var IS NOT NULL THEN
            UPDATE Faculty_Position SET position_id = var.id WHERE faculty_id = OLD.faculty_id;
            UPDATE Faculty_Position SET position_id = var2.id WHERE faculty_id = NEW.faculty_id;
        END IF;
    END IF;

    RETURN NEW;
END
$$ LANGUAGE plpgsql;


CREATE TRIGGER on_update_hod
AFTER UPDATE ON HOD
FOR EACH ROW
EXECUTE PROCEDURE T_hod_update();


-- ****************************************************************************


CREATE OR REPLACE FUNCTION T_ccf_delete() RETURNS trigger AS
$$
DECLARE
    var RECORD;
BEGIN
    INSERT INTO CCF_History(Position_id, faculty_id, start_date, end_date, rem_date) VALUES(OLD.position_id, OLD.faculty_id, OLD.start_date, OLD.end_date, now());
    SELECT into var * FROM Positions WHERE LOWER(name) = 'faculty';

    IF var IS NOT NULL THEN
        UPDATE Faculty_Position SET position_id = var.id WHERE faculty_id = OLD.faculty_id;
    END IF;

    DELETE FROM CCF WHERE Position_id = OLD.Position_id;

    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_delete_ccf
AFTER DELETE ON CCF
FOR EACH ROW
EXECUTE PROCEDURE T_ccf_delete();


-- ****************************************************************************


-- ALLOW UPDATE TO DATES ONLY
CREATE OR REPLACE FUNCTION T_ccf_update() RETURNS trigger AS
$$
DECLARE
    var RECORD;
BEGIN
    -- INSERT INTO CCF_History(POR_id, faculty_id, start_date, end_date, rem_date) VALUES(OLD.POR_id, OLD.faculty_id, OLD.start_date, OLD.end_date, now());
    INSERT INTO CCF_History(Position_id, faculty_id, start_date, end_date, rem_date) VALUES(OLD.position_id, OLD.faculty_id, OLD.start_date, OLD.end_date, now());

    IF NEW.faculty_id <> OLD.faculty_id THEN
        SELECT into var * FROM Positions WHERE LOWER(name) = 'faculty';
        IF var IS NOT NULL THEN
            UPDATE Faculty_Position SET position_id = var.id WHERE faculty_id = OLD.faculty_id;
            UPDATE Faculty_Position SET position_id = OLD.position_id WHERE faculty_id = NEW.faculty_id;
        END IF;
    END IF;

    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_update_ccf
AFTER UPDATE ON CCF
FOR EACH ROW
EXECUTE PROCEDURE T_ccf_update();