======================================= DML STATEMENT TRIGGER ======================================
SQL> create or replace Trigger secure_emp
2 before insert on employees
3 begin
4 if (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI')
5 NOT BETWEEN '08:00' AND '18:00') THEN
6 RAISE_APPLICATION_ERROR (-20500,'You may insert EMPLOYEES table only during business hours.');
7 END IF;
8 END;
9 /
Trigger created.
SQL>
SQL> insert into employees (employee_id, last_name, first_name,email,hire_date,job_id,salary,department_id)
2 values (300,'smith','rob','RSMITH',SYSDATE,'IT_PROG',4500,60);
insert into employees (employee_id, last_name, first_name,email,hire_date,job_id,salary,department_id)
*
ERROR at line 1:
ORA-20500: You may insert EMPLOYEES table only during business hours.
ORA-06512: at "HR.SECURE_EMP", line 4
ORA-04088: error during execution of trigger 'HR.SECURE_EMP'
SQL>
====================================== USING CONDITION PRIDECATE ==================================
SQL> create or replace Trigger secure_emp
2 before insert OR update OR delete on employees
3 begin
4 if (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI')
5 NOT BETWEEN '08:00' AND '18:00') THEN
6 if DELETING THEN
7 RAISE_APPLICATION_ERROR (-20502,'You may delete FROM EMPLOYEES table only during business hours.');
8 Elsif INSERTING THEN
9 RAISE_APPLICATION_ERROR (-20500,'You may insert EMPLOYEES table only during business hours.');
10 Elsif UPDATING THEN
11 RAISE_APPLICATION_ERROR (-20503,'You may update salary only during business hours.');
12 Else
13 RAISE_APPLICATION_ERROR (-20504,'You may update EMPLOYEES table only during business hours.');
14 end if;
15 end IF;
16 END;
17 /
Trigger created.
SQL>
============================================ DML ROW TRIGGER =========================================================
SQL> create or replace trigger restrict_salary
2 before insert or update of salary on employees
3 for each row
4 begin
5 IF NOT (:NEW.JOB_ID IN ('AD_PRES','AD_VP')) AND :NEW.SALARY > 15000 THEN
6 RAISE_APPLICATION_ERROR (-20202, 'EMPLOYEE CANNOT EARN THIS AMOUNT');
7 END IF;
8 END;
9 /
Trigger created.
SQL> select salary,job_id from employees where job_id in ('AD_PRES','AD_VP');
SALARY JOB_ID
---------- ----------
24000 AD_PRES
17000 AD_VP
17000 AD_VP
SQL> UPDATE EMPLOYEES SET SALARY=25000 WHERE job_id ='AD_PRES';
1 row updated.
SQL> UPDATE EMPLOYEES SET SALARY=16000 WHERE LAST_NAME ='Kochhar';
1 row updated.
SQL> select salary,job_id,LAST_NAME from employees where job_id in ('AD_PRES','AD_VP');
SALARY JOB_ID LAST_NAME
---------- ---------- -------------------------
25000 AD_PRES King
16000 AD_VP Kochhar
17000 AD_VP De Haan
SQL> select salary,job_id from employees where job_id ='AD_ASST';
SALARY JOB_ID
---------- ----------
4400 AD_ASST
SQL> UPDATE EMPLOYEES SET SALARY=16000 WHERE job_id ='AD_ASST';
UPDATE EMPLOYEES SET SALARY=16000 WHERE job_id ='AD_ASST'
*
ERROR at line 1:
ORA-20202: EMPLOYEE CANNOT EARN THIS AMOUNT
ORA-06512: at "HR.RESTRICT_SALARY", line 3
ORA-04088: error during execution of trigger 'HR.RESTRICT_SALARY'
SQL> UPDATE EMPLOYEES SET SALARY=6000 WHERE job_id ='AD_ASST';
1 row updated.
SQL> select salary,job_id from employees where job_id ='AD_ASST';
SALARY JOB_ID
---------- ----------
6000 AD_ASST
S=================================================================================================================
SQL> CREATE TABLE AUDIT_EMP_TABLE (user_name varchar2(15),timestamp date, id number(5),old_last_name varchar2(20),
2 new_last_name varchar2(20),old_title varchar2(20),new_title varchar2(20),old_salary number(6),new_salary number(6));
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
BIN$acl1i5wGQUaiEKnRLWwbrw==$0 TABLE
AUDIT_EMP_TABLE TABLE
10 rows selected.
SQL> select * from audit_emp_table;
no rows selected
SQL> desc audit_emp_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME VARCHAR2(15)
TIMESTAMP DATE
ID NUMBER(5)
OLD_LAST_NAME VARCHAR2(20)
NEW_LAST_NAME VARCHAR2(20)
OLD_TITLE VARCHAR2(20)
NEW_TITLE VARCHAR2(20)
OLD_SALARY NUMBER(6)
NEW_SALARY NUMBER(6)
SQL> create or replace trigger audit_emp_values
2 after insert or update or delete on employees
3 for each row
4 begin
5 insert into audit_emp_table (user_name,timestamp,id,old_last_name,new_last_name,old_title,new_title,old_salary,
6 new_salary)
7 values (user,sysdate,:old.employee_id,:old.last_name,:new.last_name,:old.job_id,:new.job_id,:old.salary,:new.salary);
8 end;
9 /
Trigger created.
SQL>
====================================== ===== INSTEAD OF TRIGGER =================================************************
SQL> create table new_emp as select employee_id,last_name,salary,department_id,email,job_id,hire_date from employees;
Table created.
SQL> create table new_dept as select d.department_id,d.department_name,d.location_id,sum(e.salary) tot_dept_sal from
2 employees e,departments d where e.department_id=d.department_id group by d.department_id,d.department_name,d.location_id;
Table created.
SQL> create view emp_detail as select e.employee_id,e.last_name,e.salary,e.department_id,e.email,e.job_id,d.department_name,
2 d.location_id from employees e,departments d where e.department_id=d.department_id;
View created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
BIN$acl1i5wGQUaiEKnRLWwbrw==$0 TABLE
AUDIT_EMP_TABLE TABLE
NEW_EMP TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
NEW_DEPT TABLE
EMP_DETAIL VIEW
13 rows selected.
SQL>
SQL>
SQL>
SQL> create or replace trigger new_emp_dept
2 instead of insert or update or delete on emp_detail
3 for each row
4 begin
5 IF INSERTING THEN
6 insert into new_emp
7 values (:new.employee_id,:new.last_name,:new.salary,:new.department_id,:new.email,:new.job_id,sysdate);
8 update new_dept set tot_dept_sal = tot_dept_sal + :new.salary where department_id = :new.department_id;
9 ELSIF DELETING THEN
10 delete from new_emp where employee_id = :old.employee_id;
11 update new_dept set tot_dept_sal = tot_dept_sal - :old.salary where department_id = :old.department_id;
12 ELSIF UPDATING ('salary') THEN
13 update new_emp set salary = :new.salary where employee_id = :old.employee_id;
14 update new_dept set tot_dept_sal = tot_dept_sal + (:new.salary - :old.salary) where department_id = :old.department_id;
15 ELSIF UPDATING ('department_id') THEN
16 update new_emp set department_id = :new.department_id where employee_id = :old.employee_id;
17 update new_dept set tot_dept_sal = tot_dept_sal - :old.salary where department_id = :old.department_id;
18 update new_dept set tot_dept_sal = tot_dept_sal + :new.salary where department_id = :new.department_id;
19 end if;
20 end;
21 /
Trigger created.
SQL>
SQL> SET PAGESIZE 150;
SQL> SET LINESIZE 150;
SQL> COLUMN DEPARTMENT_NAME FORMAT A17;
SQL> COLUMN LAST_NAME FORMAT A12;
SQL> SELECT * FROM EMP_DETAIL;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID EMAIL JOB_ID DEPARTMENT_NAME LOCATION_ID
----------- ------------ ---------- ------------- ------------------------- ---------- ----------------- -----------
198 OConnell 2600 50 DOCONNEL SH_CLERK Shipping 1500
199 Grant 2600 50 DGRANT SH_CLERK Shipping 1500
200 Whalen 6000 10 JWHALEN AD_ASST Administration 1700
201 Hartstein 13000 20 MHARTSTE MK_MAN Marketing 1800
202 Fay 6000 20 PFAY MK_REP Marketing 1800
203 Mavris 6500 40 SMAVRIS HR_REP Human Resources 2400
204 Baer 10000 70 HBAER PR_REP Public Relations 2700
205 Higgins 12000 110 SHIGGINS AC_MGR Accounting 1700
206 Gietz 8300 110 WGIETZ AC_ACCOUNT Accounting 1700
100 King 25000 90 SKING AD_PRES Executive 1700
101 Kochhar 16000 90 NKOCHHAR AD_VP Executive 1700
102 De Haan 17000 90 LDEHAAN AD_VP Executive 1700
103 Hunold 9000 60 AHUNOLD IT_PROG IT 1400
104 Ernst 6000 60 BERNST IT_PROG IT 1400
105 Austin 4800 60 DAUSTIN IT_PROG IT 1400
106 Pataballa 4800 60 VPATABAL IT_PROG IT 1400
107 Lorentz 4200 60 DLORENTZ IT_PROG IT 1400
108 Greenberg 12000 100 NGREENBE FI_MGR Finance 1700
109 Faviet 9000 100 DFAVIET FI_ACCOUNT Finance 1700
110 Chen 8200 100 JCHEN FI_ACCOUNT Finance 1700
111 Sciarra 7700 100 ISCIARRA FI_ACCOUNT Finance 1700
112 Urman 7800 100 JMURMAN FI_ACCOUNT Finance 1700
113 Popp 6900 100 LPOPP FI_ACCOUNT Finance 1700
114 Raphaely 11000 30 DRAPHEAL PU_MAN Purchasing 1700
115 Khoo 3100 30 AKHOO PU_CLERK Purchasing 1700
116 Baida 2900 30 SBAIDA PU_CLERK Purchasing 1700
117 Tobias 2800 30 STOBIAS PU_CLERK Purchasing 1700
118 Himuro 2600 30 GHIMURO PU_CLERK Purchasing 1700
119 Colmenares 2500 30 KCOLMENA PU_CLERK Purchasing 1700
120 Weiss 8000 50 MWEISS ST_MAN Shipping 1500
121 Fripp 8200 50 AFRIPP ST_MAN Shipping 1500
122 Kaufling 7900 50 PKAUFLIN ST_MAN Shipping 1500
123 Vollman 6500 50 SVOLLMAN ST_MAN Shipping 1500
124 Mourgos 5800 50 KMOURGOS ST_MAN Shipping 1500
125 Nayer 3200 50 JNAYER ST_CLERK Shipping 1500
126 Mikkilineni 2700 50 IMIKKILI ST_CLERK Shipping 1500
127 Landry 2400 50 JLANDRY ST_CLERK Shipping 1500
128 Markle 2200 50 SMARKLE ST_CLERK Shipping 1500
129 Bissot 3300 50 LBISSOT ST_CLERK Shipping 1500
130 Atkinson 2800 50 MATKINSO ST_CLERK Shipping 1500
131 Marlow 2500 50 JAMRLOW ST_CLERK Shipping 1500
132 Olson 2100 50 TJOLSON ST_CLERK Shipping 1500
133 Mallin 3300 50 JMALLIN ST_CLERK Shipping 1500
134 Rogers 2900 50 MROGERS ST_CLERK Shipping 1500
135 Gee 2400 50 KGEE ST_CLERK Shipping 1500
136 Philtanker 2200 50 HPHILTAN ST_CLERK Shipping 1500
137 Ladwig 3600 50 RLADWIG ST_CLERK Shipping 1500
138 Stiles 3200 50 SSTILES ST_CLERK Shipping 1500
139 Seo 2700 50 JSEO ST_CLERK Shipping 1500
140 Patel 2500 50 JPATEL ST_CLERK Shipping 1500
141 Rajs 3500 50 TRAJS ST_CLERK Shipping 1500
142 Davies 3100 50 CDAVIES ST_CLERK Shipping 1500
143 Matos 2600 50 RMATOS ST_CLERK Shipping 1500
144 Vargas 2500 50 PVARGAS ST_CLERK Shipping 1500
145 Russell 14000 80 JRUSSEL SA_MAN Sales 2500
146 Partners 13500 80 KPARTNER SA_MAN Sales 2500
147 Errazuriz 12000 80 AERRAZUR SA_MAN Sales 2500
148 Cambrault 11000 80 GCAMBRAU SA_MAN Sales 2500
149 Zlotkey 10500 80 EZLOTKEY SA_MAN Sales 2500
150 Tucker 10000 80 PTUCKER SA_REP Sales 2500
151 Bernstein 9500 80 DBERNSTE SA_REP Sales 2500
152 Hall 9000 80 PHALL SA_REP Sales 2500
153 Olsen 8000 80 COLSEN SA_REP Sales 2500
154 Cambrault 7500 80 NCAMBRAU SA_REP Sales 2500
155 Tuvault 7000 80 OTUVAULT SA_REP Sales 2500
156 King 10000 80 JKING SA_REP Sales 2500
157 Sully 9500 80 PSULLY SA_REP Sales 2500
158 McEwen 9000 80 AMCEWEN SA_REP Sales 2500
159 Smith 8000 80 LSMITH SA_REP Sales 2500
160 Doran 7500 80 LDORAN SA_REP Sales 2500
161 Sewall 7000 80 SSEWALL SA_REP Sales 2500
162 Vishney 10500 80 CVISHNEY SA_REP Sales 2500
163 Greene 9500 80 DGREENE SA_REP Sales 2500
164 Marvins 7200 80 MMARVINS SA_REP Sales 2500
165 Lee 6800 80 DLEE SA_REP Sales 2500
166 Ande 6400 80 SANDE SA_REP Sales 2500
167 Banda 6200 80 ABANDA SA_REP Sales 2500
168 Ozer 11500 80 LOZER SA_REP Sales 2500
169 Bloom 10000 80 HBLOOM SA_REP Sales 2500
170 Fox 9600 80 TFOX SA_REP Sales 2500
171 Smith 7400 80 WSMITH SA_REP Sales 2500
172 Bates 7300 80 EBATES SA_REP Sales 2500
173 Kumar 6100 80 SKUMAR SA_REP Sales 2500
174 Abel 11000 80 EABEL SA_REP Sales 2500
175 Hutton 8800 80 AHUTTON SA_REP Sales 2500
176 Taylor 8600 80 JTAYLOR SA_REP Sales 2500
177 Livingston 8400 80 JLIVINGS SA_REP Sales 2500
179 Johnson 6200 80 CJOHNSON SA_REP Sales 2500
180 Taylor 3200 50 WTAYLOR SH_CLERK Shipping 1500
181 Fleaur 3100 50 JFLEAUR SH_CLERK Shipping 1500
182 Sullivan 2500 50 MSULLIVA SH_CLERK Shipping 1500
183 Geoni 2800 50 GGEONI SH_CLERK Shipping 1500
184 Sarchand 4200 50 NSARCHAN SH_CLERK Shipping 1500
185 Bull 4100 50 ABULL SH_CLERK Shipping 1500
186 Dellinger 3400 50 JDELLING SH_CLERK Shipping 1500
187 Cabrio 3000 50 ACABRIO SH_CLERK Shipping 1500
188 Chung 3800 50 KCHUNG SH_CLERK Shipping 1500
189 Dilly 3600 50 JDILLY SH_CLERK Shipping 1500
190 Gates 2900 50 TGATES SH_CLERK Shipping 1500
191 Perkins 2500 50 RPERKINS SH_CLERK Shipping 1500
192 Bell 4000 50 SBELL SH_CLERK Shipping 1500
193 Everett 3900 50 BEVERETT SH_CLERK Shipping 1500
194 McCain 3200 50 SMCCAIN SH_CLERK Shipping 1500
195 Jones 2800 50 VJONES SH_CLERK Shipping 1500
196 Walsh 3100 50 AWALSH SH_CLERK Shipping 1500
197 Feeney 3000 50 KFEENEY SH_CLERK Shipping 1500
106 rows selected.
SQL>
SQL> SELECT * FROM NEW_DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID TOT_DEPT_SAL
------------- ----------------- ----------- ------------
110 Accounting 1700 20300
80 Sales 2500 304500
10 Administration 1700 6000 --\\OLD SALARY
60 IT 1400 28800
20 Marketing 1800 19000
90 Executive 1700 58000
50 Shipping 1500 156400
40 Human Resources 2400 6500
30 Purchasing 1700 24900
100 Finance 1700 51600
70 Public Relations 2700 10000
11 rows selected.
SQL>
SQL> SELECT * FROM NEW_EMP;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID EMAIL JOB_ID HIRE_DATE
----------- ------------ ---------- ------------- ------------------------- ---------- ---------
198 OConnell 2600 50 DOCONNEL SH_CLERK 21-JUN-99
150 Tucker 10000 80 PTUCKER SA_REP 30-JAN-97
151 Bernstein 9500 80 DBERNSTE SA_REP 24-MAR-97
152 Hall 9000 80 PHALL SA_REP 20-AUG-97
153 Olsen 8000 80 COLSEN SA_REP 30-MAR-98
154 Cambrault 7500 80 NCAMBRAU SA_REP 09-DEC-98
155 Tuvault 7000 80 OTUVAULT SA_REP 23-NOV-99
177 Livingston 8400 80 JLIVINGS SA_REP 23-APR-98
178 Grant 7000 KGRANT SA_REP 24-MAY-99
179 Johnson 6200 80 CJOHNSON SA_REP 04-JAN-00
180 Taylor 3200 50 WTAYLOR SH_CLERK 24-JAN-98
181 Fleaur 3100 50 JFLEAUR SH_CLERK 23-FEB-98
182 Sullivan 2500 50 MSULLIVA SH_CLERK 21-JUN-99
183 Geoni 2800 50 GGEONI SH_CLERK 03-FEB-00
184 Sarchand 4200 50 NSARCHAN SH_CLERK 27-JAN-96
185 Bull 4100 50 ABULL SH_CLERK 20-FEB-97
186 Dellinger 3400 50 JDELLING SH_CLERK 24-JUN-98
187 Cabrio 3000 50 ACABRIO SH_CLERK 07-FEB-99
188 Chung 3800 50 KCHUNG SH_CLERK 14-JUN-97
189 Dilly 3600 50 JDILLY SH_CLERK 13-AUG-97
190 Gates 2900 50 TGATES SH_CLERK 11-JUL-98
191 Perkins 2500 50 RPERKINS SH_CLERK 19-DEC-99
192 Bell 4000 50 SBELL SH_CLERK 04-FEB-96
193 Everett 3900 50 BEVERETT SH_CLERK 03-MAR-97
194 McCain 3200 50 SMCCAIN SH_CLERK 01-JUL-98
195 Jones 2800 50 VJONES SH_CLERK 17-MAR-99
196 Walsh 3100 50 AWALSH SH_CLERK 24-APR-98
197 Feeney 3000 50 KFEENEY SH_CLERK 23-MAY-98
107 rows selected.
SQL>
SQL>
SQL> INSERT INTO EMP_DETAIL (EMPLOYEE_ID,LAST_NAME,SALARY,DEPARTMENT_ID,EMAIL,JOB_ID)
2 VALUES (9001,'Tinku',8000,10,'tinku.com','HR_MAN');
1 row created.
SQL> SELECT * FROM NEW_EMP;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID EMAIL JOB_ID HIRE_DATE
----------- ------------ ---------- ------------- ------------------------- ---------- ---------
198 OConnell 2600 50 DOCONNEL SH_CLERK 21-JUN-99
199 Grant 2600 50 DGRANT SH_CLERK 13-JAN-00
137 Ladwig 3600 50 RLADWIG ST_CLERK 14-JUL-95
186 Dellinger 3400 50 JDELLING SH_CLERK 24-JUN-98
187 Cabrio 3000 50 ACABRIO SH_CLERK 07-FEB-99
188 Chung 3800 50 KCHUNG SH_CLERK 14-JUN-97
189 Dilly 3600 50 JDILLY SH_CLERK 13-AUG-97
190 Gates 2900 50 TGATES SH_CLERK 11-JUL-98
191 Perkins 2500 50 RPERKINS SH_CLERK 19-DEC-99
192 Bell 4000 50 SBELL SH_CLERK 04-FEB-96
193 Everett 3900 50 BEVERETT SH_CLERK 03-MAR-97
194 McCain 3200 50 SMCCAIN SH_CLERK 01-JUL-98
195 Jones 2800 50 VJONES SH_CLERK 17-MAR-99
196 Walsh 3100 50 AWALSH SH_CLERK 24-APR-98
197 Feeney 3000 50 KFEENEY SH_CLERK 23-MAY-98
9001 Tinku 8000 10 tinku.com HR_MAN 04-AUG-10 -- \\ NEW ROW INSERT
108 rows selected.
SQL> SELECT * FROM NEW_DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID TOT_DEPT_SAL
------------- ----------------- ----------- ------------
110 Accounting 1700 20300
80 Sales 2500 304500
10 Administration 1700 14000---(8000 increase salary of department_id 10)---old salary 6000
60 IT 1400 28800
20 Marketing 1800 19000
90 Executive 1700 58000
50 Shipping 1500 156400
40 Human Resources 2400 6500
30 Purchasing 1700 24900
100 Finance 1700 51600
70 Public Relations 2700 10000
11 rows selected.
SQL>
No comments:
Post a Comment