1. 테스트 테이블 생성
orcl@EXPERT> create table orders (order_id number primary key, line_items_count number default 0);
orcl@EXPERT> create table lineitems (order_id references orders, seq_no number, constraint lineitem_pk primary key (order_id,seq_no));
2. 트리거 생성
orcl@EXPERT> create or replace trigger orders_itemcnt_trg
after update or insert or delete on lineitems
for each row
begin
if updating or inserting then
update orders set line_items_count=nvl(line_items_count,0)+1
where order_id=:new.order_id;
end if; --여기서 아예 end if하면 아래 if가 안돌아가기에 end if 후 다른 if가 됨
if deleting or updating then
update orders set line_items_count=nvl(line_items_count,0)-1
where order_id=:old.order_id;
end if;
end;
/
3. Insert 테스트
orcl@EXPERT> insert into orders(order_id) values(10);
orcl@EXPERT> insert into orders(order_id) values(20);
orcl@EXPERT> insert into lineitems values(10,1);
orcl@EXPERT> select * from orders; --commit을 하지 않아도 트리거에 의해 update되었음
ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
10 1
20 0
orcl@EXPERT> insert into lineitems values (10,2);
--이때 트리거에 의해 update orders set line_item_count=nvl(1,0)+1 where order_id=10; 발생
orcl@EXPERT> select * from orders;
ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
10 2
20 0
4. update 테스트
orcl@EXPERT> update lineitems set order_id=20;
--이때 트리거에 의해 아래 과정을 거친다.
update order set line_item_count=nvl(2,0)-1 where order_id=10;
update order set line_item_count=nvl(0,0)+1 where order_id=20;
update order set line_item_count=nvl(1,0)-1 where order_id=10;
update order set line_item_count=nvl(1,0)+1 where order_id=20;
orcl@EXPERT> select * from orders;
ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
10 0
20 2
5. delete 테스트
orcl@EXPERT> delete from lineitems;
--이때 트리거에 의해 아래 과정을 거친다.
update order set line_item_count=nvl(2,0)-1 where order_id=20;
update order set line_item_count=nvl(1,0)-1 where order_id=20;
orcl@EXPERT> select * from orders;
ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
10 0
20 0
