데이터베이스 질문과 답변
이번에 작업을 하다가 트리거로 작업해야 할게 생겨서 이렇게 문의 드립니다.
A_table 테이블에 A_table.aa_column, A_table.bb_column, A_TABLE.cc_column 이 있고
이 테이블에 insert or update 로 값이 들어갈때
A_table.bb_column 컬럼에 값이 들어가면 삭제하려고 하는게 목적인데
이걸 어떻게 해야할지....여기저기 찾아보긴 했는데, 찾아보니 다들 insert or update 시에
다른 테이블에 insert를 한다거나 exception을 띄운다거나 정도로만 나오더라구요.
트리거 생성할때 문법 찾아보고 해서 하는데 계속 에러나서
--------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER row_delete_trigger
A_table 테이블에 A_table.aa_column, A_table.bb_column, A_TABLE.cc_column 이 있고
이 테이블에 insert or update 로 값이 들어갈때
A_table.bb_column 컬럼에 값이 들어가면 삭제하려고 하는게 목적인데
이걸 어떻게 해야할지....여기저기 찾아보긴 했는데, 찾아보니 다들 insert or update 시에
다른 테이블에 insert를 한다거나 exception을 띄운다거나 정도로만 나오더라구요.
트리거 생성할때 문법 찾아보고 해서 하는데 계속 에러나서
--------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER row_delete_trigger
AFTER INSERT OR UPDATE ON A_table
FOR EACH ROW
BEGIN
UPDATE A_table SET bb_column = '123' WHERE aa_column = :NEW.aa_column;
END row_delete_trigger;
/
--------------------------------------------------------------------------------------------------------------------------
생성만 하면 생성할때는 "오류와 함께 트리거가 생성되었습니다." 라고만 나오고
임의데이터로 INSERT 해보면 "ORA-04098: 'A_table' 트리거가 부적합하며 재검증을 실패했습니다"
제가 원하는 방식이 가능한지나, 어느부분이 틀린건지 확인좀 부탁드리겠습니다.
--------------------------------------------------------------------------------------------------------------------------
생성만 하면 생성할때는 "오류와 함께 트리거가 생성되었습니다." 라고만 나오고
임의데이터로 INSERT 해보면 "ORA-04098: 'A_table' 트리거가 부적합하며 재검증을 실패했습니다"
제가 원하는 방식이 가능한지나, 어느부분이 틀린건지 확인좀 부탁드리겠습니다.
by Oracler
[2012.11.30 23:28:59]
오류가 나는 이유는 트리거가 설정된 테이블 - 여기선 A_table이죠 - 과 그 트리거에서 실행되는 쿼리 및 DML의 재료집합 - 여기서도 A_table입니다 - 이 같기 때문입니다. 이때 A_table을 Mutating Table이라고 합니다.
이러한 경우 에러가 나는 이유는 A_table의 데이터 - 즉, 로우죠 - 를 수정(DML)하는 INSERT나 UPDATE 문에 의해 트리거가 수행되는데 그 트리거에서 또다시 UPDATE, INSERT, DELETE의 대상이 되는 A_table의 데이터가 트리거를 발생시킨 DML이 시작되기 직전의 그 값들로 그대로 있을 수도 있고 이미 몇 번의 행 트리거에 의해 데이터가 변경될 수도 있는데 이러면 실제로 업데이트되는 값이 그때 그때 달라질 수가 있게 됩니다.
다시 말해 일관된 결과가 나타날 수가 없게 되죠. 그래서 Mutating Table 상태가 나타나면 아예 트리거가 컴파일될 수 없게 에러를 발생시키는 것입니다.
이러한 경우 에러가 나는 이유는 A_table의 데이터 - 즉, 로우죠 - 를 수정(DML)하는 INSERT나 UPDATE 문에 의해 트리거가 수행되는데 그 트리거에서 또다시 UPDATE, INSERT, DELETE의 대상이 되는 A_table의 데이터가 트리거를 발생시킨 DML이 시작되기 직전의 그 값들로 그대로 있을 수도 있고 이미 몇 번의 행 트리거에 의해 데이터가 변경될 수도 있는데 이러면 실제로 업데이트되는 값이 그때 그때 달라질 수가 있게 됩니다.
다시 말해 일관된 결과가 나타날 수가 없게 되죠. 그래서 Mutating Table 상태가 나타나면 아예 트리거가 컴파일될 수 없게 에러를 발생시키는 것입니다.
by Oracler
[2012.11.30 23:36:49]
A_table.bb_column 컬럼에 값이 들어가면 삭제하는 것이 살짝 이해가 안되는데 NULL 값을 넣겠다는 뜻으로 해석하고 아래와 같은 트리거문을 작성해 보았습니다.
// INSERT, UPDATE 후에 값을 변경할 것이 아니라 그전에 값을 변경해 두고 나서
// 오라클이 실제 INSERT, UPDATE 작업 시 변경된 값으로 자연적으로
// 변경되게끔 해야 하기 때문에 BEFORE ROW TRIGGER로 구현해야 합니다.
CREATE OR REPLACE TRIGGER row_delete_trigger
BEFORE INSERT OR UPDATE ON A_table
FOR EACH ROW
BEGIN
:NEW.bb_column := NULL; -- :NEW 는 INSERT에서는 입력되는 레코드를
-- UPDATE에서는 새로운 레코드를 의미합니다
END row_delete_trigger;
/
by Oracler
[2012.11.30 23:44:02]
그런데 이 문제의 경우 트리거를 쓰는 것보다는 DML이 가능한 단순 뷰(simple view)를 만드는 것이 트리거를 사용하지 않아도 되므로 훨씬 효율적입니다. 트리거 - 특히 행 트리거 - 를 사용한다는 것은 DML을 할 때마다 해당 트리거가 수행되어야 하기 때문에 오버헤드 문제에서 자유롭지 못합니다.
아래와 같은 뷰를 생성한 다음에 테이블이 아닌 뷰를 통해서 INSERT, UPDATE를 하게 하면 bb_column 자체에 접근할 수 있는 방법이 없기 때문에 값이 입력되지 않게 됩니다.
그리고 이 뷰에 대해 아래와 같이 DML을 합니다.
그런 다음 기본 테이블인 A_table의 내용을 질의하면 bb_column엔 NULL 데이터가 들어가 있게 됩니다.
아래와 같은 뷰를 생성한 다음에 테이블이 아닌 뷰를 통해서 INSERT, UPDATE를 하게 하면 bb_column 자체에 접근할 수 있는 방법이 없기 때문에 값이 입력되지 않게 됩니다.
CREATE OR REPLACE VIEW A_table_vu AS SELECT aa_column, bb_column FROM A_table ;
그리고 이 뷰에 대해 아래와 같이 DML을 합니다.
INSERT INTO A_table_vu VALUES (xxx, yyy);
그런 다음 기본 테이블인 A_table의 내용을 질의하면 bb_column엔 NULL 데이터가 들어가 있게 됩니다.
by NanJangPaN
[2012.12.05 17:23:10]
댓글이 늦어져서 죄송합니다.
이런적인 부분부터 자세히 정리해주셔서 감사해요. 많은 도움이 되었습니다.
상황이 뷰를 쓰지 못하는 부분이라 부득이하게 트리거로 처리하게 되어서
설명 참조해서 좀더 공부하도록 하겠습니다. 수고하세요
이런적인 부분부터 자세히 정리해주셔서 감사해요. 많은 도움이 되었습니다.
상황이 뷰를 쓰지 못하는 부분이라 부득이하게 트리거로 처리하게 되어서
설명 참조해서 좀더 공부하도록 하겠습니다. 수고하세요
) 버튼을 클릭하여 작성 하시면 됩니다.