데이터베이스 질문과 답변

트리거 질문합니다. 0 7 5,963

by 대통이 [PL/SQL] 트리거 trigger [2013.12.09 19:18:48]

오라클 트리거 작성하려고 합니다.

우선 테이블 A에 날짜(DATE)가 있습니다.

이 테이블에 데이터를 추가 입력(INSERT) 하려고 합니다.

중복은 허용됩니다.

만약 새로 들어가는 날짜가 기존 날짜와 겹치면 '겹칩니다'라는 메시지 출력하려고 합니다.

필드명은 START_DATE 입니다.

사용한 방법1
CREATE OR REPLACE TRIGGER TEST
AFTER INSERT ON A
FOR EACH ROW

BEGIN
IF :NEW.START_DATE = :OLD.START_DATE THEN
DBMS_OUTPUT.PUT_LINE('겹칩니다.');
END IF;
END;
-- 위의 방법은 되질 않습니다. 비교를 못하고 있습니다.

사용한 방법2
CREATE OR REPLACE TRIGGER TEST
AFTER INSERT ON A
FOR EACH ROW

DECLARE 
 CDATE DATE;
BEGIN
SELECT START_DATE INTO CDATE FROM A WHERE START_DATE = :NEW.START_DATE;

IF CDATE = :NEW.START_DATE THEN
DBMS_OUTPUT.PUT_LINE('겹칩니다.');
ELSE 
DBMS_OUTPUT.PUT_LINE('겹치지 않습니다.');
END IF;
END;
--- 이상황에서는 새로운 데이터가 삽입이 되질 않습니다. 기존의 데이터와 중복이 되면 겹친다고 메시지가 뜨나 겹치지 않는 새로운 데이터가 들어갈때 에러가 납니다.
NO DATA FOUND가 뜹니다. 여기서 예외처리를 하면 원하는 메시지가 나오지만

만약 제가 ELSIF문 몇개를 추가하면 이것들은 적용을 받지 못하고 그냥 예외처리 문장이 뜨네요.

방법좀 알려주세요. 돌아버리겠네요.
by 임상준 [2013.12.09 20:15:30]

결론부터..

CREATE OR REPLACE TRIGGER TEST
BEFORE INSERT ON A
FOR EACH ROW

DECLARE
CDATE NUMBER ;

BEGIN
SELECT  COUNT(*) CNT INTO CDATE FROM A WHERE START_DATE = :NEW.START_DATE ;
 
IF CDATE > 0 then
DBMS_OUTPUT.PUT_LINE('겹칩니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('겹치지 않습니다.');
END IF;

END;

by 임상준 [2013.12.09 20:27:37]

처음 하신거..
지금 트리거는 행단위로 적용되므로
update 트리거라면 old 와 new 가 있겠지만 인서트 상황이면 old 는 없고 new 만 있을테니까
당연히 비교가 안될거구요

두번째꺼
만들어주신거 그대로 수행하면 ora-04091 에러가 납니다.
지금 수정 중에 있는 데이터를 트리거에서 또 참조한다는... 대강 그런 내용인데,
트리거가 after insert 상황에서 작동하니 select 절에서 발생합니다. 방금 집어 넣고 트렌젝션 중인 데이터가 = :NEW.START_DATE 조건에 걸리니까요.

insert 되기 전에 비교를 먼저 하고 조건 분기 처리해야 하므로 before insert 로 변경하시면 됩니다.

그 다음에  START_DATE = :NEW.START_DATE 인 데이터가 없는 상황이면 cdate 에 아무것도 담기지를 않으니 no data... 뭐라고 하는 에러가 뜹니다. count 로 위 상황 커버 해 주시면 됩니다.

by 마농 [2013.12.09 21:57:39]
뮤테이팅 에러는 before 로 바꾼다고 해결되지 않습니다.
만약 Insert 가 무조건 1건씩만 수행된다면?
트리거 안에서의 Select 가 가능 할 수도 있습니다.
그러나 Insert ~ Select 식의 2건이상 입력이라던가?
2건 이상 Update 가 발생된다면 뮤테이팅 에러를 피할 수 없습니다.
http://oracleclub.com/article/53504
by 임상준 [2013.12.10 08:49:28]

글쿤요,,, 멀티로우 인서트하는 케이스가 있었네요

by 마농 [2013.12.10 00:27:45]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- 0. 테이블 생성 --
CREATE TABLE test(dt VARCHAR2(8));
CREATE INDEX x01_test ON test(dt);
 
-- 0. 패키지에서 변경된 자료를 저장할 변수 정의 --
CREATE OR REPLACE PACKAGE pkg_test
IS
  TYPE type_dt IS TABLE OF test.dt%TYPE INDEX BY BINARY_INTEGER;
  v_dt type_dt;
  i BINARY_INTEGER;
END;
/
 
-- 1. 문장-트리거 에서 의 DML 실행전 패키지 변수 초기화 --
CREATE OR REPLACE TRIGGER tri_test_1
BEFORE INSERT ON test
BEGIN
  pkg_test.i := 0;
END;
/
 
-- 2. 행-트리거 에서 패키지 변수에 변경된 값을 저장 --
CREATE OR REPLACE TRIGGER tr_test_2
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  pkg_test.i := pkg_test.i + 1;
  pkg_test.v_dt(pkg_test.i) := :new.dt;
END;
/
 
-- 3. 문장-트리거 에서 의 DML 실행후 별도 DML 처리 --
CREATE OR REPLACE TRIGGER tr_test_3
AFTER INSERT ON test
DECLARE
  v_cnt NUMBER(1);
BEGIN
  FOR i IN 1..pkg_test.i LOOP
    SELECT COUNT(*)
      INTO v_cnt
      FROM test
     WHERE dt = pkg_test.v_dt(i)
       AND ROWNUM <= 2
    ;
    IF v_cnt = 2 THEN
      DBMS_OUTPUT.PUT_LINE(pkg_test.v_dt(i) || ' : 겹칩니다.');
    ELSE
      DBMS_OUTPUT.PUT_LINE(pkg_test.v_dt(i) || ' : 겹치지 않습니다.');
    END IF;
  END LOOP;
END;
/
 
-- 입력 테스트 --
INSERT INTO test VALUES('20130101');
20130101 : 겹치지 않습니다.
1 개의 행이 만들어졌습니다.
 
INSERT INTO test VALUES('20130102');
20130102 : 겹치지 않습니다.
1 개의 행이 만들어졌습니다.
 
INSERT INTO test VALUES('20130102');
20130102 : 겹칩니다.
1 개의 행이 만들어졌습니다.
by 대통이 [2013.12.11 17:35:32]
답변 채택이 하나 밖에 안되서 먼저 글올려 주신분꺼 했네요. 작성해주신 코드는 제가 공부가 좀 필요하네요. 감사합니다.
by 대통이 [2013.12.11 17:33:48]
소중한 답변들 감사합니다.
count(*) 이용해서 해결했구요.

뮤테이팅 에러는 예외처리했는데 편법이었네요. 

공부를 더해야 겠습니다.

감사합니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입