trigger

MS-SQL 트리거

데이터베이스 서버에서 이벤트가 발생하면 자동으로 실행되는 특수한 종류의 저장 프로시저.

예를 들어 사원 출입권한 테이블에 추가,수정 및 삭제 이벤트가 발생하면 연관된 사원 테이블에서 해당 사원의 마지막 수정시간이 자동으로 업데이트되도록 다른 테이블에 추가 동작을 지시할 수 있는 기능이라고 볼 수 있다.

트리거는 아래와 같이 다양한 경우에서 사용된다.

• 테이블에 입력되는 데이터의 무결성을 검사하거나 INSERT,UPDATE,DELETE와 같은 테이블 이벤트에 대한 추가 작업을 수행하기 위해
• CREATE,ALTER,DROP 과 같은 데이터베이스 시스템의 이벤트에 대해 추가 작업을 수행하기 위해

첫번째로 DML Trigger(INSERT,UPDATE,DELETE 이벤트에 대한 트리거) 사용 방법에 대해 작성한다.

우선 create trigger의 syntax는 아래와 같다.

CREATE TRIGGER [schema_name.]trigger_name   -- schema_name: 트리거가 속하는 스키마이름 으로 생략가능
ON table_name                               -- table_name: 트리거가 적용되는 테이블 이름
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]                       -- 복제 프로세스중에는 트리거가 수행되지 않도록 하는 옵션
AS
{sql_statements}

Trigger 예제

트리거 생성 예시를 위해 아래와 같이 production.products라는 테이블을 생성했다고 가정한다.

CREATE TABLE production.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DEC(10,2) NOT NULL
);

그리고 production.products 테이블의 INSERT, DELETE 이벤트에 대한 로그를 남기기 위한 테이블 production.product_audits를 아래와 같이 추가로 생성한다.

CREATE TABLE production.product_audits(
    change_id INT IDENTITY PRIMARY KEY,    -- 수정사항 기록을 위한 컬럼
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DEC(10,2) NOT NULL,
    updated_at DATETIME NOT NULL,           -- 수정사항 기록을 위한 컬럼
    operation CHAR(3) NOT NULL,             -- 수정사항 기록을 위한 컬럼
    CHECK(operation = 'INS' or operation='DEL')
);

마지막으로 트리거(DML trigger)를 추가한다.

CREATE TRIGGER production.trg_product_audit
ON production.products
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;     -- 트리거 영향을 받은 행 수를 나타내는 메시지를 막는 옵션
    INSERT INTO
        production.product_audits
            (
                product_id,
                product_name,
                brand_id,
                category_id,
                model_year,
                list_price,
                updated_at,
                operation
            )
    SELECT
        i.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted AS i           -- inserted: sql server 내부에서 trigger 수행시 사용되는 가상의 테이블
    UNION ALL
        SELECT
            d.product_id,
            product_name,
            brand_id,
            category_id,
            model_year,
            d.list_price,
            getdate(),
            'DEL'
        FROM
            deleted AS d;       -- deleted: sql server 내부에서 trigger 수행시 사용되는 가상의 테이블
END

이후 production.products 테이블에 데이터를 추가 혹은 삭제하면 트리거가 발생하여 production.product_audits 테이블에 내용이 추가 혹은 수정이 된다.

[참고]

https://www.sqlservertutorial.net/sql-server-triggers/