Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

for update trigger is not logging changes

Posted on 2008-11-17
8
Medium Priority
?
295 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:utlonghornjulie
  • 4
  • 4
8 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22982374
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
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 22985298
Here you go. I await your response.
0
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 22985308
Here is the file.
tbl-offer-1-price.xls
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Mark Wills
ID: 23000186
OK, got it - lots of columns !!
0
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 23037899
Any luck?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23043421
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
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 23044342
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 23055357
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question