This repository was archived by the owner on Jan 26, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDML_EMP_VALUES.trg
More file actions
90 lines (89 loc) · 2.81 KB
/
Copy pathDML_EMP_VALUES.trg
File metadata and controls
90 lines (89 loc) · 2.81 KB
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
/* Formatted on 03-Nov-21 11:55:31 AM (QP5 v5.287) */
CREATE OR REPLACE TRIGGER dml_emp_values
AFTER DELETE OR INSERT OR UPDATE
ON employee
FOR EACH ROW
ENABLE
DECLARE
idn NUMBER (6, 0);
user_name VARCHAR2 (25);
timestampn VARCHAR2 (25);
old_last_name VARCHAR2 (25);
new_last_name VARCHAR2 (25);
old_title VARCHAR2 (10);
new_title VARCHAR2 (10);
old_salary VARCHAR2 (10);
new_salary VARCHAR2 (10);
BEGIN
SELECT USER, TO_CHAR (SYSDATE, 'DD/MON/YYYY HH24:MI:SS')
INTO user_name, timestampn
FROM DUAL;
IF INSERTING
THEN
INSERT INTO t_emp_table (user_name,
timestampn,
idn,
old_last_name,
new_last_name,
old_title,
new_title,
old_salary,
new_salary,
operation)
VALUES (USER,
SYSDATE,
:NEW.employee_id,
:NEW.last_name,
:NEW.last_name,
:NEW.job_id,
:NEW.last_name,
:NEW.salary,
:NEW.salary,
'Insert');
ELSIF DELETING
THEN
INSERT INTO t_emp_table (user_name,
timestampn,
idn,
old_last_name,
new_last_name,
old_title,
new_title,
old_salary,
new_salary,
operation)
VALUES (USER,
SYSDATE,
:OLD.employee_id,
:OLD.last_name,
:OLD.last_name,
:OLD.job_id,
:OLD.last_name,
:OLD.salary,
:OLD.salary,
'Delete');
ELSIF UPDATING
THEN
INSERT INTO t_emp_table (user_name,
timestampn,
idn,
old_last_name,
new_last_name,
old_title,
new_title,
old_salary,
new_salary,
operation)
VALUES (USER,
SYSDATE,
:OLD.employee_id,
:OLD.last_name,
:NEW.last_name,
:OLD.job_id,
:NEW.last_name,
:OLD.salary,
:NEW.salary,
'Insert');
END IF;
END;
/