Neo-Oracle Home

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

비밀번호를 변경하고 싶은데 평소에 이 유저에 접속하는 다른 머신을 확인하고 싶을 수가 있다.

sys나 system으로 진행

1. 테이블 생성

2. 트리거 생성

이 트리거를 걸어둬도 유저가 dba 권한을 가지고 있으면 동작하지 않는다.

허용하는 ip가 아니면 해당 유저가 접속할 수 없도록 하는 예시

create or replace TRIGGER SYSTEM.LOGON_ACL
after logon
on database
WHEN (
    user='유저명'
)
declare
v_ip varchar(32);
BEGIN
    select sys_context('USERENV', 'IP_ADDRESS') into v_ip from dual;
    IF (v_ip not in ('xx.xx.xx.xx','xx.xx.xx.xx')) THEN
         RAISE_APPLICATION_ERROR(-20000, 'Access denied! You don"t have permission to login!');
    ELSIF (v_ip is null) then                                                    --db서버에서 sqlplus 방지
         RAISE_APPLICATION_ERROR(-20000, 'Access denied! You don"t have permission to login!');
    END IF;
END;

아니면 이렇게도 가능하다

create or replace TRIGGER SYSTEM.LOGON_ACL
after logon
on 유저명.schema

declare
v_ip varchar(32);
BEGIN
    select sys_context('USERENV', 'IP_ADDRESS') into v_ip from dual;
    IF (v_ip not in ('xx.xx.xx.xx','xx.xx.xx.xx')) THEN
         RAISE_APPLICATION_ERROR(-20000, 'Access denied! You don"t have permission to login!');
    ELSIF (v_ip is null) then                                                    --db서버에서 sqlplus 방지
         RAISE_APPLICATION_ERROR(-20000, 'Access denied! You don"t have permission to login!');
    END IF;
END;