SQL Server 2008 Trigger Code Template

SQL2008 Trigger Code Template 1 - Simple Example.sql

-- =============================================
-- Description:	簡單版，也是最常用的狀況
-- 注意：SQL Server Trigger與Oracle DB Trigger的設計觀完全不同。
--   SQL Server Trigger 的處理對象為一整批；而Oracle DB 是單一筆且可指定到欄位。
--   也就是 inserted 或 delected 是有可能是多筆的；而Oracle trigger一定是一筆。
-- =============================================
CREATE TRIGGER [dbo].[FooTable_Trigger] 
   ON [dbo].[FooTable]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	-- take @Action
	DECLARE @Action char(6);
	IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
		SET @Action = 'UPDATE';
	ELSE IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
		SET @Action = 'INSERT';
	ELSE IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
		SET @Action = 'DELETE';

	IF @Action IS NULL
		RETURN;

	-- logging
	IF @Action IN ('INSERT','UPDATE')
	BEGIN
		INSERT INTO dbo.FooTableLog (Action, LogDtm, IdName, Value)
			SELECT @Action
				 , GETDATE()
				 , IdName
				 , Value
				 FROM inserted;
	END
	ELSE IF @Action IN ('DELETE')
	BEGIN
		INSERT INTO dbo.FooTableLog (Action, LogDtm, IdName, Value)
			SELECT @Action
				 , GETDATE()
				 , IdName
				 , Value
				 FROM deleted;		
	END
END

-- =============================================
-- Description:	練習Trigger的資料表
-- =============================================

CREATE TABLE [dbo].[FooTable](
	[IdName] [char](3) NOT NULL,
	[Value] [nvarchar](50) NULL,
 CONSTRAINT [PK_FooTable] PRIMARY KEY CLUSTERED 
(
	[IdName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[FooTableLog](
	[Action] [char](6) NOT NULL,
	[LogDtm] [datetime] NOT NULL,
	[IdName] [char](3) NULL,
	[Value] [nvarchar](50) NULL
) ON [PRIMARY]

GO

SQL2008 Trigger Code Template 2 - Check Fields Example.sql

-- =============================================
-- Description:	欄位變更檢查
-- 注意：這是批次資料處理
-- =============================================
CREATE TRIGGER [dbo].[FooTable_Trigger_Field] 
   ON [dbo].[FooTable]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.FooTableLog 
			( Action
			, LogDtm
			, IdName
			, Value
			)
		SELECT 'UPDATE'
			 , GETDATE()
			 , I.IdName
			 , I.Value
			 FROM inserted I
			 INNER JOIN deleted D ON I.IdName = D.IdName -- PK
			 WHERE I.Value != D.Value; -- check fileds，欄位差異檢查在此；
			 -- OR I.Field2 != D.Field2
END

SQL2008 Trigger Code Template 3 - Complex Example.sql

-- =============================================
-- Description:	複雜的Trigger代碼，
-- 需一筆筆處理的狀況那就必需重用cursor，
-- 同時 INSERT、UPDATE、DELETE最好個別coding以降低程式碼複雜度，必竟這三個動作一次只會觸發一個。
-- trigger的程式碼越簡單越好，這是必需遵守的原則。
-- =============================================
ALTER TRIGGER [dbo].[BUYEXT_TRIGGER]
   ON  [dbo].[BUYEXT] 
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;

-- To taket the action := {INSERT|UPDATE|DELETE}	
DECLARE @ACTION NVARCHAR(6) = 'INSERT';

-- resource
DECLARE @QKIDNO NVARCHAR(11),
		@QCALLID NVARCHAR(10);
		
-- used to take new-value
DECLARE @new$SOURCETYPE CHAR(1),
		@new$CUSER NVARCHAR(8),
		@new$TRANSNO NVARCHAR(16),
		@new$IPTEL NVARCHAR(30),
		@new$KNO NVARCHAR(6),
		@new$PAYTYPE CHAR(1),
		@new$FUNDNO CHAR(2),
		@new$CREATETIME NVARCHAR(13);

DECLARE ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
	SELECT SOURCETYPE
		 , CUSER
		 , TRANSNO
		 , IPTEL
		 , KNO
		 , PAYTYPE
		 , FUNDNO
		 , CREATETIME
	FROM inserted;

OPEN ForEachInsertedRowTriggerCursor;
WHILE 1=1
BEGIN
	--# take one row new-value
	FETCH ForEachInsertedRowTriggerCursor
		INTO @new$SOURCETYPE,
			 @new$CUSER,
			 @new$TRANSNO,
			 @new$IPTEL,
			 @new$KNO,
			 @new$PAYTYPE,
			 @new$FUNDNO,
			 @new$CREATETIME;

	-- 若取不到資料就離開。
	IF @@FETCH_STATUS != 0
		BREAK;
	
	--# Logging
	-- init
	SET @QKIDNO = NULL;
	SET @QCALLID = NULL;
		
	-- go
	IF @ACTION = 'INSERT' AND @new$SOURCETYPE = '2' AND @new$CUSER = 'IVR0000'
	BEGIN
		
		-- To calculate @QKIDNO
		SELECT @QKIDNO = KIDNO 
		  FROM BENF.dbo.BENE
		  WHERE KNO = @new$KNO;
		
		SET @QKIDNO = dbo.sub_QKIDNO_AzToC2(@QKIDNO);
			
		-- To calculate @QCALLID
		IF @new$PAYTYPE = '1' 
		BEGIN
			SELECT @QCALLID = X.CALLID
				FROM CUSCALLDETAIL AS X
				   , CUSCALLDETAIL AS Y
				   , FUNDNO_TRANS AS Z 
				WHERE X.CALLID = Y.CALLID
				  AND Y.ENTRYDIGIT= @QKIDNO
				  AND X.CALLDATE + X.CALLTIME <= @new$CREATETIME
				  AND X.SERVICENO IN ('132072')
				  AND X.ENTRYDIGIT = Z.FUNDNOVOX
				  AND Z.FUNDNO = @new$FUNDNO
		END
			
		-- writing log
		INSERT INTO IPTEL (TRANSNO,IPTEL,TEL) VALUES (@new$TRANSNO, @QCALLID, '');
	END
	
	--# end of Logging	
END -- of WHILE
CLOSE ForEachInsertedRowTriggerCursor;
DEALLOCATE ForEachInsertedRowTriggerCursor;

END -- of TRIGGER