Trigger record Log Changes

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: inserts a change record when updating a sales task (individual ).
-- Notes: 1. initialize table name Object Name primary key name,
-- 2. Retrieve all fields in the table and use the cursor to traverse them. Use the SQL statement to add the display name, field name, pre-update value, and updated value to the temporary table.
-- 3. traverse the temporary table and compare the new and old values. If they are not equal, insert a change record.
-- ===================================================== ======
Alter trigger [DBO]. [t_sohu_update_new_personaltaskextensionbase]
On [DBO]. [new_personaltaskextensionbase]
After update
As
Begin
Declare @ tablename varchar (100),/* Table name */
@ Objectname varchar (100),/* entity name */
@ Objectidname varchar (100),/* primary key field name */
@ Objectid uniqueidentifier,/* entity ID */
@ Ownerid uniqueidentifier/* ownerid */

/* Initialize data */
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
/* Create a temporary table to store data comparison information */
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 ))

/* Create a temporary table to store the row before update */
If object_id ('tempdb .. # tempoldtable') is not null
Drop table # tempoldtable
Select * into # tempoldtable from deleted where 1 = 2;

/* Create a temporary table to store the updated rows */
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

/* Traverse the column name and place the new and old values in the temporary table # temptable */
Declare @ columncount int,/* number of rows */
@ Columnindex int/* row index variable */

/* Declare the cursor */
Declare columncursor scroll cursor
For select name from syscolumns where id = object_id (@ tablename) and (name not like '% _ base ')
Open columncursor/* Open cursor */
Set @ columncount = @ cursor_rows/* Number of returned rows */

/* Traverse operation */
Set @ columnindex = 0;
While (@ columnindex