August 7, 2008 at 3:49 am
I have been trying to work out what's wrong with this trigger. Sometimes it works (updates a new custom field within an application), sometimes it doesn't. But what I can say for certain is that when the variable @vote_entry is set to something static, it works every time.
UPDATETEXT is required because the destination field is ntext (which I cannot change), and the environment is SQL 2000 (which I also cannot change).
Your feedback and thoughts are much appreciated. Been trying everything I can think of.
---------------------------------------
CREATE TRIGGER apAddChangeVotingUpdateToJournal ON [ITSM_APPROVER_VOTES]
FOR UPDATE
AS
/* Inserts an entry in the Journal Field when a user adds their voting Result and Reason */
Declare @OID decimal
Declare @CHA_OID decimal
declare @CHA_APV_PER_OID decimal
declare @CHA_APV_RESULT_OLD varchar(10)
declare @CHA_APV_RESULT_NEW varchar(10)
declare @CHA_APV_REASON_OLD varchar(255)
declare @CHA_APV_REASON_NEW varchar(255)
declare @vote_entry varchar(4000)
declare @regoid decimal
declare @VOTING_PERSON varchar(100)
declare @UPDATE_PERSON varchar(100)
-- Ensure nothing is output to screen
set nocount on
set @CHA_OID = (select APV_APT_OID from DELETED)
set @CHA_APV_PER_OID = (select APV_PER_OID from DELETED)
set @CHA_APV_RESULT_OLD = (select CASE WHEN APV_APPROVED=1 THEN 'Yes' WHEN APV_APPROVED=0 THEN 'No' ELSE ' ' END from DELETED)
set @CHA_APV_RESULT_NEW = (select CASE WHEN APV_APPROVED=1 THEN 'Yes' WHEN APV_APPROVED=0 THEN 'No' ELSE ' ' END from INSERTED)
set @CHA_APV_REASON_OLD = (select APV_REASON from DELETED)
set @CHA_APV_REASON_NEW = (select APV_REASON from INSERTED)
set @VOTING_PERSON = (select PER_NAME from itsm_persons where @CHA_APV_PER_OID= PER_OID)
set @UPDATE_PERSON = (SELECT REP_ACCOUNTS.ACC_SHOWNAME
FROM ITSM_CHANGES INNER JOIN
REP_ACCOUNTS ON ITSM_CHANGES.REG_MODIFIED_BY_OID = REP_ACCOUNTS.ACC_OID
WHERE (ITSM_CHANGES.CHA_OID = @CHA_OID))
set @vote_entry='Approval Voting Update: '+cast(getdate()as varchar)+ ' by ' +@UPDATE_PERSON+char(13)+char(10)+'---------------------------------'+char(13)+char(10)+'Approver: '+@VOTING_PERSON+char(13)+char(10)+'Approval Result Changed From: '+@CHA_APV_RESULT_OLD+ ' to '+@CHA_APV_RESULT_NEW+char(13)+char(10)+'Voting Reason: '+ @CHA_APV_REASON_OLD+char(13)+char(10)+'---------------------------------'+char(13)+char(10)+char(13)+char(10)
DECLARE @ptrval binary(16)
if @CHA_OID NOT IN (select ITSM_CHA_CFT_64K001_oid from ITSM_CHA_CFT_64K001)
begin
INSERT INTO ITSM_CHA_CFT_64K001(A0$_CH_64K_001, ITSM_CHA_CFT_64K001_oid)
VALUES('',@CHA_OID)
SELECT @ptrval = TEXTPTR(A0$_CH_64K_001) FROM ITSM_CHA_CFT_64K001 WHERE ITSM_CHA_CFT_64K001_oid=@CHA_OID
UPDATETEXT ITSM_CHA_CFT_64K001.A0$_CH_64K_001 @ptrval 0 0 @vote_entry
end
if @CHA_OID IN (select ITSM_CHA_CFT_64K001_oid from ITSM_CHA_CFT_64K001)
begin
SELECT @ptrval = TEXTPTR(A0$_CH_64K_001) FROM ITSM_CHA_CFT_64K001 WHERE ITSM_CHA_CFT_64K001_oid=@CHA_OID
UPDATETEXT ITSM_CHA_CFT_64K001.A0$_CH_64K_001 @ptrval 0 0 @vote_entry
end
---------------------------------------
August 7, 2008 at 4:08 am
I haven't gone over the code in detail, but one thing jumps out. This is only going to work properly when a single row is updated.
set @CHA_OID = (select APV_APT_OID from DELETED)
set @CHA_APV_PER_OID = (select APV_PER_OID from DELETED)
set @CHA_APV_RESULT_OLD = (select CASE WHEN APV_APPROVED=1 THEN 'Yes' WHEN APV_APPROVED=0 THEN 'No' ELSE ' ' END from DELETED)
set @CHA_APV_RESULT_NEW = (select CASE WHEN APV_APPROVED=1 THEN 'Yes' WHEN APV_APPROVED=0 THEN 'No' ELSE ' ' END from INSERTED)
set @CHA_APV_REASON_OLD = (select APV_REASON from DELETED)
set @CHA_APV_REASON_NEW = (select APV_REASON from INSERTED)
This code assumes that there is only one row in inserted and updated. If more than one row was updated then there will be multiple rows in those pseudo-tables and you will get the values for one of them. No guarantees which one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply