블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

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
  • total
  • today
  • yesterday
2010. 11. 3. 11:24 SQL SERVER

트리거(TRIGGER)

데이타 베이스 서버에서 특정 이벤트의 결과로 자동으로 수행되는 루틴
트리거는 트랜잭션의 일부로 트리거가 완료 되어야 트랜잭션도 왼료된것이다.
트리거에서 롤백이 일어날경우 트리거뿐만 아니라 원 트랜잭션도 롤백시킴
트리거를 사용해서 무결성 강화를 하는 것보다 제약조건을 통해서 무결성
강화를 할 수 있다면 제약조건을 통해서 하는편이 더 좋음

AFTER 트리거
SQL 명령이 수행된 후에 실행되는 트리거

1.Permanent Table에서만 생성가능 (VIEW나 임시테이블에서는 불가능)
2.테이블에 정의된 제약조건이 변경또는 취소되면 트리거는 작동하지 않음
   실행시 행단위가 아닌 하나의 문장 단위로 호출
3.즉 쿼리가 실행되었을때 몇개의 행이 영향을 받았느냐에 관계없이 한번만 실행됨
4.여러개의 Trigger가 존재할 경우 Trigger 들은 순차적으로 하나씩 실행된다.
  처음과 마지막에 실행될 Trigger 는 sp_settriggerorder로정할수 있지만
  그 이외의 Trigger들은 순서를 정할 수 없다.

Inserted 와 deleted

새로 변경될 이미지와 삭제되기전 이미지를 가지고 있는 특수 테이블
테이블의 스키마는 Trigger가 정의된 테이블과 같은 구조를 가진다.

SQL2000에서는 트랜잭션로그를 이용한 VIEW였기 때문에 집중적인 Trigger 작업은 트랜잭션 로그의 병목현상을 유발할 수 있었음
SQL2005에서는 tempdb의 row versioning 으로 구현하여 트랜잭션 병목현상은 없으나 tempdb의 부하를 유발할 수 있다.

@@rowcount 를 이용해서 몇개의 행이 영향을 받았는지 확인하고 이에 따라 분기 처리가 가능하다.

Ex) CREATE TRIGGER TR_TEST ON TABLE1 AFTER INSERT
    AS
    (
    SET @rc = @@rowcount;
         if @rc = 0 --영향 받는 행이 없음
       RETURN
    if @rc = 1   --단일행
    begin
     작업처리
    end
   
    if @rc > 1   
    begin
      작업처리
    end
     )
CF) AFTER Trigger를 정의하기위해서는 AFTER 이외에도 FOR 를 사용할 수도 있다.

트리거 (비)활성화
Alter TABLE 테이블이름  ENABLE TRIGGER 트리거이름  --활성화
Alter TABLE 테이블이름  DISABLE TRIGGER 트리거이름 --비활성화


트리거의 중첩(Nested)및 재귀(Recursive)
중첩: 한트리거가 호출된 결과로 다른 트리거가 호출
재귀: 트리거가 자기자신을 직접하출 하든지 다른 트리거를 통해서 자기 자신이 계속 호출될때

재귀는 무한루프를 방지하기 위해서 32단계까지만 지원(32는하드코딩되어있음)한다 33번째 재귀호출을 할려고 하면 모든 작업이 롤백된다

UPDATE함수및 COLUMNS_UPDATED

1.Update함수
특정 column이 업데이트 되었는가를 확인할때 사용하는 방법
if Update(칼럼) --특정 칼럼 한개가 업데이트 되었는지 확인

2.COLUMNS_UPDATE 함수 다시 잘 볼것
각각의 칼럼을 bit로 가지는 이진문자열 반환 칼럼이 변경된경우 1 그렇지 않은경우 0 설정
칼럼의 순서는 왼쪽에서 오른쪽 가장왼쪽 byte는 처음8개의 칼럼 (1~8까지) 두번째는 그다음 8개의칼럼(9~16)
 

INSTEAD OF 트리거
대상 개체에 일어나야 할 원래의 변경작업 대신에 수행된다.(대상개체의 변경작업이 일어나지 않으므로 BEFORE 트리거가 아니다.)
INSTEAD OF 트리거에서 inserted나 deleted 테이블은 변경되어야 할 데이타를 담고 있다.
뷰에서도 생성이가능하고 재귀호출을 허용하지 않는다.


DDL트리거
데이타베이스에서 일어나는 DDL이벤트에 대해 반응
규칙에 맞지 않는 스키마의 변경을 롤백하거나 스키마의 변경을 감시할 수 있다.
(AFTER트리거만 지원)

posted by bedbmsguru