트리거

CREATE TABLE Board.POST_HISTORY (
    num       int IDENTITY(1,1) NOT NULL,
    post_num  int           NOT NULL,
    old_title nvarchar(200) NULL,
    new_title nvarchar(200) NULL,
    chg_date  datetime2(0)  NOT NULL
        CONSTRAINT DF_POST_HISTORY_chg DEFAULT SYSDATETIME(),
    CONSTRAINT PK_POST_HISTORY PRIMARY KEY CLUSTERED (num));

CREATE OR ALTER TRIGGER Board.TR_POSTS_TITLE_BAD
ON Board.POSTS
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(title) RETURN;

    DECLARE @post_num int, @old nvarchar(200), @new nvarchar(200);

    SELECT @post_num = num, @old = title FROM deleted;   -- 한 행이라고 가정합니다
    SELECT @new = title FROM inserted;

    INSERT INTO Board.POST_HISTORY (post_num, old_title, new_title)
    VALUES (@post_num, @old, @new);
END
GO
-- (가) 한 행을 고칩니다.
UPDATE Board.POSTS SET title = title + N' (고침)' WHERE num = 250;

-- (나) 공지사항 35건을 한 번에 고칩니다.
UPDATE Board.POSTS SET title = title + N' (고침)' WHERE board_num = 1;

CREATE OR ALTER TRIGGER Board.TR_POSTS_TITLE
ON Board.POSTS
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(title) RETURN;

    -- 두 가상 표를 키로 이어 한 문장으로 넣습니다.
    INSERT INTO Board.POST_HISTORY (post_num, old_title, new_title)
    SELECT i.num, d.title, i.title
    FROM inserted i
        JOIN deleted d ON d.num = i.num
    WHERE ISNULL(i.title, N'') <> ISNULL(d.title, N'');
END

CREATE OR ALTER TRIGGER Board.TR_POSTS_GUARD
ON Board.POSTS
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @@TRANCOUNT AS 트랜잭션수, @@NESTLEVEL AS 중첩수준;

    IF EXISTS (SELECT 1 FROM inserted WHERE hit_count < 0)
        THROW 50003, N'조회수는 음수가 될 수 없습니다', 1;
END
GO
UPDATE Board.POSTS SET hit_count = hit_count WHERE num = 250;

BEGIN TRAN;
BEGIN TRY
    UPDATE Board.POSTS SET hit_count = -1 WHERE num = 250;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS 번호, XACT_STATE() AS 상태;
END CATCH
IF XACT_STATE() <> 0 ROLLBACK;
SELECT num, hit_count FROM Board.POSTS WHERE num = 250;

-- 트리거 안에서 ROLLBACK 을 적었을 때
BEGIN TRAN;
UPDATE Board.POSTS SET hit_count = -1 WHERE num = 250;
PRINT N'>> 이 줄이 실행됩니까?';
SELECT @@TRANCOUNT AS 남은트랜잭션;

UPDATE Board.V_POST_LIST SET title = N'x', nickname = N'y' WHERE num = 250;

CREATE OR ALTER TRIGGER Board.TR_V_POST_LIST_UPD
ON Board.V_POST_LIST
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE p SET title = i.title, hit_count = i.hit_count
    FROM Board.POSTS p JOIN inserted i ON i.num = p.num;

    UPDATE u SET nickname = i.nickname
    FROM Member.USERS u
        JOIN Board.POSTS p ON p.user_num = u.num
        JOIN inserted i ON i.num = p.num;
END
GO
UPDATE Board.V_POST_LIST SET title = N'제목을 바꿉니다', nickname = N'닉네임도'
WHERE num = 250;

-- DELETE 트리거를 달아 둔 표입니다.
DELETE FROM Board.T_TRG;        -- (가)
TRUNCATE TABLE Board.T_TRG;    -- (나)

DISABLE TRIGGER Board.TR_T_TRG_DEL ON Board.T_TRG;
DELETE FROM Board.T_TRG;                 -- 기록이 남지 않습니다
ENABLE TRIGGER Board.TR_T_TRG_DEL ON Board.T_TRG;

-- 지금 무엇이 걸려 있고 꺼져 있는지 봅니다.
SELECT t.name AS 트리거, OBJECT_NAME(t.parent_id) AS 대상,
       t.is_instead_of_trigger AS INSTEAD_OF, t.is_disabled AS 꺼짐
FROM sys.triggers t WHERE t.is_ms_shipped = 0;