触发器记录日志变更

USE [MSCRM_MSCRM] GO /****** Object: Trigger [dbo].[T_Sohu_Update_New_personaltaskExtensionBase] Script Date: 03/24/2011 20:26:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Jeff -- Create date: 2011.03.24 -- Description: 更新销售任务(个人)时插入变更记录 -- Notes: 1.初始化表名 实体名 主键名称， -- 2.取得该表所有字段 用游标遍历,拼 SQL语句把显示名，字段名，更新前的值，更新后的值放入临时表中 -- 3.遍历临时表，比较新值和旧值，如果不相等，则插入一个变更记录 -- ============================================= ALTER trigger [dbo].[T_Sohu_Update_New_personaltaskExtensionBase] on [dbo].[New_personaltaskExtensionBase] after Update as begin declare @tableName varchar(100),/*表名*/ @objectName varchar(100),/*实体名*/ @objectidname varchar(100),/*主键字段名*/ @objectid uniqueidentifier,/*实体ID*/ @ownerid uniqueidentifier /*OwnerId*/ /*初始化数据*/ set @tableName ='New_personaltaskExtensionBase' set @objectname ='new_personaltask' set @objectidname='new_personaltaskid' select @objectid = new_personaltaskid from Inserted; select @ownerid=ModifiedBy from New_personaltask where new_personaltaskid=@objectid /*创建临时表 用于储存数据对比信息*/ if object_id('tempdb..#tempTable') is not null drop Table #tempTable Create Table #tempTable(columnDisplayName nvarchar(max),columnName nvarchar(max),oldvalue nvarchar(max),newvalue nvarchar(max)) /*创建临时表 用于放更新前的行*/ if object_id('tempdb..#tempOldTable') is not null drop Table #tempOldTable select * into #tempOldTable from Deleted where 1=2; /*创建临时表 用于放更新后的行*/ if object_id('tempdb..#tempNewTable') is not null drop Table #tempNewTable select * into #tempNewTable from Inserted where 1=2; Insert into #tempOldTable select * from Deleted Insert into #tempNewTable select * from Inserted /*遍历列名,把新值和旧值放到临时表 #tempTable 中*/ declare @ColumnCount int,/*行数*/ @ColumnIndex int /*行索引变量*/ /*声明游标*/ declare columncursor scroll CURSOR for select name from syscolumns where id=object_id(@tableName) and (name not like '%_base') open columncursor /*打开游标*/ set @ColumnCount= @@CURSOR_ROWS /*返回行数*/ /*遍历操作*/ set @ColumnIndex=0; while (@ColumnIndex

转载于:https://www.cnblogs.com/hellohongfu/archive/2011/03/28/1997723.html