농부지기

[ MS SQL.Trigger - 예제2 ]

변수 정의해서 INSERTED 와 DELETED 를 SELECT 한 예제

CREATE TRIGGER TRG_TBL_MICHAK_INST ON TBL_MICHAK

FOR INSERT AS

DECLARE

    @Old_in_amt                     DECIMAL(10),

    @Comp_amt                      DECIMAL(10),

    @d_in_numb                     CHAR(11),

    @d_prat_row_id               DECIMAL(12),

    @i_item_dist_unit            DECIMAL(10)

SELECT @d_in_numb = IN_NUMB,   @d_prat_row_id = PRAT_ROW_ID

FROM   DELETED;

SELECT @i_item_dist_unit = ITEM_DIST_UNIT

FROM   INSERTED;

SELECT @TS_PURC_NUMB = PURC_NUMB,      @TS_IN_NUMB = IN_NUMB,

FROM   TBL_INPUT_DETA

WHERE  PRAT_ROW_ID = @d_prat_row_id;

SELECT @Comp_amt  = @Old_in_amt - @d_item_dist_unit + @i_item_dist_unit ;

UPDATE TBL_INPUT_DETA

SET    PRC   = @Comp_unit,        AMT   = @Comp_amt

WHERE  IN_NUMB       = @d_in_numb

AND    PRAT_ROW_ID = @d_prat_row_id ;

IF @d_cost_gubn = '5‘ OR @d_cost_gubn = ‘7’

   begin

      UPDATE TBL_PRATIQUE_DETAIL

      SET    DIST_QTY  = DIST_QTY  - @d_this_dist_qty + @i_item_dist_unit

      WHERE  PRAT_NO   = @d_prat_no

      AND    CODE_ITEM  = @d_code_item

   end   

ELSE

   begin

       …

   end

Print ‘난 Trigger 문이야….’

정의 :

- @ : 변수 정의 시 @를 붙여야 한다. 그리고 사용시에도 항상 @를 붙인다.

- SELECT 문법이 조금 변경 되었음. (INTO 문장이 없다)

- 계산식에도 SELECT 를 앞쪽에 기입해야 한다.

 -문장을 기입시         begin

                                            …….

                                     end 를 두어야 한다.

- 문장 끝에   ; (콜론)을 넣이 않아도 된다.

- if 문장에서 then , end if   라는 예약어가 없다.

- print 문을 사용 하면 Query Analyzer 를 사용시에는 흐름을 볼 수 있지만

  Application에서 작업시는 Error 로 인식 한다 (ROLLBACK 됨)

- DataWindow로 UPDATE() function 을 사용해서 저장시 Print 문을 만나면

  다음과 같이 Error 로 인식한다.

[ MS SQL.Trigger - 예제1 ]

1.

CREATE TRIGGER TRG_TBL_BL_DETAIL_INST ON TBL_BL_DETAIL

FOR INSERT AS

IF  NOT UPDATE(BL_QTY)   RETURN

DECLARE @tmp_qty    DECIMAL(10),

                   @tmp_num  DECIMAL(10)

SELECT @tmp_qty = 10;

SELECT @tmp_num = 20;

UPDATE TBL_OFFER_DETAIL

SET    ALL_BL_QTY = ALL_BL_QTY + I.BL_QTY + @tmp_qty + @tmp_num

FROM   TBL_OFFER_DETAIL A, INSERTED I

WHERE  A.OFFER_NO   = I.OFFER_NO

AND    A.CODE_ITEM    = I.CODE_ITEM

2.

CREATE TRIGGER TRG_TBL_BL_DETAIL_UPDT ON TBL_BL_DETAIL

FOR UPDATE AS

UPDATE TBL_OFFER_DETAIL

SET    ALL_BL_QTY = ALL_BL_QTY - D.BL_QTY + I.BL_QTY

FROM   TBL_OFFER_DETAIL A, INSERTED I,  DELETED D

WHERE  A.OFFER_NO   = I.OFFER_NO

AND    A.CODE_ITEM    = I.CODE_ITEM

AND    A.OFFER_NO      = D.OFFER_NO

AND    A.CODE_ITEM    = D.CODE_ITEM

정의 :

- DELETED 와 INSERTED 라는 가상테이블과 물리적으로 존재 하는 테이블

  간에 JOIN 으로 문장을 구성 할 수 있다.

-. IF NOT UPDATE(BL_QTY) RETURN : 이란 BL_QTY 수량이 변경이

   되지 않았을 경우에는 RETURN 해라는 뜻.

   즉, Trigger 를 더 이상 작동 하지 말라는 문장이다.

[ MS SQL.Trigger - 종류 ]

1. 중첩 트리거 : 트리거가 동작해서 다른 테이블의 값을 변경한다면 이런 동작을 CASCASE INSERT(또는 UPDATE, DELETE) 라고 한다.

                            이렇게 CASCADE 동작을 할 때 대상이 되는 테이블에 또 트리거가 걸려 있다면 이 트거리는 도 동작 하게 된다.

                            이런식으로 중첩 트리거는 SQL7.0에서 32단계 까지 동작 할 수 있다.

    - 중첩트리거 중지 :  sp_configure ‘nested trigger’, 0                       à 1로 해주면 실행 됨

                                       GO

                                       reconfigure

                                       GO

2. 재귀 트리거(Recursive Trigger) : 트리거가 자기 자신을 다시 부르는 것을 재귀 트리거라고 한다. 이때 무한 루프에 빠지지 않도록 빠져 나오는 루틴을

                             처리해야만 한다. 재귀트리거는 default 가 off 되어 있다.

                             - on :   exec sp_dboption ‘db_name’, ‘recursive triggers’, true

                             - 재귀 트리거 빠져 나오기 : @@nestlevel system 변수 사용

                             - 예)  CREATE   TRIGGER   trgInsert on t3  for insert as

                                      set nocount on

                                     declare @id int,    @level int;

                                     select @id = object_id(‘trgInsert’)

                                     select @level = trigger_nestlevel(@id)

                                     if @level > 5

                                        begin

                                             set nocount off

                                             return

                                        end

                                     ……..

                                     set nocount off

* 트리거가 계속 진행시 32단계 까지 진행 후 SQL 서버가 취소를 시키고 모든 트랜잭션이 취소된다..   

[ MS SQL.Trigger - 특징 ]

1. 숨어있는 두 테이블 : INSERTED 와 DELETED 라는 두 논리적 테이블, 임시적 저장 공간, 논리적 저장 공간, 가상테이블에 자료를 가지고 있게 된다.

                                         (임시적이란, 트랜잭션이 끝나면 없어진다는 뜻,  논리적이란 디스크 상에 존재하는 테이블이 아니라는 뜻)

    - INSERTED  : 새로 삽입된 행을 저장             

    - DELETED   : 삭제된 행을 저장

   : 트랜잭션이 끝날 때 까지 자료가 남아 있다.

2. 테이블이 UPDATE 가 되면 실제로는 해당 자료를 Delete 하고 Insert 하게 된다.

    그래서 UPDATE 시는  INSERTED 와 DELETED 가 동시에 존재 한다.

[ MS SQL.Trigger - 해법 ]

1. SYSOBJECTS 테이블은 table, Trigger, sp 등의 정보가 입력 되어 있는 테이블 이다.

                              Type ( U:User Table,   V:View,   TR:Trigger, K:Index,   S:system Table,     P : Stored Procedure ..)

2. WITH ENCRYPTION : 소스 코드의 암호화 ( Enterprise Manager 에서 보게 되면 알 수 없는 코드로 조회 된다.)

3. FOR  [DELETE], [INSERT], [UPDATE]  : 각 작업시 Trigger 가 동작 된다.

4. IF UPDATE (column_name)                      : 해당 column_name 이 변경시 다음문장이 동작 된다.

[ MS SQL.Trigger - 문법 ]

- 존재시 삭제 : IF EXISTS ( SELECT NAME FROM SYSOBJECTS

                                                WHERE  NAME = ‘ TRG_TBL_LC_DETAIL_INST' AND TYPE = 'TR' )

                               DROP TRIGGER TRG_TBL_LC_DETAIL_INST

                          GO

 - 생성 :   CREATE TIRGGER  trigger_name

                ON TABLE

                [WITH ENCRYPTION]

                {  

                     { FOR { [DELETE], [INSERT] [,] [UPDATE] }

                AS

                IF UPDATE (column_name )

                    [ { AND | OR } UPDATE ( column_name ) … ]

               DECLARE   @variable_name    type ;

                sql_statement [ …. N ]

- 수정 :  ALTER TRIGGER

- 삭제 :  DROP TRIGGER

- 보기 : sp_helptext       tirgger_name

[ MS SQL.Trigger - 단점 ]

1. Client Source 만으로 bug 를 잡을 수 없다.

2. 다른 사람이 트리거를 만들 었을 경우 문서가 없으면 존재 하는지 모를 수 있다. ( 그래서 필히 트리거에 대한 문서화를 해야만 한다)

3. 큰 프로젝트를 진행시 트리거를 너무 많이 사용하게 되면 예기치 못한 결과도 초래 할 수 있다. (중첩트리거 때문에….)

[ MS SQL.Trigger - 장점 ]

1. Check 제약등으로 구현하기 힘든 것들을 트리거로 구현.

2. Network Traffic 이 없다.

3. 차후 Maintenance 를 할 경우 Application 은 수정 할 필요 없이 Trigger 만 수정 하면 된다.

    이렇게 되면 실행 Module 을 각 사용자에게 Copy 해줄 필요가 없다. Trigger 는 Server 에서 작동 하기 때문이다.

4. Application 작성시 코딩이 쉬워 진다.

[ MS SQL.Trigger - 특징 ]

1. 한 테이블에 어떤 값이 입력 되었을 때 자동적으로 다른 테이블의 데이터를 변경 해야 할 필요가 있을 경우 사용.

     과거에는 클라이언트에서 프로그램으로 처리 했지만, RDBMS 에서는 트리거를 사용한다.

2. Stored Procedure의 특별한 형태이다.

3. MS sql Server 6.5에서는 한 테이블당 최대 3개의 트리거

4. MS sql Server 7.0에서는 한 테이블당 제한이 없다.

6. CHECK, DEFAULT, RULE 등과는 달리 트랜잭션이 시작되기 전이 아니라, 트랜잭션이 시작된 후에 동작하는 무결성 구현 개체이다. 즉, 트랜잭션의 부분이다.

7. 한 INSERT에 대해서 트리거는 한번만 실행 된다. 즉, 여러행이 한 번에 INSERT 될 때 (INSERT… SELECT 의 경우)도 트리거는 한번만 수행 된다.

   (Oracle 은 그렇지 않다)

8. 트리거는 모든 변경문 (Insert, Update, Delete)이 정상적으로 끝나야만 그때 발생된다.

9. 트리거는 호출되지 않는다.  (즉, Event  이기 때문이다)