티스토리 뷰
C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin glogin.sql |
● 정의
어떤 이벤트가 발생하면 자동으로 변경되도록 하기 위해서 사용합니다.
INSERT, UPDATE, DELETE가 실행되면 트리거가 실행된다.
-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] <method_specifier> ::= assembly_name.class_name.method_name
----------------------------------------------------------------------------------------------------------------------------------------
-- trigger sample : 급여 인상에 따른 알림 트리거
set serveroutput on
create or replace trigger start_trig
after update
on emp01
begin
DBMS_OUTPUT.PUT_LINE('급여가 10% 인상되었습니다. ');
end;
/
update emp01 set sal = sal*1.1;
/
-- trigger sample(for each row) : 급여인상에 따른 트리거
set serveroutput on
create or replace trigger start_trig
after update
on emp01
for each row
begin
DBMS_OUTPUT.PUT_LINE('급여가 10% 인상되었습니다. ');
end;
/
update emp01 set sal = sal*1.1;
/
● FOR EACH ROW
NEW.컬럼이름
INSERT : NEW
UPDATE : NEW, OLD
DELTE : OLD
- 단순 메시지 출력하는 트리거 작성
-- 테이블 생성
create table emp01(
empno number(4) primary key,
ename varchar2(20),
job varchar2(20))
/
/
------------------------------------------------------------------------------------------------------------------------
-- 급여 정보를 자동으로 추가하는 트리거 작성하기
create table sal01(
salno number(4) primary key,
sal number(7,2),
empno number(4) references emp01(empno)
);
-- 시퀀스 선언
create sequence sal01_salno_seq;
create or replace trigger trg_02
after insert
on emp01
for each row
begin
insert into sal01 values(sal01_salno_seq.nextval, 100, :new.empno);
-- 새로운 데이터 값은 ':new.컬럼이름'에 저장
end;
/
insert into emp01 values(3, '전수빈','프로그래머');
select * from emp01;
select * from sal01;
탄탄
create table dept01 as select * from dept;
create table tamp as select * from dept where 0=1;
create or replace trigger backup_dept
after update or delete
on dept01
for each row
begin
-- 같은 테이블 접근시 에러..
-- insert into tamp select * from dept01 where deptno = :old.deptno;
insert into tamp select * from dept where deptno = :old.deptno;
-- 새로운 데이터 값은 ':new.컬럼이름'에 저장
end;
select * from dept01;
select * from tamp;
/
------------------------------------------------------------------------------------------
- 업데이트 및 삭제시 temp 테이블에 저장하기
create table dept01 as select * from dept;
create table temp as select * from dept where 0=1;
alter table tamp add status varchar2(20);
create or replace trigger backup_dept
after update or delete or insert
on dept01
for each row
begin
if inserting then
insert into temp(deptno, dname, loc, status) values(:new.deptno,:new.dname,:new.loc, '추가');
elsif deleting then
insert into temp(deptno, dname, loc, status) values(:old.deptno,:old.dname,:old.loc, '삭제');
elsif updating then
insert into temp(deptno, dname, loc, status) values(:old.deptno,:old.dname,:old.loc, '갱신');
end if;
end;
insert into dept01 values(50, 'B반', '강남');
update dept01 set loc = '서울' where deptno=10;
delete dept01 where deptno=10;
------------------------------------------------------------------------------------------------
--입고 트리거
create table 상품(
상품코드 varchar2(6) primary key,
상품명 varchar2(12) not null,
제조사 varchar2(12),
소비자가격 number(8),
재고수량 number default 0
);
create table 입고(
입고번호 number(6) primary key,
상품코드 char(6) references 상품(상품코드),
입고일자 date default sysdate,
입고수량 number(6),
입고단가 number(8),
입고금액 number(8)
);
insert into 상품(상품코드, 상품명, 제조사, 소비자가격) values('A00001','세탁기','LG',500);
insert into 상품(상품코드, 상품명, 제조사, 소비자가격) values('A00002','컴퓨터','LG',700);
insert into 상품(상품코드, 상품명, 제조사, 소비자가격) values('A00003','냉장고','삼성',600);
create or replace trigger 입고트리거
after update or delete or insert
on 입고
for each row
begin
if inserting then
update 상품 set 재고수량 = 재고수량 + :new.입고수량 where 상품코드 = :new.상품코드;
elsif updating then
update 상품 set 재고수량 = (재고수량 - :old.입고수량 ) + :new.입고수량 where 상품코드 = :old.상품코드;
elsif deleting then
update 상품 set 재고수량 = 재고수량 - :old.입고수량 where 상품코드 = :old.상품코드;
end if;
end;
create table temp as select * from gradecard where 1=0;
create or replace trigger gradecard_trigger
before insert
on GRADECARD
for each row
declare
v_grade gradecard.grade%type;
begin
:new.tot := :new.kor + :new.eng + :new.mat;
:new.avg := :new.tot/3;
select grad into :new.grade from grade where :new.avg <= hiscore and :new.avg >= lowscore;
end;
--컴파운드 방법
create or replace trigger gradecard_trigger
after insert
on GRADECARD
for each row
declare
gradecard gradecard%rowtype;
begin
gradecard.tot := :new.kor + :new.eng + :new.mat;
gradecard.avg := :new.tot/3;
select grad into gradecard.grade from grade where :new.avg <= hiscore and :new.avg >= lowscore;
end for each row;
after statement is
begin
end after statement;
---
SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER GRADECARD_COMPOUND_TRIGGER
FOR INSERT ON GRADECARD
COMPOUND TRIGGER
GRADECARD_TABLE GRADECARD%ROWTYPE;
AFTER EACH ROW IS
BEGIN
-- 채워 보세요
DBMS_OUTPUT.PUT_LINE('값 저장');
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
-- 채워 보세요.
DBMS_OUTPUT.PUT_LINE('한 개의 데이터가 삽입되었습니다.');
END AFTER STATEMENT;
END GRADECARD_COMPOUND_TRIGGER;