오라클 트리거(Oracle Trigger) Instead Of Trigger, 복합 뷰에 INSERT할때 대신 실행되는 Trigger, JAVA학원, 자바학원, SQL학원, 오라클학원, SQL교육, 오라클교육, 오라클동영상
http://ojc.asia/bbs/board.php?bo_table=LecOracle&wr_id=307
ojc.asia
https://www.youtube.com/watch?v=Ja6HupDK0Ow&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=12&t=4s
https://www.youtube.com/watch?v=dofAcgYofvA&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=8
오라클 PL/SQL
Instead Of Trigger
실습
Instead Of Trigger
- 여러 테이블로 이루어진 뷰에 DML이 가해질 때 대신 trigger를 동작시킬 수 있는데 이때 사용하는 것이 instead of trigger 이다.
drop table employee;
테이블이 삭제되었습니다.
drop table department;
테이블이 삭제되었습니다.
CREATE TABLE department (
deptno NUMBER PRIMARY KEY,
deptname VARCHAR2(20),
manager_num NUMBER
);
CREATE TABLE employee (
empno NUMBER PRIMARY KEY,
empname VARCHAR2(20),
deptno NUMBER REFERENCES department(deptno),
startdate DATE
);
CREATE OR REPLACE VIEW manager_info AS SELECT d.deptno, d.deptname, e.empno, e.empname
FROM employee e, department d
WHERE e.empno = d.manager_num;
CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
FOR EACH ROW
DECLARE
employeeCount NUMBER;
BEGIN
SELECT COUNT(*) INTO employeeCount
FROM employee e
WHERE e.deptno = :new.deptno;
IF employeeCount >= 1 THEN
UPDATE department d
SET manager_num = :new.empno
WHERE d.deptno = :new.deptno;
insert into employee (empno, empname, deptno, startdate)
values (:new.empno, :new.empname, :new.deptno, sysdate);
ELSE
insert into department(deptno, deptname, manager_num)
values (:new.deptno, :new.deptname, 0);
insert into employee (empno, empname, deptno, startdate)
values (:new.empno, :new.empname, :new.deptno, sysdate);
END IF;
END;
/
INSERT INTO manager_info VALUES (11,'SALES',1004,'ANGEL');
1 개의 행이 만들어졌습니다.
INSERT INTO manager_info VALUES (11,'SALES',1005,'SUJI');
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> SELECT * FROM department;
DEPTNO DEPTNAME MANAGER_NUM
---------- -------------------- -----------
11 SALES 1005
SQL> SELECT * FROM employee;
EMPNO EMPNAME DEPTNO STARTDAT
---------- -------------------- ---------- --------
1004 ANGEL 11 16/05/01
1005 SUJI 11 16/05/01
SQL> INSERT INTO manager_info VALUES (21,'RND',1006,'SMITH');
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM department;
DEPTNO DEPTNAME MANAGER_NUM
---------- -------------------- -----------
11 SALES 1005
21 RND 0
SQL> SELECT * FROM employee;
EMPNO EMPNAME DEPTNO STARTDAT
---------- -------------------- ---------- --------
1004 ANGEL 11 16/05/01
1005 SUJI 11 16/05/01
1006 SMITH 21 16/05/01
SQL> SELECT * FROM manager_info;
DEPTNO DEPTNAME EMPNO EMPNAME
---------- -------------------- ---------- --------
11 SALES 1005 SUJI
#오라클트리거, #Trigger, #ORACLE, #오라클동영상, #트리거동영상, #SQL동영상, #SQL교육, #SQL학원, #오라클학원,#ORACLE학원, #자바학원, #JAVA학원, 오라클트리거, Trigger, ORACLE, 오라클동영상, 트리거동영상, SQL동영상, SQL교육, SQL학원, 오라클학원,ORACLE학원, 자바학원, JAVA학원,
