DML 트리거의 활용

SQL Server에서 트리거 구축 및 최적화

Florin Angelescu

Instructor

DML 트리거를 쓰는 이유

데이터 조작 시 동작 시작
데이터 조작 방지
데이터/오브젝트 변경 추적
사용자 감사 및 DB 보안

AFTER와 INSTEAD OF 선택하기

CREATE TRIGGER MyFirstAfterTrigger
ON Table1
-- Triggered after 
-- the firing event (UPDATE)
AFTER UPDATE
AS
{trigger_actions_section};

CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table2
-- Triggered instead of 
-- the firing event (UPDATE)
INSTEAD OF UPDATE
AS
{trigger_actions_section};

AFTER와 INSTEAD OF 선택하기

AFTER 트리거

초기 이벤트가 트리거를 발생

초기 이벤트 실행

트리거 동작 실행

AFTER 트리거 동작 방식

INSTEAD OF 트리거

초기 이벤트가 트리거를 발생

초기 이벤트는 더 이상 실행되지 않음

트리거 동작 실행

INSTEAD OF 트리거 동작 방식

AFTER 트리거 사용 예시

판매 테이블에 데이터 삽입
정제 프로시저 시작
결과 보고서 테이블 생성
DBA에 알림

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

INSTEAD OF 트리거 사용 예시

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 45W   | 0     |
| Lume  | Ultra    | 50W   | 0     |

일부 모델의 전력이 변경됨

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

INSTEAD OF 트리거 사용 예시

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |

일부 모델의 전력이 변경됨
재고가 없는 제품만 업데이트

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END

INSTEAD OF 트리거 사용 예시

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |
| Ecco  | Standard | 35W   | 100   |

일부 모델의 전력이 변경됨
재고가 없는 제품만 업데이트
재고가 있는 제품은 새 행 추가

-- First part was truncated for spacing reasons
IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END
ELSE
    INSERT INTO Bulbs
    SELECT * FROM inserted;

연습 문제

SQL Server에서 트리거 구축 및 최적화