DBMS/오라클] 오라클 트리거 작성 예

CREATE SEQUENCE 이름
[ INCREMENT BY n ]
[ START WITH n ]
[ {MAXVALUE n | NOMAXVALUE} ]
[ {MINVALUE n | NOMINVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ]
-----------------------------------------------------


SQL> CREATE [ PUBLIC ] SYNONYM 시노님 명
FOR 오브젝트 명 ;
-----------------------------------------------------


CREATE [OR REPLACE] TRIGGER trigger명
timing event1 [OR event2 ...] OF 칼럼명 ON 테이블명
[FOR EACH ROW]
PL/SQL 블록
-----------------------------------------------------

실예:]

CREATE OR REPLACE TRIGGER time_emp
BEFORE insert OR update OR delete ON s_emp
BEGIN
IF to_char(sysdate,'hh24') NOT BETWEEN '09' AND '12' THEN
raise_application_error(-20201,'시간이 끝났음');
END IF;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER up_emptemp
AFTER UPDATE OF SALARY ON s_emp
FOR EACH ROW
BEGIN
UPDATE EMPTEMP
SET salary =:NEW.salary
WHERE id = :OLD.id ;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER ord_total
AFTER INSERT OR DELETE OR
UPDATE OF price, quantity ON s_item
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE s_ord
SET total = NVL(total,0)+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
ELSIF DELETING THEN
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
WHERE id = :OLD.ord_id;
ELSE
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
END IF;
END;
/