하나의 열 (Desc)이 테이블 A의 열 (예 : Col1)에 삽입 / 업데이트 된 값과 같은 값을 갖는 테이블 B의 모든 행을 삭제하는 테이블 A에 삽입, 업데이트 트리거를 작성해야합니다. 업데이트 및 삽입 사례를 모두 처리 할 수 있도록 작성하는 방법은 무엇입니까? 업데이트 또는 삽입에 대해 트리거가 실행되는지 어떻게 확인합니까?
답변
트리거는 특별해야 INSERTED
하고 DELETED
데이터 “다음”과 “이전”추적 테이블을. 따라서 IF EXISTS (SELECT * FROM DELETED)
업데이트를 탐지하는 것과 같은 것을 사용할 수 있습니다 . DELETED
업데이트시 행만 있지만 항상 행이 있습니다.INSERTED
있습니다.
CREATE TRIGGER 에서 “inserted”를 찾으십시오 .
편집, 2011 년 11 월 23 일
주석 후이 답변은 오직 트리거 INSERTED
및 UPDATED
트리거에 대한 것입니다.
분명히, 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