触发器记录日志变更
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<@ColumnCount)
begin
declare @ColumnName varchar(8000),
@ColumnDisplayName varchar(8000)
fetch next from columncursor into @ColumnName
select @ColumnDisplayName=[dbo].Fn_Sohu_SelectAttributeDisplayName(@ColumnName,@objectname)
declare @sql varchar(8000)
set @sql='Insert into #tempTable select '''+@columnDisplayName+'''as columnDisplayName,'''+@ColumnName+''' as columnName,CONVERT(varchar(100),O.'+@ColumnName+') as oldvalue,CONVERT(varchar(100),N.'+@ColumnName+') as newvalue from #tempOldTable o inner join #tempNewTable n on o.'+@objectidname+' = n.'+@objectidname+';'
exec(@sql);
set @ColumnIndex=@ColumnIndex+1
end
close columncursor /*关闭游标*/
DEALLOCATE columncursor /*删除游标*/
/*遍历临时表 如果新值和旧值不相等,则插入一条变更记录*/
declare @TempTableCount int,/*行数*/
@TempTableIndex int /*行索引变量*/
/*声明游标*/
declare tempcursor scroll CURSOR
for select columnDisplayName ,columnName,oldvalue,newvalue from #tempTable
open tempcursor --打开游标
set @TempTableCount= @@CURSOR_ROWS --返回行数
/*遍历临时表 tempdb..#tempTable*/
set @TempTableIndex=0;
while (@TempTableIndex<@TempTableCount)
begin
declare @AttributeName varchar(8000),
@AttributeDisplayName varchar(8000),
@AttributeOldValue varchar(8000),
@AttributeNewValue varchar(8000)
fetch next from tempcursor into @AttributeDisplayName
,@AttributeName
,@AttributeOldValue
,@AttributeNewValue;
/*如果新值和旧值不相等 则认为发生了改变 则插入一条记录*/
if(isnull(@AttributeOldValue,'')!=isnull(@AttributeNewValue,''))
begin
exec P_Sohu_Create_New_tasktrace @objectid,
@AttributeDisplayName,
@AttributeName,
@AttributeOldValue,
@AttributeNewValue,
@ObjectName,
@ownerid;
end
set @TempTableIndex=@TempTableIndex+1
end
close tempcursor /*关闭游标*/
DEALLOCATE tempcursor /*删除游标*/
/*删除临时表*/
if object_id('tempdb..#tempTable') is not null
drop Table #tempTable
if object_id('tempdb..#tempOldTable') is not null
drop Table #tempOldTable
if object_id('tempdb..#tempNewTable') is not null
drop Table #tempNewTable
end
GO
----------------------------------------------------------------------------------------------------------------------------------------------------
USE [SohuCRMTest006_MSCRM]
GO
/****** Object: Trigger [dbo].[T_Sohu_Update_New_RegionaltaskExtensionBase] Script Date: 03/24/2011 20:29:15 ******/
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_RegionaltaskExtensionBase]
on [dbo].[New_regionaltaskExtensionBase]
after Update
as
begin
declare @tableName varchar(100),/*表名*/
@objectName varchar(100),/*实体名*/
@objectidname varchar(100),/*主键字段名*/
@objectid uniqueidentifier,/*实体ID*/
@ownerid uniqueidentifier /*OwnerId*/
/*初始化数据*/
set @tableName ='New_RegionaltaskExtensionBase'
set @objectname ='New_Regionaltask'
set @objectidname='New_Regionaltaskid'
select @objectid = New_Regionaltaskid from Inserted;
select @ownerid=ModifiedBy from New_Regionaltask where New_Regionaltaskid=@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<@ColumnCount)
begin
declare @ColumnName varchar(8000),
@ColumnDisplayName varchar(8000)
fetch next from columncursor into @ColumnName
select @ColumnDisplayName=[dbo].Fn_Sohu_SelectAttributeDisplayName(@ColumnName,@objectname)
declare @sql varchar(8000)
set @sql='Insert into #tempTable select '''+@columnDisplayName+'''as columnDisplayName,'''+@ColumnName+''' as columnName,CONVERT(varchar(100),O.'+@ColumnName+') as oldvalue,CONVERT(varchar(100),N.'+@ColumnName+') as newvalue from #tempOldTable o inner join #tempNewTable n on o.'+@objectidname+' = n.'+@objectidname+';'
exec(@sql);
set @ColumnIndex=@ColumnIndex+1
end
close columncursor /*关闭游标*/
DEALLOCATE columncursor /*删除游标*/
/*遍历临时表 如果新值和旧值不相等,则插入一条变更记录*/
declare @TempTableCount int,/*行数*/
@TempTableIndex int /*行索引变量*/
/*声明游标*/
declare tempcursor scroll CURSOR
for select columnDisplayName ,columnName,oldvalue,newvalue from #tempTable
open tempcursor /*打开游标*/
set @TempTableCount= @@CURSOR_ROWS /*返回行数*/
/*遍历临时表*/
set @TempTableIndex=0;
while (@TempTableIndex<@TempTableCount)
begin
declare @AttributeName varchar(8000),
@AttributeDisplayName varchar(8000),
@AttributeOldValue varchar(8000),
@AttributeNewValue varchar(8000)
fetch next from tempcursor into @AttributeDisplayName
,@AttributeName
,@AttributeOldValue
,@AttributeNewValue;
/*如果新值和旧值不相等 则认为发生了改变 则插入一条记录*/
if(isnull(@AttributeOldValue,'')!=isnull(@AttributeNewValue,''))
begin
exec P_Sohu_Create_New_tasktrace @objectid,
@AttributeDisplayName,
@AttributeName,
@AttributeOldValue,
@AttributeNewValue,
@ObjectName,
@ownerid;
end
set @TempTableIndex=@TempTableIndex+1
end
close tempcursor --关闭游标
DEALLOCATE tempcursor --删除游标
/*删除临时表*/
if object_id('tempdb..#tempTable') is not null
drop Table #tempTable
if object_id('tempdb..#tempOldTable') is not null
drop Table #tempOldTable
if object_id('tempdb..#tempNewTable') is not null
drop Table #tempNewTable
end
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------
USE [SohuCRMTest006_MSCRM]
GO
/****** Object: Trigger [dbo].[T_Sohu_Update_New_VisittaskExtensionBase] Script Date: 03/24/2011 20:30: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_VisittaskExtensionBase]
on [dbo].[New_visittaskExtensionBase]
after Update
as
begin
declare @tableName varchar(100),/*表名*/
@objectName varchar(100),/*实体名*/
@objectidname varchar(100),/*主键字段名*/
@objectid uniqueidentifier,/*实体ID*/
@ownerid uniqueidentifier /*OwnerId*/
/*初始化数据*/
set @tableName ='New_VisittaskExtensionBase'
set @objectname ='New_Visittask'
set @objectidname='New_Visittaskid'
select @objectid = new_visittaskid from Inserted;
select @ownerid=ModifiedBy from New_Visittask where New_Visittaskid=@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;
--if object_id('tempdb..#tempOldTable') is not null
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<@ColumnCount)
begin
declare @ColumnName varchar(8000),
@ColumnDisplayName varchar(8000)
fetch next from columncursor into @ColumnName
select @ColumnDisplayName=[dbo].Fn_Sohu_SelectAttributeDisplayName(@ColumnName,@objectname)
declare @sql varchar(8000)
set @sql='Insert into #tempTable select '''+@columnDisplayName+'''as columnDisplayName,'''+@ColumnName+''' as columnName,CONVERT(varchar(max),O.'+@ColumnName+') as oldvalue,CONVERT(varchar(max),N.'+@ColumnName+') as newvalue from #tempOldTable o inner join #tempNewTable n on o.'+@objectidname+' = n.'+@objectidname+';'
exec(@sql);
--insert into temptable (columnDisplayName) values (@sql);
--Insert into #tempTable select '拜访任务'as columnDisplayName,'New_visittaskId' as columnName,CONVERT(varchar(max),isnull(O.New_visittaskId,'')) as oldvalue,CONVERT(varchar(max),isnull(N.New_visittaskId,'')) as newvalue from #tempOldTable o inner join #tempNewTable n on o.New_Visittaskid = n.New_Visittaskid;
set @ColumnIndex=@ColumnIndex+1
end
close columncursor /*关闭游标*/
DEALLOCATE columncursor /*删除游标*/
/*遍历临时表 如果新值和旧值不相等,则插入一条变更记录*/
declare @TempTableCount int,/*行数*/
@TempTableIndex int /*行索引变量*/
/*声明游标*/
declare tempcursor scroll CURSOR
for select columnDisplayName ,columnName,oldvalue,newvalue from #tempTable
open tempcursor --打开游标
set @TempTableCount= @@CURSOR_ROWS --返回行数
/*遍历临时表 tempdb..#tempTable*/
set @TempTableIndex=0;
while (@TempTableIndex<@TempTableCount)
begin
declare @AttributeName varchar(8000),
@AttributeDisplayName varchar(8000),
@AttributeOldValue varchar(8000),
@AttributeNewValue varchar(8000)
fetch next from tempcursor into @AttributeDisplayName
,@AttributeName
,@AttributeOldValue
,@AttributeNewValue;
/*如果新值和旧值不相等 则认为发生了改变 则插入一条记录*/
if(isnull(@AttributeOldValue,'')!=isnull(@AttributeNewValue,''))
begin
exec P_Sohu_Create_New_tasktrace @objectid,
@AttributeDisplayName,
@AttributeName,
@AttributeOldValue,
@AttributeNewValue,
@ObjectName,
@ownerid;
end
set @TempTableIndex=@TempTableIndex+1
end
close tempcursor --关闭游标
DEALLOCATE tempcursor --删除游标
/*删除临时表*/
if object_id('tempdb..#tempTable') is not null
drop Table #tempTable
if object_id('tempdb..#tempOldTable') is not null
drop Table #tempOldTable
if object_id('tempdb..#tempNewTable') is not null
drop Table #tempNewTable
end
GO
-------------------------------------------------------------------------------------------------------------------------
USE [SohuCRMTest006_MSCRM]
GO
/****** Object: Trigger [dbo].[T_Sohu_Update_New_personaltaskBase] 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: 更新销售任务(个人)时插入变更记录
-- =============================================
Alter trigger [dbo].[T_Sohu_Update_New_personaltaskBase]
on [dbo].[New_personaltaskBase]
after Update
as
if update(OwningUser)
begin
declare @tableName varchar(100),/*表名*/
@objectName varchar(100),/*实体名*/
@objectidname varchar(100),/*主键字段名*/
@objectid uniqueidentifier,/*实体ID*/
@ownerid uniqueidentifier /*OwnerId*/
declare @AttributeDisplayName varchar(100),/*字段显示名*/
@AttributeName varchar(100) ,/*字段名称*/
@AttributeOldValue varchar(100),/*更新前的值*/
@AttributeNewValue varchar(100)/*更新后的值*/
/*初始化数据*/
set @tableName ='New_personaltaskBase'
set @objectname ='new_personaltask'
set @objectidname='New_personaltaskId'
select @objectid = New_personaltaskId from Inserted;
select @ownerid=ModifiedBy from New_personaltask where New_personaltaskId=@objectid
set @AttributeDisplayName ='负责人'
set @AttributeName='Ownerid'
select @AttributeOldValue = OwningUser from Deleted
select @AttributeNewValue = OwningUser from Inserted
exec P_Sohu_Create_New_tasktrace @objectid,
@AttributeDisplayName,
@AttributeName,
@AttributeOldValue,
@AttributeNewValue,
@ObjectName,
@ownerid
end
GO
----------------------------------------------------------------------------------------------------------------------------------------------------
USE [SohuCRMTest006_MSCRM]
GO
/****** Object: Trigger [dbo].[T_Sohu_Update_New_RegionaltaskBase] Script Date: 03/24/2011 20:29:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jeff
-- Create date: 2011.03.24
-- Description: 更新销售任务(地区)时插入变更记录
-- =============================================
alter trigger [dbo].[T_Sohu_Update_New_RegionaltaskBase]
on [dbo].[New_regionaltaskBase]
after Update
as
if update(OwningUser)
begin
declare @tableName varchar(100),/*表名*/
@objectName varchar(100),/*实体名*/
@objectidname varchar(100),/*主键字段名*/
@objectid uniqueidentifier,/*实体ID*/
@ownerid uniqueidentifier /*OwnerId*/
declare @AttributeDisplayName varchar(100),/*字段显示名*/
@AttributeName varchar(100) ,/*字段名称*/
@AttributeOldValue varchar(100),/*更新前的值*/
@AttributeNewValue varchar(100)/*更新后的值*/
/*初始化数据*/
set @tableName ='New_RegionaltaskBase'
set @objectname ='New_Regionaltask'
set @objectidname='New_Regionaltaskid'
select @objectid = New_Regionaltaskid from Inserted;
select @ownerid=ModifiedBy from New_Regionaltask where New_Regionaltaskid=@objectid
set @AttributeDisplayName ='负责人'
set @AttributeName='Ownerid'
select @AttributeOldValue = OwningUser from Deleted
select @AttributeNewValue = OwningUser from Inserted
exec P_Sohu_Create_New_tasktrace @objectid,
@AttributeDisplayName,
@AttributeName,
@AttributeOldValue,
@AttributeNewValue,
@ObjectName,
@ownerid
end
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------
USE [SohuCRMTest006_MSCRM]
GO
/****** Object: Trigger [dbo].[T_Sohu_Update_New_VisittaskBase] Script Date: 03/24/2011 20:30:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jeff
-- Create date: 2011.03.24
-- Description: 更新拜访任务时插入变更记录
-- =============================================
alter trigger [dbo].[T_Sohu_Update_New_VisittaskBase]
on [dbo].[New_visittaskBase]
after Update
as
if update(OwningUser)
begin
declare @tableName varchar(100),/*表名*/
@objectName varchar(100),/*实体名*/
@objectidname varchar(100),/*主键字段名*/
@objectid uniqueidentifier,/*实体ID*/
@ownerid uniqueidentifier /*OwnerId*/
declare @AttributeDisplayName varchar(100),/*字段显示名*/
@AttributeName varchar(100) ,/*字段名称*/
@AttributeOldValue varchar(100),/*更新前的值*/
@AttributeNewValue varchar(100)/*更新后的值*/
/*初始化数据*/
set @tableName ='New_VisittaskBase'
set @objectname ='New_Visittask'
set @objectidname='New_Visittaskid'
select @objectid = new_visittaskid from Inserted;
select @ownerid=ModifiedBy from New_Visittask where New_Visittaskid=@objectid
set @AttributeDisplayName ='负责人'
set @AttributeName='Ownerid'
select @AttributeOldValue = OwningUser from Deleted
select @AttributeNewValue = OwningUser from Inserted
exec P_Sohu_Create_New_tasktrace @objectid,
@AttributeDisplayName,
@AttributeName,
@AttributeOldValue,
@AttributeNewValue,
@ObjectName,
@ownerid;
end
GO