최종 트리거가 3번 트리거이다.
사원을 신규 등록할 때 employee 테이블의 reg_id 값이 education 테이블의 reg_id에도 똑같이 들어가도록 설정하였다.
--1)
CREATE OR REPLACE TRIGGER after_emp_insert_edu
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
-- 특정 e_no에 해당하는 education 레코드를 생성
INSERT INTO education (edu_no, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order, e_no, reg_date, mod_date, reg_id, mod_id)
VALUES (seq_edu_no.NEXTVAL, NULL, NULL, NULL, NULL, NULL, NULL, :NEW.e_no, SYSDATE, NULL, :NEW.e_no, NULL);
END;
/
--2) edu_order의 1,2,3을 각각 insert하여 총 3개의 새로운 레코드 생성
CREATE OR REPLACE TRIGGER after_emp_insert_edu
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
-- 특정 e_no에 해당하는 education 레코드를 생성
INSERT INTO education (edu_no, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order, e_no, reg_date, mod_date, reg_id, mod_id)
VALUES (seq_edu_no.NEXTVAL, NULL, NULL, NULL, NULL, NULL, 1, :NEW.e_no, SYSDATE, NULL, :NEW.e_no, NULL);
INSERT INTO education (edu_no, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order, e_no, reg_date, mod_date, reg_id, mod_id)
VALUES (seq_edu_no.NEXTVAL, NULL, NULL, NULL, NULL, NULL, 2, :NEW.e_no, SYSDATE, NULL, :NEW.e_no, NULL);
INSERT INTO education (edu_no, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order, e_no, reg_date, mod_date, reg_id, mod_id)
VALUES (seq_edu_no.NEXTVAL, NULL, NULL, NULL, NULL, NULL, 3, :NEW.e_no, SYSDATE, NULL, :NEW.e_no, NULL);
END;
/
--3) 현재 로그인한 사용자 정보를 가져와 reg_id에 삽입하기
CREATE OR REPLACE TRIGGER after_emp_insert_edu
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
-- employee 테이블에 신규로 등록된 행의 reg_id 값을 사용하여 education 테이블의 reg_id에도 똑같이 할당
INSERT INTO education (edu_no, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order, e_no, reg_date, mod_date, reg_id, mod_id)
VALUES (seq_edu_no.NEXTVAL, NULL, NULL, NULL, NULL, NULL, 1, :NEW.e_no, SYSDATE, NULL, :NEW.reg_id, NULL);
INSERT INTO education (edu_no, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order, e_no, reg_date, mod_date, reg_id, mod_id)
VALUES (seq_edu_no.NEXTVAL, NULL, NULL, NULL, NULL, NULL, 2, :NEW.e_no, SYSDATE, NULL, :NEW.reg_id, NULL);
INSERT INTO education (edu_no, edu_period, edu_name, edu_major, edu_status, edu_division, edu_order, e_no, reg_date, mod_date, reg_id, mod_id)
VALUES (seq_edu_no.NEXTVAL, NULL, NULL, NULL, NULL, NULL, 3, :NEW.e_no, SYSDATE, NULL, :NEW.reg_id, NULL);
END;
/