tri.sql

编写触发器

--測試數據準備.

If exists(select * from sysobjects where id=object_id(N'[dbo].[T_Test]') and xtype = 'u')
   DROP Table T_Test
go

CREATE Table T_Test (
f_id    int IDENTITY(1, 1) Primary Key,
f_char    Char(8) default '',
f_varchar   varchar(8) default '',
f_nvarchar   nvarchar(8) default '',
f_datetime   datetime default getdate(),
f_int    int default 0,
f_bigint   bigint default 0,
f_decimal   decimal(18, 6) default 0.00,
f_number   numeric(18, 6) default 0.00,
f_float    float default 0.00
)
go

INSERT INTO T_Test (f_char) values('001')
INSERT INTO T_Test (f_char) values('002')
go


--編寫Update 觸發器 

If exists(select * from sysobjects where id=object_id(N'[dbo].[Tri_Test_Upd]') and objectproperty(id,N'istrigger')=1)
    DROP TRIGGER Tri_Test_Upd
go

CREATE TRIGGER Tri_Test_Upd ON T_Test --WITH ENCRYPTION
FOR UPDATE
AS
	DECLARE @iRowCnt INT

	SET @iRowCnt = @@rowcount

	IF @iRowCnt < 1
		RETURN

	DECLARE
		@sTable  VARCHAR(128),
		@sPKName VARCHAR(32),
		@sColName VARCHAR(128)

	DECLARE
		@iColCnt  INT,
		@iColId  INT

	DECLARE
		@i    TINYINT,
		@j    TINYINT,
		@iSegment TINYINT,
		@iVal   TINYINT,
		@iLog2  TINYINT

	DECLARE
		@sSQL  VARCHAR(8000)

	SET @sTable = 't_test'
	SET @sPKName = 'f_id'

	-- 求得當前表列個數
	SELECT @iColCnt = Count(1) FROM syscolumns WHERE id = object_id(@sTable)

	-- 以8 個字段為一小段
	SET @iSegment = CASE
		WHEN @iColCnt / 8 = @iColCnt / 8.0
		THEN
			@iColCnt / 8
		ELSE
			@iColCnt / 8 + 1
		END

	-- 將數據存入 臨時表
	SELECT * INTO #Inserted FROM Inserted
	SELECT * INTO #Deleted FROM Deleted

	-- 中間處理數據用
	CREATE TABLE #Temp(
	f_PKVal  varchar(254) not null primary key,
	f_OldVal  varchar(254),
	f_NewVal  varchar(254)
	)

	SET @i = 0

	WHILE @i < @iSegment
	BEGIN
		IF @iColCnt < 9
			SET @iVal= COLUMNS_UPDATED() 
		ELSE
			SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)

	-- 等於0, 則表示當前小節所對應的8個字段無一被改.
	IF @iVal = 0
	BEGIN
		SET @i = @i + 1
		CONTINUE
	END

	WHILE @iVal > 0
	BEGIN
		SET @j = 0
		SET @iLog2 = @iVal / 2

		WHILE @iLog2 > 0
		BEGIN
			SET @j = @j + 1
			SET @iLog2 = @iLog2 / 2
		END

		-- 得到被Update 的 列ID
		SET @iColId = 8 * @i + @j + 1

		-- 將Update列名 賦予 @sColName
		SELECT @sColName = S.name 
		FROM Inserted as I,	Deleted as D, Syscolumns as S
		WHERE I.F_id = D.F_id
				AND S.id = object_id(@sTable)
				AND S.colid = @iColId

		Truncate table #Temp
		-- 拼成動態語句
		SET @sSQL = 'INSERT INTO #Temp (f_PkVal, f_OldVal, f_NewVal) ' + 
					'SELECT Convert( varchar(200), I.' + @sPkName + '), ' + 
					'Convert( varchar(200), D.' + @sColName + '), ' + 
					'Convert( varchar(200), I.' + @sColName + ') ' + 
					'FROM  #Inserted as I, #Deleted as D ' +
					'WHERE I.' + @sPKName + ' = D.' + @sPKName + 
					' AND I.' + @sColName + ' <> D.' + @sColName

		EXEC(@sSQL)

		-- 測試輸出
		Select f_pkVal,  @sColName as f_column_name, f_oldVal, f_newVal  FROM #temp
		-- 實際上用 將信息處理后插入消息表
		/*
		.....
		
		INSERT INTO T_Message(....)
		SELECT 要組織的內容
		FROM #temp
		*/

		SET @iVal = @iVal - Power(2, @j)
		END

		SET @i = @i + 1
	END

	DROP TABLE #Inserted
	DROP TABLE #Deleted
	DROP TABLE #Temp

go

--  測試數據
Update T_test Set f_datetime = getdate(), f_float = 0.0123, f_int= 1

--  上面Update 語句共修改了三個列
--  實際輸出
1.)
1 f_int 0 1
2 f_int 0 1
2.)
1 f_datetime May 15 2004  5:30PM May 15 2004  5:31PM
2 f_datetime May 15 2004  5:30PM May 15 2004  5:31PM
3.)
1 f_float 0 0.0123
2 f_float 0 0.0123