Null value is eliminated error when updating table 1

tyutghf · Jul 2, 2009

I am trying to update a table using asp but get this error on the page and the table is not updated

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation.

Here is the code to update

Code:

SQL = "UPDATE table SET row='" & request.querystring("answer") & "' WHERE id = '"&request.querystring("id")&"'"
objConn.execute SQL

My google efforts say that this error comes when using SUM() but I am using a simply update command.

can anyone help?

tyutghf · Jul 2, 2009

forgot to say that 'row' is currently set to NULL

gmmastros · Jul 2, 2009

Do you have a trigger on the table?

You can prevent this error by adding.

Code:

SET ANSI_WARNINGS OFF

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

tyutghf · Jul 2, 2009

Oh yes I have just seen a trigger on it. I have not come across Triggers before, is this the bit causing me a problem

SET ANSI_NULLS ON
GO

Can I use the code you supplied to overcome this? If so, do I just use this on my SQL in the asp script i.e.

SQL = "SET ANSI_WARNINGS OFF; SELECT ...

gmmastros · Jul 2, 2009

I would modify the trigger to add the ansi warnings line to it. It may be possible (I don't know for sure) to add the ansi_warnings line to the query the updates the table and actually have it solve your problem. But... consider this. If you add the line to the trigger, any code, any where that modifies the table (and therefore runs the trigger), will also be fixed.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

gmmastros · Jul 2, 2009

If you post the code for the trigger, I could show you the best way to modify it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

tyutghf · Jul 2, 2009

It is rather large, the row I want to update is TaskLookup8

Code:

USE [DATABASENAME]
GO
/****** Object:  Trigger [dbo].[TABLENAME$AutoLog]    Script Date: 07/02/2009 13:45:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[TABLENAME$AutoLog] on [dbo].[TABLENAME] FOR DELETE, INSERT, UPDATE
AS
begin
set nocount on
declare
	@DbChangeLogId int,
	@CreatedTime          datetime ,
	@PrincipalName        varchar(40),
	@DbChangeLogTransactionId int,
	@ActivitySystemResourceId integer
  --
declare
	@PKColumnId  int ,
	@DbChangeLogColumnId  int ,
	@TableName            varchar(255),
	@ColumnName           varchar(255),
	@IsPk                 bit,
	@InsertUpdateDeleteCode char(1),
	@TableHasMultiPk      bit,
	@DbChangeLogDataType  varchar(255),
	@DBMSDatatype         varchar(255),
	@InsertedCt int,
	@DeletedCt int,
	@cPK int
declare
	@iDbChangeLogId  int ,
	@dDbChangeLogId  int ,
	@iPKColumnId  int,
	@dPKColumnId  int
-- cursor variables
Declare
  @iGUIDO varchar(40),
  @iWO_NUM integer,
  @iTASK varchar(100),
  @iTYPE varchar(30),
  @iREQUEST varchar(50),
  @iREQDATE datetime,
  @iOPENBY varchar(255),
  @iOPENDATE datetime,
  @iCLSDBY varchar(255),
  @iCLSDDATE datetime,
  @iELAPSETIME varchar(20),
  @iELAPSEMIN integer,
  @iPRIORITY varchar(30),
  @iDUEDATE datetime,
  @iRESPONS varchar(30),
  @iASSNDATE datetime,
  @iCOMPLETED datetime,
  @iHOURS float,
  @iRATE float,
  @iCHARGE float,
  @iWS_NUM integer,
  @iDEPT_NUM varchar(30),
  @iDEPT varchar(30),
  @iPHONE varchar(30),
  @iPHONE_EXT varchar(8),
  @iLOCATION varchar(30),
  @iWO_TEXT1 varchar(40),
  @iWO_TEXT2 varchar(40),
  @iWO_TEXT3 varchar(40),
  @iWO_TEXT4 varchar(40),
  @iWO_TEXT5 varchar(40),
  @iWO_TEXT6 varchar(40),
  @iWO_DATE1 datetime,
  @iWO_DATE2 datetime,
  @iWO_DATE3 datetime,
  @iWO_DATE4 datetime,
  @iWO_NUM1 float,
  @iWO_INT1 integer,
  @iFT bit,
  @iSTATUS varchar(25),
  @iAGENTDATE datetime,
  @iAGENTLEVEL tinyint,
  @iAWS_NUM varchar(40),
  @iLOOKUP1 varchar(30),
  @iLOOKUP2 varchar(30),
  @iTaskLookup3 varchar(30),
  @iTaskLookup4 VARCHAR(30),
  @iTaskLookup5 VARCHAR(30),
  @iTaskLookup6 VARCHAR(30),
  @iTaskLookup7 VARCHAR(30),
  @iTaskLookup8 VARCHAR(30),
  @iEMAILADDR varchar(100),
  @iUSERID integer,
  @iPARENTWOID int,
  @iWOTYPE2 varchar(30),
  @iWOTYPE3 varchar(30),
  @iATTACHCOUNT integer,
  @iWorkOrderTypeId int,
  @iWorkOrderStatusId int,
  @iAssignmentNumber int,
  @iPolicyComponentId int,
  @iPolicyPendingDueAlarmDate datetime,
  @iPolicyOverdueAlarmDate datetime,
  @iExpComplPendingDueAlarmDate datetime,
  @iExpComplOverdueAlarmDate datetime,
  @iPolicyDueDate datetime,
  @iWorkOrderTemplateName varchar(255),
  @iSLANAME varchar(30),
  @dGUIDO varchar(40),
  @dWO_NUM integer,
  @dTASK varchar(100),
  @dTYPE varchar(30),
  @dREQUEST varchar(50),
  @dREQDATE datetime,
  @dOPENBY varchar(255),
  @dOPENDATE datetime,
  @dCLSDBY varchar(255),
  @dCLSDDATE datetime,
  @dELAPSETIME varchar(20),
  @dELAPSEMIN integer,
  @dPRIORITY varchar(30),
  @dDUEDATE datetime,
  @dRESPONS varchar(30),
  @dASSNDATE datetime,
  @dCOMPLETED datetime,
  @dHOURS float,
  @dRATE float,
  @dCHARGE float,
  @dWS_NUM integer,
  @dDEPT_NUM varchar(30),
  @dDEPT varchar(30),
  @dPHONE varchar(30),
  @dPHONE_EXT varchar(8),
  @dLOCATION varchar(30),
  @dWO_TEXT1 varchar(40),
  @dWO_TEXT2 varchar(40),
  @dWO_TEXT3 varchar(40),
  @dWO_TEXT4 varchar(40),
  @dWO_TEXT5 varchar(40),
  @dWO_TEXT6 varchar(40),
  @dWO_DATE1 datetime,
  @dWO_DATE2 datetime,
  @dWO_DATE3 datetime,
  @dWO_DATE4 datetime,
  @dWO_NUM1 float,
  @dWO_INT1 integer,
  @dFT bit,
  @dSTATUS varchar(25),
  @dAGENTDATE datetime,
  @dAGENTLEVEL tinyint,
  @dAWS_NUM varchar(40),
  @dLOOKUP1 varchar(30),
  @dLOOKUP2 varchar(30),
  @dTaskLookup3 varchar(30),
  @dTaskLookup4 VARCHAR(30),
  @dTaskLookup5 VARCHAR(30),
  @dTaskLookup6 VARCHAR(30),
  @dTaskLookup7 VARCHAR(30),
  @dTaskLookup8 VARCHAR(30),
  @dEMAILADDR varchar(100),
  @dUSERID integer,
  @dPARENTWOID int,
  @dWOTYPE2 varchar(30),
  @dWOTYPE3 varchar(30),
  @dATTACHCOUNT integer,
  @dWorkOrderTypeId int,
  @dWorkOrderStatusId int,
  @dAssignmentNumber int,
  @dPolicyComponentId int,
  @dPolicyPendingDueAlarmDate datetime,
  @dPolicyOverdueAlarmDate datetime,
  @dExpComplPendingDueAlarmDate datetime,
  @dExpComplOverdueAlarmDate datetime,
  @dPolicyDueDate datetime,
  @dWorkOrderTemplateName varchar(255),
  @dSLANAME varchar(30)
declare
    @tStringValueOld varchar(4000),
    @tStringValueNew varchar(4000)
-- dont fire recursively please
if trigger_nestlevel(object_id('[TABLENAME$AutoLog]')) > 1
begin
	return
end
-- compute dml type
select @InsertedCt = count(*) from inserted
select @DeletedCt = count(*) from deleted
select @InsertUpdateDeleteCode = case
	when @InsertedCt > 0 and @DeletedCt > 0 then 'U'
	when @InsertedCt > 0 then 'I'
	when @DeletedCt > 0 then 'D'
	else 'N' end,
    @TableHasMultiPk = 0
-- this tests to see if autologging should occur at all
if  @InsertUpdateDeleteCode in ('I','D')
	or UPDATE( AGENTDATE )
	or UPDATE( AGENTLEVEL )
	or UPDATE( AGENTLEVELREQ )
	or UPDATE( AssignmentNumber )
	or UPDATE( ASSNDATE )
	or UPDATE( ATTACHCOUNT )
	or UPDATE( AWS_NUM )
	or UPDATE( CHARGE )
	or UPDATE( CLSDBY )
	or UPDATE( CLSDDATE )
	or UPDATE( COMPLETED )
	or UPDATE( DEPT_NUM )
	or UPDATE( DEPT )
	or UPDATE( DUEDATE )
	or UPDATE( ELAPSEMIN )
	or UPDATE( ELAPSETIME )
	or UPDATE( EMAILADDR )
	or UPDATE( ExpComplOverdueAlarmDate )
	or UPDATE( ExpComplPendingDueAlarmDate )
	or UPDATE( FT )
	or UPDATE( GUIDO )
	or UPDATE( HOURS )
	or UPDATE( LOCATION )
	or UPDATE( LOOKUP1 )
	or UPDATE( LOOKUP2 )
	or UPDATE( OPENBY )
	or UPDATE( OPENDATE )
	or UPDATE( PARENTWOID )
	or UPDATE( PHONE_EXT )
	or UPDATE( PHONE )
	or UPDATE( PolicyComponentId )
	or UPDATE( PolicyDueDate )
	or UPDATE( PolicyOverdueAlarmDate )
	or UPDATE( PolicyPendingDueAlarmDate )
	or UPDATE( PRIORITY )
	or UPDATE( RATE )
	or UPDATE( REQDATE )
	or UPDATE( REQUEST )
	or UPDATE( RESPONS )
	or UPDATE( SLANAME )
	or UPDATE( STATUS )
	or UPDATE( TASK )
	or UPDATE( TaskLookup3 )
	or UPDATE( TaskLookup4 )
	or UPDATE( TaskLookup5 )
	or UPDATE( TaskLookup6 )
	or UPDATE( TaskLookup7 )
	or UPDATE( TaskLookup8 )
	or UPDATE( TYPE )
	or UPDATE( USERID )
	or UPDATE( WO_DATE1 )
	or UPDATE( WO_DATE2 )
	or UPDATE( WO_DATE3 )
	or UPDATE( WO_DATE4 )
	or UPDATE( WO_INT1 )
	or UPDATE( WO_NUM )
	or UPDATE( WO_NUM1 )
	or UPDATE( WO_TEXT1 )
	or UPDATE( WO_TEXT2 )
	or UPDATE( WO_TEXT3 )
	or UPDATE( WO_TEXT4 )
	or UPDATE( WO_TEXT5 )
	or UPDATE( WO_TEXT6 )
	or UPDATE( WorkOrderStatusId )
	or UPDATE( WorkOrderTemplateName )
	or UPDATE( WorkOrderTypeId )
	or UPDATE( WOTYPE2 )
	or UPDATE( WOTYPE3 )
	or UPDATE( WS_NUM )
begin
select
       @CreatedTime = CreatedTime,
       @PrincipalName = PrincipalName,
       @DbChangeLogTransactionId = DbChangeLogTransactionId,
       @ActivitySystemResourceId = ActivitySystemResourceId
from vDbChangeLogTransactionCurrent
  -- get the column dictionary info for pk1 'TABLENAME.WOID'
exec DbChangeLogColumn$InsUpd
	@DbChangeLogColumnId = @PKColumnId output,
	@TableName =  'TABLENAME',
	@ColumnName = 'WOID',
	@IsPk = 1,
	@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
	@TableHasMultiPk = @TableHasMultiPk,
	@DbChangeLogDataType = 'INT',
	@DBMSDatatype = 'integer'
-- now spin through all rows to process the audit
declare cChangeLogColumns cursor read_only FAST_FORWARD for
	select case when i.WOID is not null then i.WOID else d.WOID end,
			i.GUIDO,i.WO_NUM,i.TASK,i.TYPE,i.REQUEST,i.REQDATE,i.OPENBY,i.OPENDATE,i.CLSDBY,i.CLSDDATE,i.ELAPSETIME,i.ELAPSEMIN,i.PRIORITY,i.DUEDATE,i.RESPONS,i.ASSNDATE,i.COMPLETED,i.HOURS,i.RATE,i.CHARGE,i.WS_NUM,i.DEPT_NUM,i.DEPT,i.PHONE,i.PHONE_EXT,i.LOCATION,i.WO_TEXT1,i.WO_TEXT2,i.WO_TEXT3,i.WO_TEXT4,i.WO_TEXT5,i.WO_TEXT6,i.WO_DATE1,i.WO_DATE2,i.WO_DATE3,i.WO_DATE4,i.WO_NUM1,i.WO_INT1,i.FT,i.STATUS,i.AGENTDATE,i.AGENTLEVEL,i.AWS_NUM,i.LOOKUP1,i.LOOKUP2,i.TaskLookup3,i.TaskLookup4,i.TaskLookup5,i.TaskLookup6,i.TaskLookup7,i.TaskLookup8,i.EMAILADDR,i.USERID,i.PARENTWOID,i.WOTYPE2,i.WOTYPE3,i.ATTACHCOUNT,i.WorkOrderTypeId,i.WorkOrderStatusId,i.AssignmentNumber,i.PolicyComponentId,i.PolicyPendingDueAlarmDate,i.PolicyOverdueAlarmDate,i.ExpComplPendingDueAlarmDate,i.ExpComplOverdueAlarmDate,i.PolicyDueDate,i.WorkOrderTemplateName,i.SLANAME
			,d.GUIDO,d.WO_NUM,d.TASK,d.TYPE,d.REQUEST,d.REQDATE,d.OPENBY,d.OPENDATE,d.CLSDBY,d.CLSDDATE,d.ELAPSETIME,d.ELAPSEMIN,d.PRIORITY,d.DUEDATE,d.RESPONS,d.ASSNDATE,d.COMPLETED,d.HOURS,d.RATE,d.CHARGE,d.WS_NUM,d.DEPT_NUM,d.DEPT,d.PHONE,d.PHONE_EXT,d.LOCATION,d.WO_TEXT1,d.WO_TEXT2,d.WO_TEXT3,d.WO_TEXT4,d.WO_TEXT5,d.WO_TEXT6,d.WO_DATE1,d.WO_DATE2,d.WO_DATE3,d.WO_DATE4,d.WO_NUM1,d.WO_INT1,d.FT,d.STATUS,d.AGENTDATE,d.AGENTLEVEL,d.AWS_NUM,d.LOOKUP1,d.LOOKUP2,d.TaskLookup3,d.TaskLookup4,d.TaskLookup5,d.TaskLookup6,d.TaskLookup7,d.TaskLookup8,d.EMAILADDR,d.USERID,d.PARENTWOID,d.WOTYPE2,d.WOTYPE3,d.ATTACHCOUNT,d.WorkOrderTypeId,d.WorkOrderStatusId,d.AssignmentNumber,d.PolicyComponentId,d.PolicyPendingDueAlarmDate,d.PolicyOverdueAlarmDate,d.ExpComplPendingDueAlarmDate,d.ExpComplOverdueAlarmDate,d.PolicyDueDate,d.WorkOrderTemplateName,d.SLANAME
	from inserted i full outer join deleted d on i.WOID = d.WOID
open cChangeLogColumns
fetch next from cChangeLogColumns into @cPK
	,@iGUIDO,@iWO_NUM,@iTASK,@iTYPE,@iREQUEST,@iREQDATE,@iOPENBY,@iOPENDATE,@iCLSDBY,@iCLSDDATE,@iELAPSETIME,@iELAPSEMIN,@iPRIORITY,@iDUEDATE,@iRESPONS,@iASSNDATE,@iCOMPLETED,@iHOURS,@iRATE,@iCHARGE,@iWS_NUM,@iDEPT_NUM,@iDEPT,@iPHONE,@iPHONE_EXT,@iLOCATION,@iWO_TEXT1,@iWO_TEXT2,@iWO_TEXT3,@iWO_TEXT4,@iWO_TEXT5,@iWO_TEXT6,@iWO_DATE1,@iWO_DATE2,@iWO_DATE3,@iWO_DATE4,@iWO_NUM1,@iWO_INT1,@iFT,@iSTATUS,@iAGENTDATE,@iAGENTLEVEL,@iAWS_NUM,@iLOOKUP1,@iLOOKUP2,@iTaskLookup3,@iTaskLookup4,@iTaskLookup5,@iTaskLookup6,@iTaskLookup7,@iTaskLookup8,@iEMAILADDR,@iUSERID,@iPARENTWOID,@iWOTYPE2,@iWOTYPE3,@iATTACHCOUNT,@iWorkOrderTypeId,@iWorkOrderStatusId,@iAssignmentNumber,@iPolicyComponentId,@iPolicyPendingDueAlarmDate,@iPolicyOverdueAlarmDate,@iExpComplPendingDueAlarmDate,@iExpComplOverdueAlarmDate,@iPolicyDueDate,@iWorkOrderTemplateName,@iSLANAME
	,@dGUIDO,@dWO_NUM,@dTASK,@dTYPE,@dREQUEST,@dREQDATE,@dOPENBY,@dOPENDATE,@dCLSDBY,@dCLSDDATE,@dELAPSETIME,@dELAPSEMIN,@dPRIORITY,@dDUEDATE,@dRESPONS,@dASSNDATE,@dCOMPLETED,@dHOURS,@dRATE,@dCHARGE,@dWS_NUM,@dDEPT_NUM,@dDEPT,@dPHONE,@dPHONE_EXT,@dLOCATION,@dWO_TEXT1,@dWO_TEXT2,@dWO_TEXT3,@dWO_TEXT4,@dWO_TEXT5,@dWO_TEXT6,@dWO_DATE1,@dWO_DATE2,@dWO_DATE3,@dWO_DATE4,@dWO_NUM1,@dWO_INT1,@dFT,@dSTATUS,@dAGENTDATE,@dAGENTLEVEL,@dAWS_NUM,@dLOOKUP1,@dLOOKUP2,@dTaskLookup3,@dTaskLookup4,@dTaskLookup5,@dTaskLookup6,@dTaskLookup7,@dTaskLookup8,@dEMAILADDR,@dUSERID,@dPARENTWOID,@dWOTYPE2,@dWOTYPE3,@dATTACHCOUNT,@dWorkOrderTypeId,@dWorkOrderStatusId,@dAssignmentNumber,@dPolicyComponentId,@dPolicyPendingDueAlarmDate,@dPolicyOverdueAlarmDate,@dExpComplPendingDueAlarmDate,@dExpComplOverdueAlarmDate,@dPolicyDueDate,@dWorkOrderTemplateName,@dSLANAME
while @@FETCH_STATUS = 0
	begin
	  -- Log the row change event
		set @DbChangeLogId = null
		exec DbChangeLog$Ins
			@DbChangeLogId = @DbChangeLogId output,
			@DbChangeLogTransactionId = @DbChangeLogTransactionId,
			@CreatedTime = @CreatedTime,
			@PrincipalName = @PrincipalName,
			@PkValue = @cPK,
			@PkColumnId = @PKColumnId,
			@ActivitySystemResourceId = @ActivitySystemResourceId
	  -- Audit Updates for each of the columns that are audited
  -- Now record changes for each of the columns that are autologged
  IF @dGUIDO != @iGUIDO
	OR (@dGUIDO is null and @iGUIDO is not null)
 	OR (@dGUIDO is not null and @iGUIDO is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'GUIDO',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dGUIDO,
		@StringValueNew = @iGUIDO,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
    -- temporarily correct the wo_num default for insert only
    -- we will fix permanently later on down the line
    if @InsertUpdateDeleteCode = 'I'
	and @iwo_num is null
    begin
      select @iwo_num = @cPK  -- the woid
    end
	IF @dWO_NUM != @iWO_NUM
	OR (@dWO_NUM is null and @iWO_NUM is not null)
 	OR (@dWO_NUM is not null and @iWO_NUM is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_NUM',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = @dWO_NUM,
		@IntValueNew = @iWO_NUM,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'integer'
	  end
	IF @dTASK != @iTASK
	OR (@dTASK is null and @iTASK is not null)
 	OR (@dTASK is not null and @iTASK is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TASK',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTASK,
		@StringValueNew = @iTASK,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(100)'
	  end
	IF @dTYPE != @iTYPE
	OR (@dTYPE is null and @iTYPE is not null)
 	OR (@dTYPE is not null and @iTYPE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TYPE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTYPE,
		@StringValueNew = @iTYPE,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dREQUEST != @iREQUEST
	OR (@dREQUEST is null and @iREQUEST is not null)
 	OR (@dREQUEST is not null and @iREQUEST is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'REQUEST',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dREQUEST,
		@StringValueNew = @iREQUEST,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(50)'
	  end
	IF @dREQDATE != @iREQDATE
	OR (@dREQDATE is null and @iREQDATE is not null)
 	OR (@dREQDATE is not null and @iREQDATE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'REQDATE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dREQDATE,
		@DateValueNew = @iREQDATE,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dOPENBY != @iOPENBY
	OR (@dOPENBY is null and @iOPENBY is not null)
 	OR (@dOPENBY is not null and @iOPENBY is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'OPENBY',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dOPENBY,
		@StringValueNew = @iOPENBY,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(255)'
	  end
	IF @dOPENDATE != @iOPENDATE
	OR (@dOPENDATE is null and @iOPENDATE is not null)
 	OR (@dOPENDATE is not null and @iOPENDATE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'OPENDATE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dOPENDATE,
		@DateValueNew = @iOPENDATE,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dCLSDBY != @iCLSDBY
	OR (@dCLSDBY is null and @iCLSDBY is not null)
 	OR (@dCLSDBY is not null and @iCLSDBY is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'CLSDBY',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dCLSDBY,
		@StringValueNew = @iCLSDBY,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(255)'
	  end
	IF @dCLSDDATE != @iCLSDDATE
	OR (@dCLSDDATE is null and @iCLSDDATE is not null)
 	OR (@dCLSDDATE is not null and @iCLSDDATE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'CLSDDATE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dCLSDDATE,
		@DateValueNew = @iCLSDDATE,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dELAPSETIME != @iELAPSETIME
	OR (@dELAPSETIME is null and @iELAPSETIME is not null)
 	OR (@dELAPSETIME is not null and @iELAPSETIME is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'ELAPSETIME',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dELAPSETIME,
		@StringValueNew = @iELAPSETIME,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(20)'
	  end
	IF @dELAPSEMIN != @iELAPSEMIN
	OR (@dELAPSEMIN is null and @iELAPSEMIN is not null)
 	OR (@dELAPSEMIN is not null and @iELAPSEMIN is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'ELAPSEMIN',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = @dELAPSEMIN,
		@IntValueNew = @iELAPSEMIN,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'integer'
	  end
	IF @dPRIORITY != @iPRIORITY
	OR (@dPRIORITY is null and @iPRIORITY is not null)
 	OR (@dPRIORITY is not null and @iPRIORITY is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PRIORITY',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dPRIORITY,
		@StringValueNew = @iPRIORITY,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dDUEDATE != @iDUEDATE
	OR (@dDUEDATE is null and @iDUEDATE is not null)
 	OR (@dDUEDATE is not null and @iDUEDATE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'DUEDATE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dDUEDATE,
		@DateValueNew = @iDUEDATE,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dRESPONS != @iRESPONS
	OR (@dRESPONS is null and @iRESPONS is not null)
 	OR (@dRESPONS is not null and @iRESPONS is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'RESPONS',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dRESPONS,
		@StringValueNew = @iRESPONS,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dASSNDATE != @iASSNDATE
	OR (@dASSNDATE is null and @iASSNDATE is not null)
 	OR (@dASSNDATE is not null and @iASSNDATE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'ASSNDATE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dASSNDATE,
		@DateValueNew = @iASSNDATE,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dCOMPLETED != @iCOMPLETED
	OR (@dCOMPLETED is null and @iCOMPLETED is not null)
 	OR (@dCOMPLETED is not null and @iCOMPLETED is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'COMPLETED',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dCOMPLETED,
		@DateValueNew = @iCOMPLETED,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dHOURS != @iHOURS
	OR (@dHOURS is null and @iHOURS is not null)
 	OR (@dHOURS is not null and @iHOURS is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'HOURS',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dHOURS,
		@StringValueNew = @iHOURS,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'float'
	  end
	IF @dRATE != @iRATE
	OR (@dRATE is null and @iRATE is not null)
 	OR (@dRATE is not null and @iRATE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'RATE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dRATE,
		@StringValueNew = @iRATE,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'float'
	  end
	IF @dCHARGE != @iCHARGE
	OR (@dCHARGE is null and @iCHARGE is not null)
 	OR (@dCHARGE is not null and @iCHARGE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'CHARGE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dCHARGE,
		@StringValueNew = @iCHARGE,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'float'
	  end
	IF @dWS_NUM != @iWS_NUM
	OR (@dWS_NUM is null and @iWS_NUM is not null)
 	OR (@dWS_NUM is not null and @iWS_NUM is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WS_NUM',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = @dWS_NUM,
		@IntValueNew = @iWS_NUM,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'integer'
	  end
	IF @dDEPT_NUM != @iDEPT_NUM
	OR (@dDEPT_NUM is null and @iDEPT_NUM is not null)
 	OR (@dDEPT_NUM is not null and @iDEPT_NUM is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'DEPT_NUM',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dDEPT_NUM,
		@StringValueNew = @iDEPT_NUM,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dDEPT != @iDEPT
	OR (@dDEPT is null and @iDEPT is not null)
 	OR (@dDEPT is not null and @iDEPT is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'DEPT',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dDEPT,
		@StringValueNew = @iDEPT,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dPHONE != @iPHONE
	OR (@dPHONE is null and @iPHONE is not null)
 	OR (@dPHONE is not null and @iPHONE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PHONE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dPHONE,
		@StringValueNew = @iPHONE,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dPHONE_EXT != @iPHONE_EXT
	OR (@dPHONE_EXT is null and @iPHONE_EXT is not null)
 	OR (@dPHONE_EXT is not null and @iPHONE_EXT is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PHONE_EXT',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dPHONE_EXT,
		@StringValueNew = @iPHONE_EXT,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(8)'
	  end
	IF @dLOCATION != @iLOCATION
	OR (@dLOCATION is null and @iLOCATION is not null)
 	OR (@dLOCATION is not null and @iLOCATION is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'LOCATION',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dLOCATION,
		@StringValueNew = @iLOCATION,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dWO_TEXT1 != @iWO_TEXT1
	OR (@dWO_TEXT1 is null and @iWO_TEXT1 is not null)
 	OR (@dWO_TEXT1 is not null and @iWO_TEXT1 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_TEXT1',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWO_TEXT1,
		@StringValueNew = @iWO_TEXT1,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
	IF @dWO_TEXT2 != @iWO_TEXT2
	OR (@dWO_TEXT2 is null and @iWO_TEXT2 is not null)
 	OR (@dWO_TEXT2 is not null and @iWO_TEXT2 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_TEXT2',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWO_TEXT2,
		@StringValueNew = @iWO_TEXT2,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
	IF @dWO_TEXT3 != @iWO_TEXT3
	OR (@dWO_TEXT3 is null and @iWO_TEXT3 is not null)
 	OR (@dWO_TEXT3 is not null and @iWO_TEXT3 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_TEXT3',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWO_TEXT3,
		@StringValueNew = @iWO_TEXT3,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
	IF @dWO_TEXT4 != @iWO_TEXT4
	OR (@dWO_TEXT4 is null and @iWO_TEXT4 is not null)
 	OR (@dWO_TEXT4 is not null and @iWO_TEXT4 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_TEXT4',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWO_TEXT4,
		@StringValueNew = @iWO_TEXT4,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
	IF @dWO_TEXT5 != @iWO_TEXT5
	OR (@dWO_TEXT5 is null and @iWO_TEXT5 is not null)
 	OR (@dWO_TEXT5 is not null and @iWO_TEXT5 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_TEXT5',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWO_TEXT5,
		@StringValueNew = @iWO_TEXT5,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
	IF @dWO_TEXT6 != @iWO_TEXT6
	OR (@dWO_TEXT6 is null and @iWO_TEXT6 is not null)
 	OR (@dWO_TEXT6 is not null and @iWO_TEXT6 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_TEXT6',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWO_TEXT6,
		@StringValueNew = @iWO_TEXT6,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
	IF @dWO_DATE1 != @iWO_DATE1
	OR (@dWO_DATE1 is null and @iWO_DATE1 is not null)
 	OR (@dWO_DATE1 is not null and @iWO_DATE1 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_DATE1',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dWO_DATE1,
		@DateValueNew = @iWO_DATE1,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dWO_DATE2 != @iWO_DATE2
	OR (@dWO_DATE2 is null and @iWO_DATE2 is not null)
 	OR (@dWO_DATE2 is not null and @iWO_DATE2 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_DATE2',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dWO_DATE2,
		@DateValueNew = @iWO_DATE2,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dWO_DATE3 != @iWO_DATE3
	OR (@dWO_DATE3 is null and @iWO_DATE3 is not null)
 	OR (@dWO_DATE3 is not null and @iWO_DATE3 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_DATE3',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dWO_DATE3,
		@DateValueNew = @iWO_DATE3,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dWO_DATE4 != @iWO_DATE4
	OR (@dWO_DATE4 is null and @iWO_DATE4 is not null)
 	OR (@dWO_DATE4 is not null and @iWO_DATE4 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_DATE4',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dWO_DATE4,
		@DateValueNew = @iWO_DATE4,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dWO_NUM1 != @iWO_NUM1
	OR (@dWO_NUM1 is null and @iWO_NUM1 is not null)
 	OR (@dWO_NUM1 is not null and @iWO_NUM1 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_NUM1',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWO_NUM1,
		@StringValueNew = @iWO_NUM1,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'float'
	  end
	IF @dWO_INT1 != @iWO_INT1
	OR (@dWO_INT1 is null and @iWO_INT1 is not null)
 	OR (@dWO_INT1 is not null and @iWO_INT1 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WO_INT1',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = @dWO_INT1,
		@IntValueNew = @iWO_INT1,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'integer'
	  end
	IF @dSTATUS != @iSTATUS
	OR (@dSTATUS is null and @iSTATUS is not null)
 	OR (@dSTATUS is not null and @iSTATUS is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'STATUS',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dSTATUS,
		@StringValueNew = @iSTATUS,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(25)'
	  end
	IF @dAGENTDATE != @iAGENTDATE
	OR (@dAGENTDATE is null and @iAGENTDATE is not null)
 	OR (@dAGENTDATE is not null and @iAGENTDATE is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'AGENTDATE',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dAGENTDATE,
		@DateValueNew = @iAGENTDATE,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dAGENTLEVEL != @iAGENTLEVEL
	OR (@dAGENTLEVEL is null and @iAGENTLEVEL is not null)
 	OR (@dAGENTLEVEL is not null and @iAGENTLEVEL is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'AGENTLEVEL',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = @dAGENTLEVEL,
		@IntValueNew = @iAGENTLEVEL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'tinyint'
	  end
	IF @dAWS_NUM != @iAWS_NUM
	OR (@dAWS_NUM is null and @iAWS_NUM is not null)
 	OR (@dAWS_NUM is not null and @iAWS_NUM is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'AWS_NUM',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dAWS_NUM,
		@StringValueNew = @iAWS_NUM,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(40)'
	  end
	IF @dLOOKUP1 != @iLOOKUP1
	OR (@dLOOKUP1 is null and @iLOOKUP1 is not null)
 	OR (@dLOOKUP1 is not null and @iLOOKUP1 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'LOOKUP1',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dLOOKUP1,
		@StringValueNew = @iLOOKUP1,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dLOOKUP2 != @iLOOKUP2
	OR (@dLOOKUP2 is null and @iLOOKUP2 is not null)
 	OR (@dLOOKUP2 is not null and @iLOOKUP2 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'LOOKUP2',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dLOOKUP2,
		@StringValueNew = @iLOOKUP2,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dTaskLookup3 != @iTaskLookup3
	OR (@dTaskLookup3 is null and @iTaskLookup3 is not null)
 	OR (@dTaskLookup3 is not null and @iTaskLookup3 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TaskLookup3',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTaskLookup3,
		@StringValueNew = @iTaskLookup3,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dTaskLookup4 != @iTaskLookup4
	OR (@dTaskLookup4 is null and @iTaskLookup4 is not null)
 	OR (@dTaskLookup4 is not null and @iTaskLookup4 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TaskLookup4',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTaskLookup4,
		@StringValueNew = @iTaskLookup4,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'VARCHAR(30)'
	  end
	IF @dTaskLookup5 != @iTaskLookup5
	OR (@dTaskLookup5 is null and @iTaskLookup5 is not null)
 	OR (@dTaskLookup5 is not null and @iTaskLookup5 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TaskLookup5',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTaskLookup5,
		@StringValueNew = @iTaskLookup5,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'VARCHAR(30)'
	  end
	IF @dTaskLookup6 != @iTaskLookup6
	OR (@dTaskLookup6 is null and @iTaskLookup6 is not null)
 	OR (@dTaskLookup6 is not null and @iTaskLookup6 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TaskLookup6',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTaskLookup6,
		@StringValueNew = @iTaskLookup6,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'VARCHAR(30)'
	  end
	IF @dTaskLookup7 != @iTaskLookup7
	OR (@dTaskLookup7 is null and @iTaskLookup7 is not null)
 	OR (@dTaskLookup7 is not null and @iTaskLookup7 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TaskLookup7',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTaskLookup7,
		@StringValueNew = @iTaskLookup7,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'VARCHAR(30)'
	  end
	IF @dTaskLookup8 != @iTaskLookup8
	OR (@dTaskLookup8 is null and @iTaskLookup8 is not null)
 	OR (@dTaskLookup8 is not null and @iTaskLookup8 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'TaskLookup8',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dTaskLookup8,
		@StringValueNew = @iTaskLookup8,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'VARCHAR(30)'
	  end
	IF @dEMAILADDR != @iEMAILADDR
	OR (@dEMAILADDR is null and @iEMAILADDR is not null)
 	OR (@dEMAILADDR is not null and @iEMAILADDR is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'EMAILADDR',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dEMAILADDR,
		@StringValueNew = @iEMAILADDR,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(100)'
	  end
	IF @dUSERID != @iUSERID
	OR (@dUSERID is null and @iUSERID is not null)
 	OR (@dUSERID is not null and @iUSERID is null)
  	  begin
		-- manually added
		select
			@tStringValueOld = dbo.TIUSER$getFULLNAME(@dUSERID),
			@tStringValueNew = dbo.TIUSER$getFULLNAME(@iUSERID)
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'USERID',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @tStringValueOld,
		@StringValueNew = @tStringValueNew,
		@IntValueOld = @dUSERID,
		@IntValueNew = @iUSERID,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 1,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'integer'
	  end
    -- temporarily correct the parentwoid relationship for insert only
    -- we will fix permanently later on down the line
    if @InsertUpdateDeleteCode = 'I'
    and @iWorkOrderTypeId in (0,2) -- Workorder, Workorder Template
	and @iPARENTWOID is null
    begin
      select @iPARENTWOID = @cPK  -- the woid
    end
    --
	IF @dPARENTWOID != @iPARENTWOID
	OR (@dPARENTWOID is null and @iPARENTWOID is not null)
 	OR (@dPARENTWOID is not null and @iPARENTWOID is null)
  	  begin
		-- manually added
		-- record the historical name of the parent task on the child
		select
			@tStringValueOld = dbo.TABLENAME$getTASK(@dPARENTWOID),
			@tStringValueNew = dbo.TABLENAME$getTASK(@iPARENTWOID)
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PARENTWOID',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @tStringValueOld,
		@StringValueNew = @tStringValueNew,
		@IntValueOld = @dPARENTWOID,
		@IntValueNew = @iPARENTWOID,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 1,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'int'
		-- manually added
		-- record the old name of the child task
		-- as a delete on the old parent
        if @dPARENTWOID is not null
		and @dWorkOrderTypeId in (1,3) -- Assignment, Assignment Template
		begin
			-- get the column dictionary info for DELETE of pk1 'TABLENAME.WOID'
			exec DbChangeLogColumn$InsUpd
        		@DbChangeLogColumnId = @dPKColumnId output,
        		@TableName =  'TABLENAME',
        		@ColumnName = 'WOID',
        		@IsPk = 1,
        		@InsertUpdateDeleteCode = 'D',--@InsertUpdateDeleteCode,
        		@TableHasMultiPk = @TableHasMultiPk,
        		@DbChangeLogDataType = 'INT',
        		@DBMSDatatype = 'integer'
		    -- Log the row change event for the parent delete
			set @dDbChangeLogId = null
			exec DbChangeLog$Ins
				@DbChangeLogId = @dDbChangeLogId output,
				@DbChangeLogTransactionId = @DbChangeLogTransactionId,
				@CreatedTime = @CreatedTime,
				@PrincipalName = @PrincipalName,
				@PkValue = @dPARENTWOID, --@cPK,
				@PkColumnId = @dPKColumnId,
				@ActivitySystemResourceId = @ActivitySystemResourceId
			exec DbChangeLogValue$Ins
			@DbChangeLogId = @dDbChangeLogId,
			@TableName = 'TABLENAME',
			@ColumnName = 'AssignmentId',--'PARENTWOID',
			@InsertUpdateDeleteCode = 'D',--@InsertUpdateDeleteCode,
			@StringValueOld = @dTASK, --@tStringValueOld,
			@StringValueNew = NULL,
			@IntValueOld = @cPK,--@dPARENTWOID,
			@IntValueNew = NULL,--@iPARENTWOID,
			@DateValueOld = NULL,
			@DateValueNew = NULL,
			@TextValueOld = NULL,
			@TextValueNew = NULL,
			@HasConvenienceValue = 1,
			@IsPk = 0,
			@TableHasMultiPk = @TableHasMultiPk,
			@DbChangeLogDataType = 'INT',
			@DBMSDatatype = 'int'
		end
		-- manually added
		-- record the new name of the child task
		-- as an add on the new parent
        if @iPARENTWOID is not null
		and @iWorkOrderTypeId in (1,3) -- Assignment, Assignment Template
		begin
			-- get the column dictionary info for INSERT of pk1 'TABLENAME.WOID'
			exec DbChangeLogColumn$InsUpd
        		@DbChangeLogColumnId = @iPKColumnId output,
        		@TableName =  'TABLENAME',
        		@ColumnName = 'WOID',
        		@IsPk = 1,
        		@InsertUpdateDeleteCode = 'I',--@InsertUpdateDeleteCode,
        		@TableHasMultiPk = @TableHasMultiPk,
        		@DbChangeLogDataType = 'INT',
        		@DBMSDatatype = 'integer'
		    -- Log the row change event for the parent INSERT
			set @iDbChangeLogId = null
			exec DbChangeLog$Ins
				@DbChangeLogId = @iDbChangeLogId output,
				@DbChangeLogTransactionId = @DbChangeLogTransactionId,
				@CreatedTime = @CreatedTime,
				@PrincipalName = @PrincipalName,
				@PkValue = @iPARENTWOID, --@cPK,
				@PkColumnId = @iPKColumnId,
				@ActivitySystemResourceId = @ActivitySystemResourceId
			exec DbChangeLogValue$Ins
			@DbChangeLogId = @iDbChangeLogId,
			@TableName = 'TABLENAME',
			@ColumnName = 'AssignmentId',--'PARENTWOID',
			@InsertUpdateDeleteCode = 'I',--@InsertUpdateDeleteCode,
			@StringValueOld = NULL,
			@StringValueNew = @iTASK, --@tStringValueNew,
			@IntValueOld = NULL,--@dPARENTWOID,
			@IntValueNew = @cPK,-- @iPARENTWOID,
			@DateValueOld = NULL,
			@DateValueNew = NULL,
			@TextValueOld = NULL,
			@TextValueNew = NULL,
			@HasConvenienceValue = 1,
			@IsPk = 0,
			@TableHasMultiPk = @TableHasMultiPk,
			@DbChangeLogDataType = 'INT',
			@DBMSDatatype = 'int'
		end
	  end
	IF @dWOTYPE2 != @iWOTYPE2
	OR (@dWOTYPE2 is null and @iWOTYPE2 is not null)
 	OR (@dWOTYPE2 is not null and @iWOTYPE2 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WOTYPE2',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWOTYPE2,
		@StringValueNew = @iWOTYPE2,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dWOTYPE3 != @iWOTYPE3
	OR (@dWOTYPE3 is null and @iWOTYPE3 is not null)
 	OR (@dWOTYPE3 is not null and @iWOTYPE3 is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WOTYPE3',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWOTYPE3,
		@StringValueNew = @iWOTYPE3,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
	IF @dATTACHCOUNT != @iATTACHCOUNT
	OR (@dATTACHCOUNT is null and @iATTACHCOUNT is not null)
 	OR (@dATTACHCOUNT is not null and @iATTACHCOUNT is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'ATTACHCOUNT',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = @dATTACHCOUNT,
		@IntValueNew = @iATTACHCOUNT,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'integer'
	  end
	IF @dWorkOrderTypeId != @iWorkOrderTypeId
	OR (@dWorkOrderTypeId is null and @iWorkOrderTypeId is not null)
 	OR (@dWorkOrderTypeId is not null and @iWorkOrderTypeId is null)
  	  begin
  	        -- manually added
		select
			@tStringValueOld = dbo.WorkOrderType$getWorkOrderTypeName(@dWorkOrderTypeId),
			@tStringValueNew = dbo.WorkOrderType$getWorkOrderTypeName(@iWorkOrderTypeId)
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WorkOrderTypeId',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @tStringValueOld,
		@StringValueNew = @tStringValueNew,
		@IntValueOld = @dWorkOrderTypeId,
		@IntValueNew = @iWorkOrderTypeId,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 1,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'int'
	  end
	IF @dWorkOrderStatusId != @iWorkOrderStatusId
	OR (@dWorkOrderStatusId is null and @iWorkOrderStatusId is not null)
 	OR (@dWorkOrderStatusId is not null and @iWorkOrderStatusId is null)
  	  begin
  	        -- manually added
		select
			@tStringValueOld = dbo.WorkOrderStatus$getWorkOrderStatusName(@dWorkOrderStatusId),
			@tStringValueNew = dbo.WorkOrderStatus$getWorkOrderStatusName(@iWorkOrderStatusId)
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WorkOrderStatusId',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @tStringValueOld,
		@StringValueNew = @tStringValueNew,
		@IntValueOld = @dWorkOrderStatusId,
		@IntValueNew = @iWorkOrderStatusId,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 1,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'int'
	  end
	IF @dAssignmentNumber != @iAssignmentNumber
	OR (@dAssignmentNumber is null and @iAssignmentNumber is not null)
 	OR (@dAssignmentNumber is not null and @iAssignmentNumber is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'AssignmentNumber',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = @dAssignmentNumber,
		@IntValueNew = @iAssignmentNumber,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'int'
	  end
	IF @dPolicyComponentId != @iPolicyComponentId
	OR (@dPolicyComponentId is null and @iPolicyComponentId is not null)
 	OR (@dPolicyComponentId is not null and @iPolicyComponentId is null)
  	  begin
        	-- manually added
        	select
        		@tStringValueOld = dbo.PolicyComponent$getName(@dPolicyComponentId),
        		@tStringValueNew = dbo.PolicyComponent$getName(@iPolicyComponentId)
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PolicyComponentId',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @tStringValueOld,
		@StringValueNew = @tStringValueNew,
		@IntValueOld = @dPolicyComponentId,
		@IntValueNew = @iPolicyComponentId,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 1,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'INT',
		@DBMSDatatype = 'int'
	  end
	IF @dPolicyPendingDueAlarmDate != @iPolicyPendingDueAlarmDate
	OR (@dPolicyPendingDueAlarmDate is null and @iPolicyPendingDueAlarmDate is not null)
 	OR (@dPolicyPendingDueAlarmDate is not null and @iPolicyPendingDueAlarmDate is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PolicyPendingDueAlarmDate',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dPolicyPendingDueAlarmDate,
		@DateValueNew = @iPolicyPendingDueAlarmDate,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dPolicyOverdueAlarmDate != @iPolicyOverdueAlarmDate
	OR (@dPolicyOverdueAlarmDate is null and @iPolicyOverdueAlarmDate is not null)
 	OR (@dPolicyOverdueAlarmDate is not null and @iPolicyOverdueAlarmDate is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PolicyOverdueAlarmDate',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dPolicyOverdueAlarmDate,
		@DateValueNew = @iPolicyOverdueAlarmDate,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dExpComplPendingDueAlarmDate != @iExpComplPendingDueAlarmDate
	OR (@dExpComplPendingDueAlarmDate is null and @iExpComplPendingDueAlarmDate is not null)
 	OR (@dExpComplPendingDueAlarmDate is not null and @iExpComplPendingDueAlarmDate is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'ExpComplPendingDueAlarmDate',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dExpComplPendingDueAlarmDate,
		@DateValueNew = @iExpComplPendingDueAlarmDate,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dExpComplOverdueAlarmDate != @iExpComplOverdueAlarmDate
	OR (@dExpComplOverdueAlarmDate is null and @iExpComplOverdueAlarmDate is not null)
 	OR (@dExpComplOverdueAlarmDate is not null and @iExpComplOverdueAlarmDate is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'ExpComplOverdueAlarmDate',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dExpComplOverdueAlarmDate,
		@DateValueNew = @iExpComplOverdueAlarmDate,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dPolicyDueDate != @iPolicyDueDate
	OR (@dPolicyDueDate is null and @iPolicyDueDate is not null)
 	OR (@dPolicyDueDate is not null and @iPolicyDueDate is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'PolicyDueDate',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = NULL,
		@StringValueNew = NULL,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = @dPolicyDueDate,
		@DateValueNew = @iPolicyDueDate,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'DATE',
		@DBMSDatatype = 'datetime'
	  end
	IF @dWorkOrderTemplateName != @iWorkOrderTemplateName
	OR (@dWorkOrderTemplateName is null and @iWorkOrderTemplateName is not null)
 	OR (@dWorkOrderTemplateName is not null and @iWorkOrderTemplateName is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'WorkOrderTemplateName',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dWorkOrderTemplateName,
		@StringValueNew = @iWorkOrderTemplateName,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(255)'
	  end
	IF @dSLANAME != @iSLANAME
	OR (@dSLANAME is null and @iSLANAME is not null)
 	OR (@dSLANAME is not null and @iSLANAME is null)
  	  begin
		exec DbChangeLogValue$Ins
		@DbChangeLogId = @DbChangeLogId,
		@TableName = 'TABLENAME',
		@ColumnName = 'SLANAME',
		@InsertUpdateDeleteCode = @InsertUpdateDeleteCode,
		@StringValueOld = @dSLANAME,
		@StringValueNew = @iSLANAME,
		@IntValueOld = NULL,
		@IntValueNew = NULL,
		@DateValueOld = NULL,
		@DateValueNew = NULL,
		@TextValueOld = NULL,
		@TextValueNew = NULL,
		@HasConvenienceValue = 0,
		@IsPk = 0,
		@TableHasMultiPk = @TableHasMultiPk,
		@DbChangeLogDataType = 'VARCHAR',
		@DBMSDatatype = 'varchar(30)'
	  end
		fetch next from cChangeLogColumns into @cPK
			,@iGUIDO,@iWO_NUM,@iTASK,@iTYPE,@iREQUEST,@iREQDATE,@iOPENBY,@iOPENDATE,@iCLSDBY,@iCLSDDATE,@iELAPSETIME,@iELAPSEMIN,@iPRIORITY,@iDUEDATE,@iRESPONS,@iASSNDATE,@iCOMPLETED,@iHOURS,@iRATE,@iCHARGE,@iWS_NUM,@iDEPT_NUM,@iDEPT,@iPHONE,@iPHONE_EXT,@iLOCATION,@iWO_TEXT1,@iWO_TEXT2,@iWO_TEXT3,@iWO_TEXT4,@iWO_TEXT5,@iWO_TEXT6,@iWO_DATE1,@iWO_DATE2,@iWO_DATE3,@iWO_DATE4,@iWO_NUM1,@iWO_INT1,@iFT,@iSTATUS,@iAGENTDATE,@iAGENTLEVEL,@iAWS_NUM,@iLOOKUP1,@iLOOKUP2,@iTaskLookup3,@iTaskLookup4,@iTaskLookup5,@iTaskLookup6,@iTaskLookup7,@iTaskLookup8,@iEMAILADDR,@iUSERID,@iPARENTWOID,@iWOTYPE2,@iWOTYPE3,@iATTACHCOUNT,@iWorkOrderTypeId,@iWorkOrderStatusId,@iAssignmentNumber,@iPolicyComponentId,@iPolicyPendingDueAlarmDate,@iPolicyOverdueAlarmDate,@iExpComplPendingDueAlarmDate,@iExpComplOverdueAlarmDate,@iPolicyDueDate,@iWorkOrderTemplateName,@iSLANAME
			,@dGUIDO,@dWO_NUM,@dTASK,@dTYPE,@dREQUEST,@dREQDATE,@dOPENBY,@dOPENDATE,@dCLSDBY,@dCLSDDATE,@dELAPSETIME,@dELAPSEMIN,@dPRIORITY,@dDUEDATE,@dRESPONS,@dASSNDATE,@

gmmastros · Jul 2, 2009

Thank goodness for scroll bars. [smile]

add one line, at the top, like this...

Code:

ALTER trigger [dbo].[TABLENAME$AutoLog] on [dbo].[TABLENAME] FOR DELETE, INSERT, UPDATE
AS
begin
set nocount on
[!]set ansi_warnings off[/!]
-- The rest of your code here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

tyutghf · Jul 2, 2009

I am on SQL Server 2005, I right click the trigger and select modify, change it but when I save it it saves it as a different name. How do I actually change the Trigger?

thanks again

gmmastros · Jul 2, 2009

After modifying the code, simply press F5 on your keyboard.

This will run the modify trigger script, compiling it in to the database. The save button is really just a handy way to save the text of the script to an ASCII file.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

tyutghf · Jul 2, 2009

thanks, much appreciated

tyutghf · Jul 2, 2009

There are two other triggers for this table, I didn`t think they did much but obviously they do.

The one I pasted above is called TABLENAME$autolog and these others are called z$TABLENAME_TU and z$TABLENAME_TD

If I leave these without the ansi_off then I get the same error message as my orignial post, if I add ansi_off I get a new message

Attempt to Update Table "TABLENAME" failed for LOCATION = "USA" with access level = "0"

I am thinking the z$TABLENAME_TU is the trigger for updating, is this causing me the error now?

thanks again matey

Code:

USE [DATABASENAME]
GO
/****** Object:  Trigger [dbo].[z$TABLENAME_TU]    Script Date: 07/02/2009 15:22:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[z$TABLENAME_TU] on [dbo].[TABLENAME] after update
as
begin
   set nocount on
SET ANSI_WARNINGS OFF
  declare	@errno   int,
	@errmsg  varchar(255),
	@badValue varchar(30),
	@badValueFlag tinyint,
	@securityColumnName varchar(30)
  	if dbo.zf$shouldBypassSecurity() = 1 goto bypass -- bypass row level security since dbo can do anything anyway
	if trigger_nestlevel( object_ID('Tasks$Autolog')) > 0 GOTO bypass -- bypass recursive firing from autolog
	select @badValue = min(DEPT)
	from deleted
	where dbo.zf$CanModifyDept(deleted.DEPT) is null
	if @badValue is not null
	begin
		--lets find out whats wrong
                select @badValueFlag = dbo.zf$CanViewDept(@badValue),
                       @errno  = dbo.AddOffsetToErrorNo(50003),
                       @securityColumnName='DEPT'
		goto error
	end
--	if update(DEPT)
--	begin
--  	     select @badValue = min(DEPT)
--  	     from inserted
--  	     where dbo.zf$CanModifyDept(inserted.DEPT) is null
--
--  	     if @badValue is not null
--  	     begin
--  		--lets find out whats wrong
--                  select @badValueFlag = dbo.zf$CanViewDept(@badValue),
--                         @errno  = dbo.AddOffsetToErrorNo(50004),
--                         @securityColumnName='DEPT'
--  		goto error
--  	     end
--	end
	select @badValue = min(LOCATION)
	from deleted
	where dbo.zf$CanModifyLocation(deleted.LOCATION) is null
	if @badValue is not null
	begin
		--lets find out whats wrong
                select @badValueFlag = dbo.zf$CanViewLocation(@badValue),
                       @errno  = dbo.AddOffsetToErrorNo(50003),
                       @securityColumnName='LOCATION'
		goto error
	end
--	if update(LOCATION)
--	begin
--  	     select @badValue = min(LOCATION)
--  	     from inserted
--  	     where dbo.zf$CanModifyLocation(inserted.LOCATION) is null
--
--  	     if @badValue is not null
--  	     begin
--  		--lets find out whats wrong
--                  select @badValueFlag = dbo.zf$CanViewLocation(@badValue),
--                         @errno  = dbo.AddOffsetToErrorNo(50004),
--                         @securityColumnName='LOCATION'
--  		goto error
--  	     end
--	end
	select @badValue = min(RESPONS)
	from deleted
	where dbo.zf$CanModifyStaff(deleted.RESPONS) is null
	if @badValue is not null
	begin
		--lets find out whats wrong
                select @badValueFlag = dbo.zf$CanViewStaff(@badValue),
                       @errno  = dbo.AddOffsetToErrorNo(50003),
                       @securityColumnName='RESPONS'
		goto error
	end
--	if update(RESPONS)
--	begin
--  	     select @badValue = min(RESPONS)
--  	     from inserted
--  	     where dbo.zf$CanModifyStaff(inserted.RESPONS) is null
--
--  	     if @badValue is not null
--  	     begin
--  		--lets find out whats wrong
--                  select @badValueFlag = dbo.zf$CanViewStaff(@badValue),
--                         @errno  = dbo.AddOffsetToErrorNo(50004),
--                         @securityColumnName='RESPONS'
--  		goto error
--  	     end
--	end
    return
error:
    if @badValueFlag is null select @badValueFlag = 0
    raiserror (@errno,16,1,'TABLENAME',@securityColumnName,@badValue,@badValueFlag)
    rollback transaction
bypass:
end

gmmastros · Jul 2, 2009

I suggest you create a post in the SQL Server forum. Someone there should be able to help better than me. Sorry.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

tyutghf · Jul 2, 2009

Thanks, I have posted in the SQL Server forum, hopefully someone over there can help.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Null value is eliminated error when updating table 1

tyutghf

Technical User

tyutghf

Technical User

gmmastros

Programmer

tyutghf

Technical User

gmmastros

Programmer

gmmastros

Programmer

tyutghf

Technical User

gmmastros

Programmer

tyutghf

Technical User

gmmastros

Programmer

tyutghf

Technical User

tyutghf

Technical User

gmmastros

Programmer

tyutghf

Technical User

Similar threads

Part and Inventory Search

Sponsor