[sql-server] 삽입 업데이트 트리거 삽입 또는 업데이트 여부를 결정하는 방법

하나의 열 (Desc)이 테이블 A의 열 (예 : Col1)에 삽입 / 업데이트 된 값과 같은 값을 갖는 테이블 B의 모든 행을 삭제하는 테이블 A에 삽입, 업데이트 트리거를 작성해야합니다. 업데이트 및 삽입 사례를 모두 처리 할 수 ​​있도록 작성하는 방법은 무엇입니까? 업데이트 또는 삽입에 대해 트리거가 실행되는지 어떻게 확인합니까?



답변

트리거는 특별해야 INSERTED하고 DELETED데이터 “다음”과 “이전”추적 테이블을. 따라서 IF EXISTS (SELECT * FROM DELETED)업데이트를 탐지하는 것과 같은 것을 사용할 수 있습니다 . DELETED업데이트시 행만 있지만 항상 행이 있습니다.INSERTED 있습니다.

CREATE TRIGGER 에서 “inserted”를 찾으십시오 .

편집, 2011 년 11 월 23 일

주석 후이 답변은 오직 트리거 INSERTEDUPDATED트리거에 대한 것입니다.
분명히, INSERTED위에서 언급 한 것처럼 DELETE 트리거는 “항상 행”을 가질 수 없습니다.


답변

CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    --
    -- Check if this is an INSERT, UPDATE or DELETE Action.
    -- 
    DECLARE @action as char(1);

    SET @action = 'I'; -- Set Action to Insert by default.
    IF EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @action =
            CASE
                WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' -- Set Action to Updated.
                ELSE 'D' -- Set Action to Deleted.       
            END
    END
    ELSE
        IF NOT EXISTS(SELECT * FROM INSERTED) RETURN; -- Nothing updated or inserted.

    ...

    END


답변

아무것도 삭제하지 않는 delete 문을 실행하면 이러한 제안 중 많은 부분이 고려되지 않습니다.

ID가 테이블에 존재하지 않는 값과 같은 곳을 삭제하려고한다고 가정 해보십시오.

트리거는 여전히 호출되지만 삭제 또는 삽입 된 테이블에는 아무 것도 없습니다.

이것을 안전하게 사용하십시오 :

--Determine if this is an INSERT,UPDATE, or DELETE Action or a "failed delete".
DECLARE @Action as char(1);
    SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN 'U'  -- Set Action to Updated.
                        WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN 'I'  -- Set Action to Insert.
                        WHEN EXISTS(SELECT * FROM DELETED)
                        THEN 'D'  -- Set Action to Deleted.
                        ELSE NULL -- Skip. It may have been a "failed delete".   
                    END)

그들의 답변에 대한 @KenDog와 @Net_Prog에게 감사드립니다.

나는 그들의 대본에서 이것을 만들었다.


답변

나는 다음을 사용하고 있으며 아무것도 삭제하지 않는 delete 문을 올바르게 감지합니다.

CREATE TRIGGER dbo.TR_TableName_TriggerName
    ON dbo.TableName
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT * FROM INSERTED)
        -- DELETE
        PRINT 'DELETE';
    ELSE
    BEGIN
        IF NOT EXISTS(SELECT * FROM DELETED)
            -- INSERT
            PRINT 'INSERT';
        ELSE
            -- UPDATE
            PRINT 'UPDATE';
    END
END;


답변

많은 검색을 한 후 INSERT, UPDATE 및 DELETE 트리거 동작의 세 가지 조건을 모두 처리하는 단일 SQL Server 트리거의 정확한 예를 찾을 수 없었습니다. 마지막으로 DELETE 또는 UPDATE가 발생할 때 공통 DELETED 테이블에이 두 작업에 대한 레코드가 포함된다는 사실에 대해 설명하는 텍스트 줄을 찾았습니다. 그런 정보를 바탕으로 트리거가 활성화 된 이유를 결정하는 작은 동작 루틴을 만들었습니다. 이 유형의 인터페이스는 공통 구성과 INSERT vs. UPDATE 트리거에서 발생하는 특정 조치가있을 때 가끔 필요합니다. 이 경우 UPDATE 및 INSERT에 대해 별도의 트리거를 작성하면 유지 보수 문제가됩니다. (즉, 필요한 공통 데이터 알고리즘 수정을 위해 두 트리거가 올바르게 업데이트 되었습니까?)

이를 위해 Microsoft SQL Server에 대한 하나의 트리거에서 INSERT, UPDATE, DELETE를 처리하기 위해 다음과 같은 다중 트리거 이벤트 코드 스 니펫을 제공하고 싶습니다.

CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable]
ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with caller queries SELECT statements.
-- If an update/insert/delete occurs on the main table, the number of records affected
-- should only be based on that table and not what records the triggers may/may not
-- select.
SET NOCOUNT ON;

--
-- Variables Needed for this Trigger
-- 
DECLARE @PACKLIST_ID varchar(15)
DECLARE @LINE_NO smallint
DECLARE @SHIPPED_QTY decimal(14,4)
DECLARE @CUST_ORDER_ID varchar(15)
--
-- Determine if this is an INSERT,UPDATE, or DELETE Action
-- 
DECLARE @Action as char(1)
DECLARE @Count as int
SET @Action = 'I' -- Set Action to 'I'nsert by default.
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
    BEGIN
        SET @Action = 'D' -- Set Action to 'D'eleted.
        SELECT @Count = COUNT(*) FROM INSERTED
        IF @Count > 0
            SET @Action = 'U' -- Set Action to 'U'pdated.
    END

if @Action = 'D'
    -- This is a DELETE Record Action
    --
    BEGIN
        SELECT @PACKLIST_ID =[PACKLIST_ID]
                    ,@LINE_NO = [LINE_NO]
        FROM DELETED

        DELETE [dbo].[MyDataTable]
        WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
    END
 Else
    BEGIN
            --
            -- Table INSERTED is common to both the INSERT, UPDATE trigger
            --
            SELECT @PACKLIST_ID =[PACKLIST_ID]
                ,@LINE_NO = [LINE_NO]
                ,@SHIPPED_QTY =[SHIPPED_QTY]
                ,@CUST_ORDER_ID = [CUST_ORDER_ID]
            FROM INSERTED

         if @Action = 'I'
            -- This is an Insert Record Action
            --
            BEGIN
                INSERT INTO [MyChildTable]
                    (([PACKLIST_ID]
                    ,[LINE_NO]
                    ,[STATUS]
                VALUES
                    (@PACKLIST_ID
                    ,@LINE_NO
                    ,'New Record'
                    )
            END
        else
            -- This is an Update Record Action
            --
            BEGIN
                UPDATE [MyChildTable]
                    SET [PACKLIST_ID] = @PACKLIST_ID
                          ,[LINE_NO] = @LINE_NO
                          ,[STATUS]='Update Record'
                WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
            END
    END   


답변

나는 중첩 된 ifs가 약간 혼란스럽고 믿습니다.

평지가 중첩 된 것보다 낫다 [The Zen of Python]

😉

DROP TRIGGER IF EXISTS AFTER_MYTABLE

GO

CREATE TRIGGER dbo.AFTER_MYTABLE ON dbo.MYTABLE AFTER INSERT, UPDATE, DELETE

AS BEGIN

    --- FILL THE BEGIN/END SECTION FOR YOUR NEEDS.

    SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM INSERTED)  AND EXISTS(SELECT * FROM DELETED)
        BEGIN PRINT 'UPDATE' END
    ELSE IF EXISTS(SELECT * FROM INSERTED)  AND NOT EXISTS(SELECT * FROM DELETED)
        BEGIN PRINT 'INSERT' END
    ELSE IF    EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
        BEGIN PRINT 'DELETED' END
    ELSE BEGIN PRINT 'NOTHING CHANGED'; RETURN; END  -- NOTHING

END


답변

Declare @Type varchar(50)='';
IF EXISTS (SELECT * FROM inserted) and  EXISTS (SELECT * FROM deleted)
BEGIN
    SELECT @Type = 'UPDATE'
END
ELSE IF EXISTS(SELECT * FROM inserted)
BEGIN
    SELECT @Type = 'INSERT'
END
ElSE IF EXISTS(SELECT * FROM deleted)
BEGIN
    SELECT @Type = 'DELETE'
END