-
Notifications
You must be signed in to change notification settings - Fork 0
/
HW4.ddl4
96 lines (92 loc) · 1.58 KB
/
HW4.ddl4
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
drop view sim_project_emp ;
drop view sim_manager;
create view sim_project_emp as
SELECT PERSON_ID, NAME, SSNUM, GENDER, BIRTH_DATE, ADDRESS, CITY, STATE,
ZIP, TYPE, HIRE_DATE, SALARY, STATUS, TITLE, RATING, SIM_DEPT_DEPT_ID
FROM sim_person
WHERE TYPE = 'Project Employee' ;
create or replace TRIGGER project_emp_trigger
INSTEAD OF insert ON sim_project_emp
FOR EACH ROW
BEGIN
insert into sim_person(
PERSON_ID,
NAME,
SSNUM,
GENDER,
BIRTH_DATE,
ADDRESS,
CITY,
STATE,
ZIP,
TYPE,
HIRE_DATE,
SALARY,
STATUS,
TITLE,
RATING,
SIM_DEPT_DEPT_ID)
VALUES (
:new.PERSON_ID,
:new.NAME,
:new.SSNUM,
:new.GENDER,
:new.BIRTH_DATE,
:new.ADDRESS,
:new.CITY,
:new.STATE,
:new.ZIP,
'Project Employee',
:new.HIRE_DATE,
:new.SALARY,
:new.STATUS,
:new.TITLE,
:new.RATING,
:new.SIM_DEPT_DEPT_ID) ;
END;
/
create view sim_manager as
SELECT PERSON_ID, NAME, SSNUM, GENDER, BIRTH_DATE, ADDRESS, CITY, STATE,
ZIP, TYPE, HIRE_DATE, SALARY, STATUS, TITLE, BONUS, SIM_DEPT_DEPT_ID
FROM sim_person
WHERE TYPE = 'Manager' ;
create or replace TRIGGER manager_trigger
INSTEAD OF insert ON sim_manager
FOR EACH ROW
BEGIN
insert into sim_person(
PERSON_ID,
NAME,
SSNUM,
GENDER,
BIRTH_DATE,
ADDRESS,
CITY,
STATE,
ZIP,
TYPE,
HIRE_DATE,
SALARY,
STATUS,
TITLE,
BONUS,
SIM_DEPT_DEPT_ID)
VALUES (
:new.PERSON_ID,
:new.NAME,
:new.SSNUM,
:new.GENDER,
:new.BIRTH_DATE,
:new.ADDRESS,
:new.CITY,
:new.STATE,
:new.ZIP,
'Manager',
:new.HIRE_DATE,
:new.SALARY,
:new.STATUS,
:new.TITLE,
:new.BONUS,
:new.SIM_DEPT_DEPT_ID) ;
END;
/