180406 Creating and Using Triggers
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
12 /
Trigger created.
HR@orcl>insert into employees
2
HR@orcl>
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
12 /
Trigger created.
HR@orcl>save secure_emp.sql
Created file secure_emp.sql
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT OR UPDATE ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>update employees
2 set salary = salary*1.1
3 where employee_id = 100;
1 row updated.
HR@orcl>!date
Fri Apr 6 10:29:31 KST 2018
HR@orcl>ed
Wrote file afiedt.buf
1 update employees
2 set salary = salary*1.1
3* where employee_id = 100
HR@orcl>get secure_emp
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>/
Trigger created.
HR@orcl>update employees
2 set salary = salary*1.1
3 where employee_id = 100;
1 row updated.
HR@orcl>get secure_emp
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT OR UPDATE ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>save secure_emp.sql
HR@orcl>get secure_emp
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>update employees
2 set salary = salary * 1.1
3 where employee_id = 100;
1 row updated.
HR@orcl>ed
Wrote file afiedt.buf
1 update employees
2 set salary = salary * 1.1
3* where employee_id = 100
HR@orcl>get secure_emp
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT OR UPDATE ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 NOT BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT OR UPDATE ON employees
3 BEGIN
4 IF (TO_CHAR(SYSDATE,'DY') IN ('FRI','SAT','SUN')) OR
5 (TO_CHAR(SYSDATE,'HH24:MI')
6 BETWEEN '09:00' AND '13:00') THEN
7 RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||
8 ' into EMPLOYEES table only during '
9 || 'normal business hours.');
10 END IF;
11* END;
HR@orcl>update employees
2 set salary = salary * 1.1
3 where employee_id = 100;
1 row updated.
HR@orcl>save secure_emp.sql
Created file secure_emp.sql
HR@orcl>update employees
2 set salary = salary *1.1
3 where employee_id = 101;
update employees
*
ERROR at line 1:
ORA-20500: You may insert into EMPLOYEES table only during normal business hours.
ORA-06512: at "HR.SECURE_EMP", line 5
ORA-04088: error during execution of trigger 'HR.SECURE_EMP'
HR@orcl>alter trigger secure_emp disable
2 ;
Trigger altered.
HR@orcl>update employees
2 set salary = 15500
3 where last_name ='Zlotkey';
update employees
*
ERROR at line 1:
ORA-20202: Employee cannot earn more the $15,000.
ORA-06512: at "HR.RESTRICT_SALARY", line 4
ORA-04088: error during execution of trigger 'HR.RESTRICT_SALARY'
HR@orcl>ed
Wrote file afiedt.buf
1 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'))
6 AND :NEW.salary > 15000 THEN
7 RAISE_APPLICATION_ERROR (-20202,
8 'Employee cannot earn more the $15,000.');
9 END IF;
10* END;
11 /
Trigger created.
HR@orcl>save restrict_salary.sql
Created file restrict_salary.sql
HR@orcl>alter trigger secure_emp able
2 ;
alter trigger secure_emp able
*
ERROR at line 1:
ORA-00922: missing or invalid option
HR@orcl>alter trigger secure_emp enable
2 ;
Trigger altered.
HR@orcl>alter trigger secure_emp disable
2 ;
Trigger altered.
HR@orcl>alter trigger restrict_salary disable
2 ;
Trigger altered.
HR@orcl>CREATE TABLE audit_emp ( user_name VARCHAR2(30),
2 time_stamp timestamp(2), ip_addr varchar2(20),
3 os_user varchar2(20), id number(6),
4 old_last_name varchar2(25), new_last_name varchar2(25),
5 old_title varchar2(10), new_title varchar2(10),
6 old_salary number(8,2) new_salary number(8,2));
old_salary number(8,2) new_salary number(8,2))
*
ERROR at line 6:
ORA-00907: missing right parenthesis
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE TABLE audit_emp ( user_name VARCHAR2(30),
2 time_stamp timestamp(2), ip_addr varchar2(20),
3 os_user varchar2(20), id number(6),
4 old_last_name varchar2(25), new_last_name varchar2(25),
5 old_title varchar2(10), new_title varchar2(10),
6* old_salary number(8,2) new_salary number(8,2))
HR@orcl>/
old_salary number(8,2) new_salary number(8,2))
*
ERROR at line 6:
ORA-00907: missing right parenthesis
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE TABLE audit_emp (user_name VARCHAR2(30),
2 time_stamp timestamp(2), ip_addr varchar2(20),
3 os_user varchar2(20), id number(6),
4 old_last_name varchar2(25), new_last_name varchar2(25),
5 old_title varchar2(10), new_title varchar2(10),
6* old_salary number(8,2), new_salary number(8,2))
HR@orcl>/
Table created.
HR@orcl>CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp(user_name, time_stamp, ip_addr, os_user, id,
6 old_last_name, new_last_name, old_title, new_title, old_salary, new_salary)
7 VALUES (USER, SYSDATE, sys_context('userenv','os_user'), sys_context('su
8
9 /
Warning: Trigger created with compilation errors.
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp(user_name, time_stamp, ip_addr, os_user, id,
6 old_last_name, new_last_name, old_title, new_title, old_salary, new_salary)
7 VALUES (USER, SYSDATE,sys_context('userenv','ip_address'), sys_context('userenv','os_user'), employee_id, :OLD.last_name, :NEW.last_name, :OLD.title, :NEW.title, :OLD.salary, :NEW.salary);
8* END;
9
10 /
Warning: Trigger created with compilation errors.
HR@orcl>show error
Errors for TRIGGER AUDIT_EMP_VALUES:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/140 PLS-00049: bad bind variable 'OLD.TITLE'
4/152 PLS-00049: bad bind variable 'NEW.TITLE'
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp(user_name, time_stamp, ip_addr, os_user, id,
6 old_last_name, new_last_name, old_title, new_title, old_salary, new_salary)
7 VALUES (USER, SYSDATE,sys_context('userenv','ip_address'), sys_context('userenv','os_user'), employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_title, :NEW.job_title, :OLD.salary, :NEW.salary);
8* END;
9
10 /
Warning: Trigger created with compilation errors.
HR@orcl>show error
Errors for TRIGGER AUDIT_EMP_VALUES:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/140 PLS-00049: bad bind variable 'OLD.JOB_TITLE'
4/156 PLS-00049: bad bind variable 'NEW.JOB_TITLE'
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp(user_name, time_stamp, ip_addr, os_user, id,
6 old_last_name, new_last_name, old_title, new_title, old_salary, new_salary)
7 VALUES (USER, SYSDATE,sys_context('userenv','ip_address'), sys_context('userenv','os_user'), employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary);
8* END;
9 /
Warning: Trigger created with compilation errors.
HR@orcl>show error
Errors for TRIGGER AUDIT_EMP_VALUES:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2 PL/SQL: SQL Statement ignored
4/95 PL/SQL: ORA-00984: column not allowed here
HR@orcl>ed\
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp(user_name, time_stamp, ip_addr, os_user, id,
6 old_last_name, new_last_name, old_job_id, new_job_id, old_salary, new_salary)
7 VALUES (USER, SYSDATE,sys_context('userenv','ip_address'), sys_context('userenv','os_user'), employee_id, :OLD.last_name, :NEW.last_name,
8 :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary);
9* END;
HR@orcl>/
Warning: Trigger created with compilation errors.
HR@orcl>shoe error
SP2-0042: unknown command "shoe error" - rest of line ignored.
HR@orcl>show error
Errors for TRIGGER AUDIT_EMP_VALUES:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2 PL/SQL: SQL Statement ignored
4/95 PL/SQL: ORA-00984: column not allowed here
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp(user_name, time_stamp, ip_addr, os_user, id,
6 old_last_name, new_last_name, old_job_id, new_job_id, old_salary, new_salary)
7 VALUES (USER, SYSDATE,sys_context('userenv','ip_address'),
8 sys_context('userenv','os_user'),:OLD.employee_id, :OLD.last_name, :NEW.last_name,
9 :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary);
10* END;
HR@orcl>/
Warning: Trigger created with compilation errors.
HR@orcl>show error
Errors for TRIGGER AUDIT_EMP_VALUES:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2 PL/SQL: SQL Statement ignored
3/43 PL/SQL: ORA-00904: "NEW_JOB_ID": invalid identifier
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp(user_name, time_stamp, ip_addr, os_user, id,
6 old_last_name, new_last_name, old_title, new_title, old_salary, new_salary)
7 VALUES (USER, SYSDATE,sys_context('userenv','ip_address'),
8 sys_context('userenv','os_user'),:OLD.employee_id, :OLD.last_name, :NEW.last_name,
9 :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary);
10* END;
HR@orcl>/
Trigger created.
HR@orcl>save audit_emp_values.sql
Created file audit_emp_values.sql
HR@orcl>INSERT INTO employees(employee_id, last_name, job_id, salary, email, hire_date)
2 VALUES(999,'Temp emp', 'SA_REP', 6000, 'TEMPEMP', TRUNC(SYSDATE));
INSERT INTO employees(employee_id, last_name, job_id, salary, email, hire_date)
*
ERROR at line 1:
ORA-01502: index 'HR.EMP_EMP_ID_PK' or partition of such index is in unusable state
HR@orcl>ED
Wrote file afiedt.buf
1 INSERT INTO employees(employee_id, last_name, job_id, salary, email, hire_date)
2* VALUES(999,'Temp emp', 'SA_REP', 6000, 'TEMPEMP', TRUNC(SYSDATE))
HR@orcl>/
INSERT INTO employees(employee_id, last_name, job_id, salary, email, hire_date)
*
ERROR at line 1:
ORA-01502: index 'HR.EMP_EMP_ID_PK' or partition of such index is in unusable state
HR@orcl>ed
Wrote file afiedt.buf
1 INSERT INTO employees
2* VALUES('hyejin',sysdate,sys_context('userenv','ip_address'),sys_context('userenv','os_user'))
HR@orcl>/
INSERT INTO employees
*
ERROR at line 1:
ORA-00947: not enough values
HR@orcl>update employees set salary = 7000, last_name = 'Smith' where employee_id = 100;
1 row updated.
HR@orcl>select * from audit_emp;
USER_NAME TIME_STAMP
------------------------------ ---------------------------------------------------------------------------
IP_ADDR OS_USER ID OLD_LAST_NAME NEW_LAST_NAME OLD_TITLE
-------------------- -------------------- ---------- ------------------------- ------------------------- ----------
NEW_TITLE OLD_SALARY NEW_SALARY
---------- ---------- ----------
HR 06-APR-18 11.32.38.00 AM
oracle 100 King Smith AD_PRES
AD_PRES 51020.96 7000
HR@orcl>show linesize
linesize 120
HR@orcl>set linesize 80
HR@orcl>/
USER_NAME
------------------------------
TIME_STAMP
---------------------------------------------------------------------------
IP_ADDR OS_USER ID OLD_LAST_NAME
-------------------- -------------------- ---------- -------------------------
NEW_LAST_NAME OLD_TITLE NEW_TITLE OLD_SALARY NEW_SALARY
------------------------- ---------- ---------- ---------- ----------
HR
06-APR-18 11.32.38.00 AM
oracle 100 King
Smith AD_PRES AD_PRES 51020.96 7000
HR@orcl>set linesize 160;
HR@orcl>/
USER_NAME TIME_STAMP IP_ADDR OS_USER ID
------------------------------ --------------------------------------------------------------------------- -------------------- -------------------- ----------
OLD_LAST_NAME NEW_LAST_NAME OLD_TITLE NEW_TITLE OLD_SALARY NEW_SALARY
------------------------- ------------------------- ---------- ---------- ---------- ----------
HR 06-APR-18 11.32.38.00 AM oracle 100
King Smith AD_PRES AD_PRES 51020.96 7000
HR@orcl>insert into employees
2 values(450, gwon, hyejin, koongdori, 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP';
values(450, gwon, hyejin, koongdori, 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP'
*
ERROR at line 2:
ORA-00917: missing comma
HR@orcl>ED
Wrote file afiedt.buf
1 insert into employees
2* values(450, gwon, hyejin, koongdori, 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP')
HR@orcl>.
HR@orcl>/
values(450, gwon, hyejin, koongdori, 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP')
*
ERROR at line 2:
ORA-00984: column not allowed here
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2* values(450,'gwon', 'hyejin', 'koongdori', 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP')
HR@orcl>/
insert into employees
*
ERROR at line 1:
ORA-01502: index 'HR.EMP_EMP_ID_PK' or partition of such index is in unusable state
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2* values(450,'gwon', 'hyejin', 'koongdori', 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP')
HR@orcl>/
insert into employees
*
ERROR at line 1:
ORA-01502: index 'HR.EMP_EMP_ID_PK' or partition of such index is in unusable state
HR@orcl>select 'alter index '||index_name||' rebuild;'
2 from user_indexes
3 where table_name = 'EMPLOYEES';
'ALTERINDEX'||INDEX_NAME||'REBUILD;'
---------------------------------------------------
alter index EMP_NAME_IX rebuild;
alter index EMP_MANAGER_IX rebuild;
alter index EMP_JOB_IX rebuild;
alter index EMP_DEPARTMENT_IX rebuild;
alter index EMP_EMP_ID_PK rebuild;
alter index EMP_EMAIL_UK rebuild;
6 rows selected.
HR@orcl>set heading off
HR@orcl>set feedback off
HR@orcl>spool in.sql
HR@orcl>/
alter index EMP_NAME_IX rebuild;
alter index EMP_MANAGER_IX rebuild;
alter index EMP_JOB_IX rebuild;
alter index EMP_DEPARTMENT_IX rebuild;
alter index EMP_EMP_ID_PK rebuild;
alter index EMP_EMAIL_UK rebuild;
HR@orcl>spool off
HR@orcl>set heading on
HR@orcl>set feedback on
HR@orcl>/
'ALTERINDEX'||INDEX_NAME||'REBUILD;'
---------------------------------------------------
alter index EMP_NAME_IX rebuild;
alter index EMP_MANAGER_IX rebuild;
alter index EMP_JOB_IX rebuild;
alter index EMP_DEPARTMENT_IX rebuild;
alter index EMP_EMP_ID_PK rebuild;
alter index EMP_EMAIL_UK rebuild;
6 rows selected.
HR@orcl>@in
SP2-0042: unknown command "HR@orcl>/" - rest of line ignored.
Index altered.
Index altered.
Index altered.
Index altered.
Index altered.
Index altered.
SP2-0734: unknown command beginning "HR@orcl>sp..." - rest of line ignored.
HR@orcl>ed
Wrote file afiedt.buf
1* alter index EMP_EMAIL_UK rebuild
HR@orcl>insert into employees
values(450, gwon, hyejin, koongdori, 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP');
ERROR:
ORA-01756: quoted string not properly terminated
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2 values(450, 'gwon','hyejin', 'koongdori', 88570735, sysdate, 'temp emp',
3* 5000, null, null, 'SA_REP')
HR@orcl>/
5000, null, null, 'SA_REP')
*
ERROR at line 3:
ORA-01722: invalid number
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2 values(450, 'gwon','hyejin', 'koongdori', 88570735, sysdate, 'temp emp',
3* 5000, null, null, 'SA_REP')
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2* values(450,'gwon', 'hyejin', 'koongdori', 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP')
3 /
values(450,'gwon', 'hyejin', 'koongdori', 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP')
*
ERROR at line 2:
ORA-01722: invalid number
HR@orcl>/
values(450,'gwon', 'hyejin', 'koongdori', 88570735, sysdate, 'temp emp', 5000, null, null, 'SA_REP')
*
ERROR at line 2:
ORA-01722: invalid number
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2* values(450,'gwon', 'hyejin', 'koongdori', '88570735', sysdate, 'temp emp', 5000, null, null, 'SA_REP')
HR@orcl>/
values(450,'gwon', 'hyejin', 'koongdori', '88570735', sysdate, 'temp emp', 5000, null, null, 'SA_REP')
*
ERROR at line 2:
ORA-01722: invalid number
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2* values(450,'gwon', 'hyejin', 'koongdori', '88570735', sysdate, 'temp emp', 5000, null, null, 'SA_REP')
HR@orcl>/
values(450,'gwon', 'hyejin', 'koongdori', '88570735', sysdate, 'temp emp', 5000, null, null, 'SA_REP')
*
ERROR at line 2:
ORA-01722: invalid number
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2* values(450,'gwon', 'hyejin', 'koongdori', '88570735', sysdate, 'IT_PROG', 5000, null, null, null)
HR@orcl>/
1 row created.
HR@orcl>select * from audit_emp;
USER_NAME TIME_STAMP IP_ADDR OS_USER ID
------------------------------ --------------------------------------------------------------------------- -------------------- -------------------- ----------
OLD_LAST_NAME NEW_LAST_NAME OLD_TITLE NEW_TITLE OLD_SALARY NEW_SALARY
------------------------- ------------------------- ---------- ---------- ---------- ----------
HR 06-APR-18 11.32.38.00 AM oracle 100
King Smith AD_PRES AD_PRES 51020.96 7000
HR 06-APR-18 11.47.05.00 AM oracle
hyejin IT_PROG 5000
2 rows selected.
HR@orcl>show pagesize
pagesize 14
HR@orcl>set pagesize 20
HR@orcl>/
USER_NAME TIME_STAMP IP_ADDR OS_USER ID
------------------------------ --------------------------------------------------------------------------- -------------------- -------------------- ----------
OLD_LAST_NAME NEW_LAST_NAME OLD_TITLE NEW_TITLE OLD_SALARY NEW_SALARY
------------------------- ------------------------- ---------- ---------- ---------- ----------
HR 06-APR-18 11.32.38.00 AM oracle 100
King Smith AD_PRES AD_PRES 51020.96 7000
HR 06-APR-18 11.47.05.00 AM oracle
hyejin IT_PROG 5000
2 rows selected.
HR@orcl>show linesize 100;
linesize 160
SP2-0158: unknown SHOW option "100"
HR@orcl>show linesize
linesize 160
HR@orcl>set linesize 100
HR@orcl>/
USER_NAME
------------------------------
TIME_STAMP IP_ADDR
--------------------------------------------------------------------------- --------------------
OS_USER ID OLD_LAST_NAME NEW_LAST_NAME OLD_TITLE
-------------------- ---------- ------------------------- ------------------------- ----------
NEW_TITLE OLD_SALARY NEW_SALARY
---------- ---------- ----------
HR
06-APR-18 11.32.38.00 AM
oracle 100 King Smith AD_PRES
AD_PRES 51020.96 7000
HR
06-APR-18 11.47.05.00 AM
oracle hyejin
IT_PROG 5000
2 rows selected.
HR@orcl>insert into employees
2 (employee_id, last_name, job_id, salary, email, hire_date)
3 values(999,'temp emp', 'sa_rep', 6000, 'tempemp', trunc(sysdate))
4 /
insert into employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2 (employee_id, last_name, job_id, salary, email, hire_date)
3 values(999,'temp emp', 'sa_rep', 6000, 'tempemp', trunc(sysdate))
4 /
5 update employees
6 set salary = 7000, last_name = 'smith'
7 where employee_id = 999
8* /select * from audit_emp;
HR@orcl>/
/
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees(employee_id, last_name, job_id, salary, email, hire_date)
2 values(999,'temp emp', 'sa_rep', 6000, 'tempemp', trunc(sysdate))
3 /
4 update employees
5 set salary = 7000, last_name = 'smith'
6 where employee_id = 999
7 /
8* select * from audit_emp
HR@orcl>/
/
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2 values(999,'temp emp', 'sa_rep', 6000, 'tempemp', trunc(sysdate))
3 /
4 update employees
5 set salary = 7000, last_name = 'smith'
6 where employee_id = 999
7 /
8* select * from audit_emp
HR@orcl>/
/
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
HR@orcl>ed
Wrote file afiedt.buf
1 insert into employees
2 values(999,'temp emp', 'sa_rep', 6000, 'tempemp', trunc(sysdate))
3 /
4 update employees
5 set salary = 7000, last_name = 'smith'
6 where employee_id = 999
7 /
8* select * from audit_emp
HR@orcl>CREATE OR REPLACE TRIGGER derive_commission_pct
2
3
4 /
*
ERROR at line 3:
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword
HR@orcl>CREATE OR REPLACE TRIGGER
2
3
4 /
*
ERROR at line 3:
ORA-04070: invalid trigger name
HR@orcl>ED
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* END;
8 /
Trigger created.
HR@orcl>update job_history
2
HR@orcl>update employees
2 set job_id = 'SA_REP'
3 where employee_id = 100;
1 row updated.
HR@orcl>select * from job_history;
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
100 17-JUN-87 06-APR-18 AD_PRES 90
102 13-JAN-93 24-JUL-98 IT_PROG 60
101 21-SEP-89 27-OCT-93 AC_ACCOUNT 110
101 28-OCT-93 15-MAR-97 AC_MGR 110
201 17-FEB-96 19-DEC-99 MK_REP 20
114 24-MAR-98 31-DEC-99 ST_CLERK 50
122 01-JAN-99 31-DEC-99 ST_CLERK 50
200 17-SEP-87 17-JUN-93 AD_ASST 90
176 24-MAR-98 31-DEC-98 SA_REP 80
176 01-JAN-99 31-DEC-99 SA_MAN 80
200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90
11 rows selected.
HR@orcl>1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* END;
8 /
HR@orcl>HR@orcl>HR@orcl>HR@orcl>HR@orcl>HR@orcl>SP2-0042: unknown command "7* END" - rest of line ignored.
HR@orcl>HR@orcl>HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE OF job_id ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id)
7* ;
HR@orcl>/
Warning: Trigger created with compilation errors.
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE OF job_id ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* end;
8 /
Trigger created.
HR@orcl>save in_job_history.sql
Created file in_job_history.sql
HR@orcl>update employees
2 set job_id = 'SA_REP'
3 where employee_id = 100;
update employees
*
ERROR at line 1:
ORA-00001: unique constraint (HR.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "HR.IN_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'HR.IN_JOB_HISTORY'
HR@orcl>/
update employees
*
ERROR at line 1:
ORA-00001: unique constraint (HR.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "HR.IN_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'HR.IN_JOB_HISTORY'
HR@orcl>ed
Wrote file afiedt.buf
1 update employees
2 set job_id = 'SA_REP'
3* where employee_id = 100
HR@orcl>ed
Wrote file afiedt.buf
1 update employees
2 set job_id = 'SA_REP'
3* where employee_id = 107
HR@orcl>/
1 row updated.
HR@orcl>select * from job_history;
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
100 17-JUN-87 06-APR-18 AD_PRES 90
107 07-FEB-99 06-APR-18 IT_PROG 60
102 13-JAN-93 24-JUL-98 IT_PROG 60
101 21-SEP-89 27-OCT-93 AC_ACCOUNT 110
101 28-OCT-93 15-MAR-97 AC_MGR 110
201 17-FEB-96 19-DEC-99 MK_REP 20
114 24-MAR-98 31-DEC-99 ST_CLERK 50
122 01-JAN-99 31-DEC-99 ST_CLERK 50
200 17-SEP-87 17-JUN-93 AD_ASST 90
176 24-MAR-98 31-DEC-98 SA_REP 80
176 01-JAN-99 31-DEC-99 SA_MAN 80
200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90
12 rows selected.
HR@orcl>get in_job_history
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE OF job_id ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* end;
HR@orcl>create user SCOTT identified by SCOTT;
create user SCOTT identified by SCOTT
*
ERROR at line 1:
ORA-01031: insufficient privileges
HR@orcl>conn / as sysdba
Connected.
SYS@orcl>create user scott identified by scott;
User created.
SYS@orcl>create table new_emp
2 as select empno, ename, sal, deptno, job, hiredate
3 from emp;
from emp
*
ERROR at line 3:
ORA-00942: table or view does not exist
SYS@orcl>ed
Wrote file afiedt.buf
1 create table new_emp
2 as select empno, ename, sal, deptno, job, hiredate
3* from employees
SYS@orcl>/
from employees
*
ERROR at line 3:
ORA-00942: table or view does not exist
SYS@orcl>create table new_emp
2 as select employee_id, last_name, salary, department_id, job_id, hire_date
3 from employees;
from employees
*
ERROR at line 3:
ORA-00942: table or view does not exist
SYS@orcl>ed
Wrote file afiedt.buf
1 create table new_emp
2 as select employee_id, last_name, salary, department_id, job_id, hire_date
3* from employees
SYS@orcl>conn hr/hr
Connected.
HR@orcl>create table new_emp
as select employee_id, last_name, salary, department_id, job_id, hire_date
from employees
/
2 3 4
Table created.
HR@orcl>drop table new_emp purge;
Table dropped.
HR@orcl>conn / as sysdba
Connected.
SYS@orcl>grant on connect, read, write to scott;
grant on connect, read, write to scott
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SYS@orcl>grant connect, read, write to scott
2 ;
grant connect, read, write to scott
*
ERROR at line 1:
ORA-01919: role 'READ' does not exist
SYS@orcl>grant connect, write to scott;
grant connect, write to scott
*
ERROR at line 1:
ORA-01919: role 'WRITE' does not exist
SYS@orcl>grant connect to scott
2 ;
Grant succeeded.
SYS@orcl>grant connect, resource to scott;
Grant succeeded.
SYS@orcl>conn scott/scott
Connected.
SCOTT@orcl>create table new_emp
2 as select employee_id, last_name, salary, department_id, job_id, hire_date from employees;
as select employee_id, last_name, salary, department_id, job_id, hire_date from employees
*
ERROR at line 2:
ORA-00942: table or view does not exist
SCOTT@orcl>ed
Wrote file afiedt.buf
1 create table new_emp
2* as select employee_id, last_name, salary, department_id, job_id, hire_date from hr.employees
SCOTT@orcl>/
as select employee_id, last_name, salary, department_id, job_id, hire_date from hr.employees
*
ERROR at line 2:
ORA-00942: table or view does not exist
SCOTT@orcl>ed
Wrote file afiedt.buf
1 create table new_emp
2* as select employee_id, last_name, salary, department_id, job_id, hire_date from hr.employees
SCOTT@orcl>conn / as sysdba
Connected.
SYS@orcl>create table scott.new_emp
2 as select employee_id, last_name, salary, department_id, job_id, hire_date from hr.employees;
Table created.
SYS@orcl>conn hr/hr
Connected.
HR@orcl>get in_job_history
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE OF job_id ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* end;
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE OF job_id ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* end;
HR@orcl>ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE OF job_id ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* end;
HR@orcl>CREATE OR REPLACE TRIGGER derive_commission_pct
2 BEFORE INSERT OR UPDATE OF salary ON employees
3 FOR EACH ROW
4 WHEN (NEW.job_id='SA_REP')
5 BEGIN IF INSERTING THEN
6 :NEW.COMMISSION_PCT := 0;
7 ELSIF :OLD.commission_pct IS NULL THEN
8 :NEW.commission_pct := 0;
9 ELSE
10 :NEW.commission_pct := :OLD.commission_pct+0.05;
11 END IF
12 ;
13 END;
14 /
Trigger created.
HR@orcl>UPDATE employees
2 set salary = salary*1.1
3 WHERE employee_id = 107;
1 row updated.
HR@orcl>select * from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
208 Jand Harris Jaharris 03-APR-18
SA_REP 1000 0 107 80
210 bloe blarris yauaoo 03-APR-18
SA_REP 1000 0 107 80
211 David Smith DASMITH 04-APR-18
SA_REP 1000 0 107 80
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-87
AD_ASST 5280 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-96
MK_MAN 15600 100 20
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
202 Pat Fay PFAY 603.123.6666 17-AUG-97
MK_REP 7200 201 20
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-94
AC_MGR 14400 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-94
AC_ACCOUNT 9960 205 110
100 Steven Smith SKING 515.123.4567 17-JUN-87
SA_REP 7000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89
AD_VP 24633 100 90
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93
AD_VP 26979 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-90
IT_PROG 10800 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-91
IT_PROG 7200 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-99
SA_REP 5544 0 103 60
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-99
ST_MAN 6960 100 50
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-95
ST_CLERK 4200 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-97
ST_CLERK 3720 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-98
ST_CLERK 3120 124 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-98
ST_CLERK 3000 124 50
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-00
SA_MAN 12600 .2 100 80
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-96
SA_REP 13200 .3 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-98
SA_REP 12487.2 .2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-99
SA_REP 8400 .15 149
450 gwon hyejin koongdori 88570735 06-APR-18
IT_PROG 5000
24 rows selected.
HR@orcl>set linesize 120
HR@orcl>/
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
208 Jand Harris Jaharris 03-APR-18
SA_REP 1000 0 107 80
210 bloe blarris yauaoo 03-APR-18
SA_REP 1000 0 107 80
211 David Smith DASMITH 04-APR-18
SA_REP 1000 0 107 80
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-87
AD_ASST 5280 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-96
MK_MAN 15600 100 20
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
202 Pat Fay PFAY 603.123.6666 17-AUG-97
MK_REP 7200 201 20
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-94
AC_MGR 14400 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-94
AC_ACCOUNT 9960 205 110
100 Steven Smith SKING 515.123.4567 17-JUN-87
SA_REP 7000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89
AD_VP 24633 100 90
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93
AD_VP 26979 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-90
IT_PROG 10800 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-91
IT_PROG 7200 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-99
SA_REP 5544 0 103 60
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-99
ST_MAN 6960 100 50
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-95
ST_CLERK 4200 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-97
ST_CLERK 3720 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-98
ST_CLERK 3120 124 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-98
ST_CLERK 3000 124 50
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-00
SA_MAN 12600 .2 100 80
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-96
SA_REP 13200 .3 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-98
SA_REP 12487.2 .2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-99
SA_REP 8400 .15 149
450 gwon hyejin koongdori 88570735 06-APR-18
IT_PROG 5000
24 rows selected.
HR@orcl>show pagesize 16
pagesize 20
SP2-0158: unknown SHOW option "16"
HR@orcl>show pagesize
pagesize 20
HR@orcl>set pagesize 20
HR@orcl>/
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
208 Jand Harris Jaharris 03-APR-18
SA_REP 1000 0 107 80
210 bloe blarris yauaoo 03-APR-18
SA_REP 1000 0 107 80
211 David Smith DASMITH 04-APR-18
SA_REP 1000 0 107 80
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-87
AD_ASST 5280 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-96
MK_MAN 15600 100 20
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
202 Pat Fay PFAY 603.123.6666 17-AUG-97
MK_REP 7200 201 20
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-94
AC_MGR 14400 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-94
AC_ACCOUNT 9960 205 110
100 Steven Smith SKING 515.123.4567 17-JUN-87
SA_REP 7000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89
AD_VP 24633 100 90
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93
AD_VP 26979 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-90
IT_PROG 10800 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-91
IT_PROG 7200 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-99
SA_REP 5544 0 103 60
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-99
ST_MAN 6960 100 50
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-95
ST_CLERK 4200 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-97
ST_CLERK 3720 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-98
ST_CLERK 3120 124 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-98
ST_CLERK 3000 124 50
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-00
SA_MAN 12600 .2 100 80
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-96
SA_REP 13200 .3 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-98
SA_REP 12487.2 .2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-99
SA_REP 8400 .15 149
450 gwon hyejin koongdori 88570735 06-APR-18
IT_PROG 5000
24 rows selected.
HR@orcl>update employees
2 set salary = salary *1.1
3 where job_id = 'SA_REP';
8 rows updated.
HR@orcl>SELECT * FROM EMPLOYEES;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
208 Jand Harris Jaharris 03-APR-18
SA_REP 1100 .05 107 80
210 bloe blarris yauaoo 03-APR-18
SA_REP 1100 .05 107 80
211 David Smith DASMITH 04-APR-18
SA_REP 1100 .05 107 80
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-87
AD_ASST 5280 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-96
MK_MAN 15600 100 20
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
202 Pat Fay PFAY 603.123.6666 17-AUG-97
MK_REP 7200 201 20
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-94
AC_MGR 14400 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-94
AC_ACCOUNT 9960 205 110
100 Steven Smith SKING 515.123.4567 17-JUN-87
SA_REP 7700 0 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89
AD_VP 24633 100 90
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93
AD_VP 26979 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-90
IT_PROG 10800 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-91
IT_PROG 7200 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-99
SA_REP 6098.4 .05 103 60
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-99
ST_MAN 6960 100 50
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-95
ST_CLERK 4200 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-97
ST_CLERK 3720 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-98
ST_CLERK 3120 124 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-98
ST_CLERK 3000 124 50
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-00
SA_MAN 12600 .2 100 80
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-96
SA_REP 14520 .35 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-98
SA_REP 13735.92 .25 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-99
SA_REP 9240 .2 149
450 gwon hyejin koongdori 88570735 06-APR-18
IT_PROG 5000
24 rows selected.
HR@orcl>get in_job_history
1 CREATE OR REPLACE TRIGGER in_job_history
2 AFTER UPDATE OF job_id ON employees
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO JOB_HISTORY
6 VALUES(:OLD.employee_id, :OLD.hire_date, SYSDATE,:OLD.job_id, :OLD.department_id);
7* end;
HR@orcl>CREATE OR REPLACE TRIGGER check_salary
2 FOR INSERT ON UPDATE OF salary, job_id
3 ON employees
4 WHEN (NEW.job_id <> 'AD_PRES')
5 COMPOUND TRIGGERㅑㄹ
6
7
8 /
FOR INSERT ON UPDATE OF salary, job_id
*
ERROR at line 2:
ORA-00903: invalid table name
HR@orcl>conn / as sysdba
Connected.
SYS@orcl>create table create_log
2 (name varchar2(20), execute_date DATE);
Table created.
SYS@orcl>CREATE OR REPLACE TRIGGER all_user_create
2 BEFORE create ON DATABASE
3 BEGIN
4 INSERT INTO create_log VALUES(USER,SYSDATE);
5 END;
6 /
Trigger created.
SYS@orcl>save all_user_create.sql
Created file all_user_create.sql
SYS@orcl>conn hr/hr
Connected.
HR@orcl>create table test(c1 number);
Table created.
HR@orcl>conn / as sysdba
Connected.
SYS@orcl>select * from create_log
2 ;
NAME EXECUTE_D
-------------------- ---------
HR 06-APR-18
1 row selected.
SYS@orcl>conn hr/hr
Connected.
HR@orcl>create table log_table
2 (name varchar2(20), log_date timestamp(2),
3 msg varchar2(10));
Table created.
HR@orcl>create or replace trigger logon_trig
2 after logon on schema
3 begin
4 insert into log_table values(user, sysdate, 'Log on');
5 end;
6 /
Trigger created.
HR@orcl>create or replace trigger logoff_trig
2 before logogg on schema
3 begin
4 insert into log_table values(user,sysdate,'LOG off');
5 end;
6 .
HR@orcl>ed
Wrote file afiedt.buf
1 create or replace trigger logoff_trig
2 before logogg on schema
3 begin
4 insert into log_table values(user,sysdate,'LOG off');
5* end;
HR@orcl>/
before logogg on schema
*
ERROR at line 2:
ORA-04072: invalid trigger type
HR@orcl>ed
Wrote file afiedt.buf
1 create or replace trigger logoff_trig
2 before logoff on schema
3 begin
4 insert into log_table values(user,sysdate,'LOG off');
5* end;
HR@orcl>/
Trigger created.
HR@orcl>save logoff_trig.sql
Created file logoff_trig.sql
HR@orcl>conn hr/hr
Connected.
HR@orcl>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@localhost ~]$ sqlplus hr/hr
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 6 16:16:30 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
HR@orcl>select * from log_table;
NAME LOG_DATE MSG
-------------------- --------------------------------------------------------------------------- ----------
HR 06-APR-18 04.16.23.00 PM LOG off
HR 06-APR-18 04.16.23.00 PM Log on
HR 06-APR-18 04.16.25.00 PM LOG off
HR 06-APR-18 04.16.30.00 PM Log on
HR@orcl>select text from user_trigger where name = 'LOGON_TRIG';
select text from user_trigger where name = 'LOGON_TRIG'
*
ERROR at line 1:
ORA-00942: table or view does not exist
HR@orcl>ED
Wrote file afiedt.buf
1 create or replace trigger logon_trig
2 after logon on schema
3 begin
4 insert into log_table values(user, sysdate, 'Log on');
5 end;
6* /
7
8 /
Warning: Trigger created with compilation errors.
HR@orcl>ed
Wrote file afiedt.buf
1 create or replace trigger logon_trig
2 after logon on schema
3 begin
4 insert into log_table values(user, sysdate, 'Log on');
5* end;
6 /
Trigger created.
HR@orcl>save logon_trig.sql
Created file logon_trig.sql
<연습문제 8장 미션 1>
CREATE OR REPLACE TRIGGER secure_dept
BEFORE INSERT OR UPDATE OR DELETE ON hr.departments
BEGIN
if (to_char(sysdate,'DY') IN ('FRI')) AND
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '03:00' AND '17:00') THEN
RAISE_APPLICATION_ERROR(-20500,'You may insert'||'into DEPARTMENTS table only during' ||'normal business hours');
end if;
end;
/
<연습문제 8장 미션 2>
min_sal_by_job 테이블의 minsalary보다 새로 받는 값이 적을 경우 min_sal_by_job 테이블의 최소급여를 변경하라
CREATE OR REPLACE TRIGGER upd_emp_sal
before insert or update of salary on employees
for each row
DECLARE
v_minsal min_sal_by_job.minsal%type;
begin
select minsal
into v_minsal
from min_sal_by_job
where job_id= :new.job_id;
if :new.salary < v_minsal then
update min_sal_by_job
set minsal =:new.salary
where job_id = :new.job_id;
end if;
end;
/