for update trigger is not logging changes

The trigger below is not logging any changes to my log table. Please help!
USE [Pricing_test3]
GO
/****** Object:  Trigger [dbo].[LogOffer1PriceChanges]    Script Date: 11/17/2008 08:34:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:			Julie Liu
-- Create date:		11/13/2008
-- Description:     Log changes to tbl_Offer_1_price
-- =============================================
ALTER TRIGGER [dbo].[LogOffer1PriceChanges]
   ON [dbo].[tbl_Offer_1_Price]
   FOR UPDATE
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
		SET NOCOUNT ON;
 
		-- Insert statements for trigger here
 
		declare @dateTime		dateTime
		declare @user			varchar(50)
		declare @machine		varchar(50)
		declare @column			varchar(200)
		declare @prevalue		varchar(200)
		declare @updateValue	varchar(200)
		declare @IDofOffer		INT
		declare @sql			nvarchar(4000)
		declare @colcount		smallint
		declare @colname		varchar(100)
		declare @counter		smallint
		declare @change			smallint
		declare @IDvalue		INT
		DECLARE @ParmDefinition NVARCHAR(500)
		DECLARE @count			INT
--		DECLARE @IntVariable	INT
		--DECLARE @Lastlname varchar(30)
	 
		set @dateTime = GetDate()
		set @user = original_login()
		set @machine = host_name()
 
		SELECT @count = count(*) FROM Inserted;
 
		IF @count > 0
			BEGIN
				SELECT @colcount = count(*)
				FROM sys.all_objects o
				INNER JOIN sys.all_columns c ON o.object_id = c.object_id
				WHERE o.name = 'dbo.tbl_offer_1_price' and type_desc = 'USER_TABLE';
	 
				SELECT * INTO #Inserted FROM Inserted;
				SELECT * INTO #Deleted FROM Deleted;
 
				DECLARE cRow CURSOR FOR    
					SELECT IDofOffer
					FROM #Inserted;
 
				OPEN cRow;
				FETCH NEXT FROM cRow INTO @IDvalue;
				WHILE @@FETCH_STATUS = 0
					BEGIN            
						SET @counter = 2;
						WHILE @counter <= @colcount
							BEGIN
								SELECT @colname = c.name
								FROM sys.all_objects o
								INNER JOIN sys.all_columns c ON o.object_id = c.object_id
								WHERE o.name = 'dbo.tbl_offer_1_price'
								AND type_desc = 'USER_TABLE'
								AND c.column_id = @counter;
 
								SET @sql = N'SELECT @change = ISNULL(COUNT(*), 0) FROM #Inserted I INNER JOIN #Deleted D ON I.IDofOffer = D.IDofOffer WHERE I.IDofOffer = @IDvalue AND I.'+ @colname + N' <> D.'+ @colname
								--PRINT @sql;
								EXEC sp_executesql @sql, N'@change smallint OUTPUT, @IDvalue integer', @change OUTPUT, @IDvalue;
		--						SET @sql = N'SELECT @change = ISNULL(COUNT(*), 0) FROM Inserted INNER JOIN Deleted on Inserted.IDofOffer = Deleted.IDofOffer WHERE Inserted.IDofOffer = @IDvalue'
		--						SET @ParmDefinition = N'@IDvalue INT, @changeOUT varchar(200) OUTPUT'
		--								--SET @IntVariable = 35
		--							EXECUTE sp_executesql @sql, @ParmDefinition, @IDvalue = @IDvalue, @changeOUT=@change OUTPUT
		--							
								IF (@change > 0)
									BEGIN
										SET @column = @colname;
										SET @sql = N'SELECT @prevalue = ' +  @colname + ' FROM #Deleted WHERE IDofOffer = @IDvalue'
										EXEC sp_executesql @sql, N'@prevalue varchar(200) OUTPUT, @IDvalue integer', @prevalue OUTPUT, @IDvalue;
										--Set @prevalue = (SELECT @colname FROM Deleted WHERE IDofOffer = @IDvalue)
 
										--Select @prevalue
		--								SET @sql = N'SELECT @prevalueOUT = @colname FROM Deleted WHERE IDofOffer = @IDvalue'
		--								SET @ParmDefinition = N'@colname varchar(100), @IDvalue INT, @prevalueOUT varchar(200) OUTPUT'
		--								--SET @IntVariable = 35
		--								EXECUTE sp_executesql @sql, @ParmDefinition, @colname = @column, @IDvalue = @IDvalue, @prevalueOUT=@prevalue OUTPUT
		--								SELECT @prevalue
 
										SET @sql = N'SELECT @updateValue = ' +  @colname + ' FROM #Inserted WHERE IDofOffer = @IDvalue'
										EXEC sp_executesql @sql, N'@updateValue varchar(200) OUTPUT, @IDvalue integer', @updateValue OUTPUT, @IDvalue;
										--Set @updateValue = (SELECT @colname FROM Inserted WHERE IDofOffer = @IDvalue)
										--Select @updateValue
										--SET @IDofOffer = (SELECT IDofOffer FROM Deleted WHERE IDofOffer = @IDvalue)
		--								SET @sql = N'SELECT @updateValueOUT = @colname FROM Inserted WHERE IDofOffer = @IDvalue'
		--								SET @ParmDefinition = N'@colname varchar(100), @IDvalue INT, @updateValueOUT varchar(200) OUTPUT'
		--								--SET @IntVariable = 35
		--								EXECUTE sp_executesql @sql, @ParmDefinition, @colname = @column, @IDvalue = @IDvalue, @updateValueOUT=@updateValue OUTPUT
		--								SELECT @updateValue
 
										INSERT INTO dbo.tbl_offer_1_price_LogChanges (IDofOffer, LogTime, MachineName, LogUser, UpdatedCol, PreValue, UpdateValue) VALUES (@IDvalue, @dateTime, @machine, @user, @column, @prevalue, @updateValue);
COMMIT TRANSACTION
									END;
								SET @counter = @counter + 1;
							END
						FETCH NEXT FROM cRow INTO @IDvalue;
					END;
				CLOSE cRow;
				DEALLOCATE cRow;
				DROP TABLE #Inserted;
				DROP TABLE #Deleted;
			END
END

Open in new window

LVL 3
utlonghornjulieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
OK, think I can help, let me digest the trigger for a while, and have two other "tricky" things to finish, then will get onto it...

in the meantime, and if OK with you, could you please do :

select * from information_schema.columns where table_name in ('tbl_offer_1_price','tbl_offer_1_price_LogChanges') order by table_name, ordinal_position

My thoughts are to re-write elements and it would be good to get something a bit more than a mental picture of what you are working with, and will probably create the tables locally (in which case a create table script would be good for the above)...
0
utlonghornjulieAuthor Commented:
Here you go. I await your response.
0
utlonghornjulieAuthor Commented:
Here is the file.
tbl-offer-1-price.xls
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mark WillsTopic AdvisorCommented:
OK, got it - lots of columns !!
0
utlonghornjulieAuthor Commented:
Any luck?
0
Mark WillsTopic AdvisorCommented:
Hi, sorry about the delays. Your error is the temp table - need to make it a global temp table ie use two ## then the sub routines (like sp_executesql) will also have access.  For inserted, you can use the current 'live' table the columns have already been updated, so, it really is the "previous" values that present the major challenge. I have been looking at re-writing with a few new thoughts, but suggest you simply try adding two # to the temp table and have a quick test of that - see below...
ALTER TRIGGER [dbo].[LogOffer1PriceChanges] ON [dbo].[tbl_Offer_1_Price]
-- =============================================
-- Author:			Julie Liu
-- Create date:		11/13/2008
-- Description:     Log changes to tbl_Offer_1_price
-- =============================================
FOR UPDATE
AS
BEGIN
		SET NOCOUNT ON;
 
		-- Insert statements for trigger here
 
		declare @dateTime		dateTime
		declare @user			varchar(50)
		declare @machine		varchar(50)
		declare @column			varchar(200)
		declare @prevalue		varchar(200)
		declare @updateValue	varchar(200)
		declare @IDofOffer		INT
		declare @sql			nvarchar(4000)
		declare @colcount		smallint
		declare @colname		varchar(100)
		declare @counter		smallint
		declare @change			smallint
		declare @IDvalue		INT
		DECLARE @ParmDefinition NVARCHAR(500)
		DECLARE @count			INT
	 
		set @dateTime = GetDate()
		set @user = original_login()
		set @machine = host_name()
 
		SELECT @count = count(*) FROM Inserted;
 
		IF @count > 0
			BEGIN
				SELECT @colcount = count(*)
				FROM sys.all_objects o
				INNER JOIN sys.all_columns c ON o.object_id = c.object_id
				WHERE o.name = 'dbo.tbl_offer_1_price' and type_desc = 'USER_TABLE';
	 
				SELECT * INTO ##Inserted FROM Inserted;
				SELECT * INTO ##Deleted FROM Deleted;
 
				DECLARE cRow CURSOR FOR    
					SELECT IDofOffer
					FROM ##Inserted;
 
				OPEN cRow;
				FETCH NEXT FROM cRow INTO @IDvalue;
				WHILE @@FETCH_STATUS = 0
					BEGIN            
						SET @counter = 2;
						WHILE @counter <= @colcount
							BEGIN
								SELECT @colname = c.name
								FROM sys.all_objects o
								INNER JOIN sys.all_columns c ON o.object_id = c.object_id
								WHERE o.name = 'dbo.tbl_offer_1_price'
								AND type_desc = 'USER_TABLE'
								AND c.column_id = @counter;
 
								SET @sql = N'SELECT @change = ISNULL(COUNT(*), 0) FROM ##Inserted I INNER JOIN ##Deleted D ON I.IDofOffer = D.IDofOffer WHERE I.IDofOffer = @IDvalue AND I.'+ @colname + N' <> D.'+ @colname
								EXEC sp_executesql @sql, N'@change smallint OUTPUT, @IDvalue integer', @change OUTPUT, @IDvalue;
 
								IF (@change > 0)
									BEGIN
										SET @column = @colname;
 
										SET @sql = N'SELECT @prevalue = ' +  @colname + ' FROM ##Deleted WHERE IDofOffer = @IDvalue'
										EXEC sp_executesql @sql, N'@prevalue varchar(200) OUTPUT, @IDvalue integer', @prevalue OUTPUT, @IDvalue;
 
										SET @sql = N'SELECT @updateValue = ' +  @colname + ' FROM ##Inserted WHERE IDofOffer = @IDvalue'
										EXEC sp_executesql @sql, N'@updateValue varchar(200) OUTPUT, @IDvalue integer', @updateValue OUTPUT, @IDvalue;
 
										INSERT INTO dbo.tbl_offer_1_price_LogChanges (IDofOffer, LogTime, MachineName, LogUser, UpdatedCol, PreValue, UpdateValue) VALUES (@IDvalue, @dateTime, @machine, @user, @column, @prevalue, @updateValue);
 
									END;
								SET @counter = @counter + 1;
							END
						FETCH NEXT FROM cRow INTO @IDvalue;
					END;
				CLOSE cRow;
				DEALLOCATE cRow;
				DROP TABLE ##Inserted;
				DROP TABLE ##Deleted;
			END
END

Open in new window

0
utlonghornjulieAuthor Commented:
Ok here is the trigger code. I try and run an update statement that will affect 43 rows, and I get the following error.

UPDATE tbl_offer_1_price
SET prospectid = 15719
WHERE prospectid = 99999

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'On'.
USE [Pricing_test3]
GO
/****** Object:  Trigger [dbo].[LogOffer1PriceChanges]    Script Date: 11/26/2008 12:14:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:			Julie Liu
-- Create date:		11/13/2008
-- Description:     Log changes to tbl_Offer_1_price
-- =============================================
ALTER TRIGGER [dbo].[LogOffer1PriceChanges] ON [dbo].[tbl_Offer_1_Price]
-- =============================================
-- Author:                      Julie Liu
-- Create date:         11/13/2008
-- Description:     Log changes to tbl_Offer_1_price
-- =============================================
FOR UPDATE
AS
BEGIN
                SET NOCOUNT ON;
 
                -- Insert statements for trigger here
 
                declare @dateTime               dateTime
                declare @user                   varchar(50)
                declare @machine                varchar(50)
                declare @column                 varchar(200)
                declare @prevalue               varchar(200)
                declare @updateValue			varchar(200)
                declare @IDofOffer              INT
                declare @sql                    nvarchar(4000)
                declare @colcount               smallint
                declare @colname                varchar(100)
                declare @counter                smallint
                declare @change                 smallint
                declare @IDvalue                INT
                DECLARE @ParmDefinition			NVARCHAR(500)
                DECLARE @count                  INT
         
                set @dateTime = GetDate()
                set @user = original_login()
                set @machine = host_name()
 
                SELECT @count = count(*) FROM Inserted;
 
                IF @count > 0
                        BEGIN
                                SELECT @colcount = count(*)
								FROM sys.all_objects o
								INNER JOIN sys.all_columns c on o.object_id = c.object_id
								WHERE o.name = 'tbl_offer_1_price' and type_desc = 'USER_TABLE';
         
                                SELECT * INTO ##Inserted FROM Inserted;
                                SELECT * INTO ##Deleted FROM Deleted;
 
                                DECLARE cRow CURSOR FOR    
                                        SELECT IDofOffer
                                        FROM ##Inserted;
 
                                OPEN cRow;
                                FETCH NEXT FROM cRow INTO @IDvalue;
                                WHILE @@FETCH_STATUS = 0
                                        BEGIN            
                                                SET @counter = 2;
                                                WHILE @counter <= @colcount
                                                        BEGIN
                                                                SELECT @colname = c.name
                                                                FROM sys.all_objects o
                                                                INNER JOIN sys.all_columns c ON o.object_id = c.object_id
                                                                WHERE o.name = 'tbl_offer_1_price'
                                                                AND type_desc = 'USER_TABLE'
                                                                AND c.column_id = @counter;
 
                                                                SET @sql = N'SELECT @change = ISNULL(COUNT(*), 0) FROM ##Inserted I INNER JOIN ##Deleted D ON I.IDofOffer = D.IDofOffer WHERE I.IDofOffer = @IDvalue AND I.'+ @colname + N' <> D.'+ @colname
                                                                EXEC sp_executesql @sql, N'@change smallint OUTPUT, @IDvalue integer', @change OUTPUT, @IDvalue;
 
                                                                IF (@change > 0)
                                                                        BEGIN
                                                                                SET @column = @colname;
 
                                                                                SET @sql = N'SELECT @prevalue = ' +  @colname + ' FROM ##Deleted WHERE IDofOffer = @IDvalue'
                                                                                EXEC sp_executesql @sql, N'@prevalue varchar(200) OUTPUT, @IDvalue integer', @prevalue OUTPUT, @IDvalue;
 
                                                                                SET @sql = N'SELECT @updateValue = ' +  @colname + ' FROM ##Inserted WHERE IDofOffer = @IDvalue'
                                                                                EXEC sp_executesql @sql, N'@updateValue varchar(200) OUTPUT, @IDvalue integer', @updateValue OUTPUT, @IDvalue;
 
                                                                                INSERT INTO tbl_offer_1_price_LogChanges (IDofOffer, LogTime, MachineName, LogUser, UpdatedCol, PreValue, UpdateValue) VALUES (@IDvalue, @dateTime, @machine, @user, @colname, @prevalue, @updateValue);
 
                                                                        END;
                                                                SET @counter = @counter + 1;
                                                        END
                                                FETCH NEXT FROM cRow INTO @IDvalue;
                                        END;
                                CLOSE cRow;
                                DEALLOCATE cRow;
                                DROP TABLE ##Inserted;
                                DROP TABLE ##Deleted;
                        END
END

Open in new window

0
Mark WillsTopic AdvisorCommented:
OK, found it - two columns in your price table - "on-peak" and "off-peak"  both have hyphens so the dynamic SQL wasn't creating properly.

Have also rationalised a little more - it is pretty slow given the high number of columns - you might want to address that somehow (maybe a level of importance or similar, maybe even create an audit control where you sepecify what columns and use that to loop around that counter).

Ended up having to create the tables, et al on the local database to sort out what was happening. So, to fix, put column names in [] (ie square brackets) and also removed the "dbo." from the table name lookup in sysobjects - which I did change to information_schemas...

alter TRIGGER [LogOffer1PriceChanges] ON [tbl_Offer_1_Price]
-- =============================================
-- Author:            Julie Liu
-- Create date:        11/13/2008
-- Description:     Log changes to tbl_Offer_1_price
-- =============================================
FOR UPDATE
AS
BEGIN
 
        -- Insert statements for trigger here
 
        declare @dateTime       dateTime
        declare @user           varchar(50)
        declare @machine        varchar(50)
        declare @column         varchar(200)
        declare @prevalue       varchar(200)
        declare @updateValue    varchar(200)
        declare @IDofOffer      INT
        declare @sql            nvarchar(4000)
        declare @colcount       smallint
        declare @colname        varchar(100)
        declare @counter        smallint
        declare @change         smallint
        declare @IDvalue        INT
        DECLARE @ParmDefinition NVARCHAR(500)
        DECLARE @count          INT
     
        set @dateTime = GetDate()
        set @user = original_login()
        set @machine = host_name()
 
        SELECT @count = count(*) FROM Inserted;
 
        IF @count > 0
            BEGIN
 
                SELECT @colcount = max(ordinal_position) 
                FROM information_schema.columns 
                WHERE table_name = 'tbl_offer_1_price'
     
                SELECT * INTO ##Inserted FROM Inserted;
                SELECT * INTO ##Deleted FROM Deleted;
 
                DECLARE cRow CURSOR FAST_FORWARD FOR    
                    SELECT IDofOffer
                    FROM ##Inserted;
 
                OPEN cRow;
                FETCH NEXT FROM cRow INTO @IDvalue;
                WHILE @@FETCH_STATUS = 0
                BEGIN            
                        SET @counter = 2;
                        WHILE @counter <= @colcount
                        BEGIN
 
                                SELECT @colname = column_name
                                FROM information_schema.columns 
                                WHERE table_name = 'tbl_offer_1_price'
                                AND ordinal_position = @counter
 
                                SET @change = 0
                                SET @sql = N'SELECT @change = 1, @prevalue = d.[' +  @colname + '],@updateValue = i.[' +  @colname + ']  FROM ##Inserted I INNER JOIN ##Deleted D ON I.IDofOffer = D.IDofOffer WHERE I.IDofOffer = @IDvalue and I.['+ @colname+'] <> D.['+ @colname+']'
                                EXEC sp_executesql @sql, N'@change smallint OUTPUT, @prevalue varchar(200) output, @updateValue varchar(200) output, @IDvalue integer', @change OUTPUT, @prevalue OUTPUT, @updatevalue output, @IDvalue;
 
                                IF @change = 1 INSERT INTO dbo.tbl_offer_1_price_LogChanges (IDofOffer, LogTime, MachineName, LogUser, UpdatedCol, PreValue, UpdateValue) VALUES (@IDvalue, @dateTime, @machine, @user, @colname, @prevalue, @updateValue);
                                SET @counter = @counter + 1
 
                        END
                        FETCH NEXT FROM cRow INTO @IDvalue;
                END;
                CLOSE cRow;
                DEALLOCATE cRow;
 
                DROP TABLE ##Inserted;
                DROP TABLE ##Deleted;
 
            END
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.