I have a trigger that saves any changes made to one table in our database, to another table, just for historical backup purposes. The trigger works just fine, but the only problem is if I run a bulk update statement in Query Analyzer. I get a "Subquery return more than one result" Can I put something in the trigger to say if the query is a bulk update statement, do not run the trigger? I cannot disable the trigger. This table is our busiest table in our database, and we do not have the luxury to turn the trigger off. Here is the code. It's generic, to work on any table. I am including dummy table structures, so you may see how everything works.
---------------------------------------------------------------------
CREATE TABLE [dbo].[History] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NULL ,
[FieldChanged] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OldValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
---------------------------------------------------------------------
CREATE TABLE [dbo].[Transaction] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[A] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[int] NULL ,
[C] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[D] [float] NULL ,
[E] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[F] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[G] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[J] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[K] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
---------------------------------------------------------------------
CREATE TRIGGER [On_Update]
ON [dbo].[Transaction]
FOR UPDATE
AS
-- Variable to store the Column Names and Values
Declare @ColName varchar(100), @ColPos int
Declare @OldValue as varchar(100), @NewValue as varchar(100)
-- Cursor to read Column Names
Declare Column_Names Cursor
For
SELECT COLUMN_NAME, Ordinal_Position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Transaction'
Open Column_Names
FETCH NEXT FROM Column_Names INTO @ColName, @ColPos
-- Loop to read all Column Names
WHILE @@FETCH_STATUS = 0
BEGIN
-- Log the change
IF substring(Columns_Updated(),ceiling((@ColPos-1)/8)+1,1) & POWER(2,((@ColPos-floor((@ColPos-1)/8)*8)-1)) > 0
Begin
SET @OldValue = (SELECT CASE
When @ColName = 'A' Then A
When @ColName = 'B' Then B
When @ColName = 'C' Then C
When @ColName = 'D' Then D
When @ColName = 'E' Then E
When @ColName = 'F' Then F
When @ColName = 'G' Then G
When @ColName = 'H' Then H
When @ColName = 'I' Then I
When @ColName = 'J' Then J
When @ColName = 'K' Then K
End
From Deleted )
SET @NewValue = (SELECT CASE
When @ColName = 'A' Then A
When @ColName = 'B' Then B
When @ColName = 'C' Then C
When @ColName = 'D' Then D
When @ColName = 'E' Then E
When @ColName = 'F' Then F
When @ColName = 'G' Then G
When @ColName = 'H' Then H
When @ColName = 'I' Then I
When @ColName = 'J' Then J
When @ColName = 'K' Then K
End
From Inserted )
INSERT INTO History ([Date],FieldChanged, OldValue, NewValue) VALUES (GetDate(),@ColName, @OldValue, @NewValue)
End
-- Get the next column name
FETCH NEXT FROM Column_Names INTO @ColName,@ColPos
END
-- Close Columns
CLOSE Column_Names
DEALLOCATE Column_Names
---------------------------------------------------------------------
---------------------------------------------------------------------
CREATE TABLE [dbo].[History] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NULL ,
[FieldChanged] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OldValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
---------------------------------------------------------------------
CREATE TABLE [dbo].[Transaction] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[A] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[int] NULL ,
[C] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[D] [float] NULL ,
[E] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[F] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[G] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[J] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[K] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
---------------------------------------------------------------------
CREATE TRIGGER [On_Update]
ON [dbo].[Transaction]
FOR UPDATE
AS
-- Variable to store the Column Names and Values
Declare @ColName varchar(100), @ColPos int
Declare @OldValue as varchar(100), @NewValue as varchar(100)
-- Cursor to read Column Names
Declare Column_Names Cursor
For
SELECT COLUMN_NAME, Ordinal_Position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Transaction'
Open Column_Names
FETCH NEXT FROM Column_Names INTO @ColName, @ColPos
-- Loop to read all Column Names
WHILE @@FETCH_STATUS = 0
BEGIN
-- Log the change
IF substring(Columns_Updated(),ceiling((@ColPos-1)/8)+1,1) & POWER(2,((@ColPos-floor((@ColPos-1)/8)*8)-1)) > 0
Begin
SET @OldValue = (SELECT CASE
When @ColName = 'A' Then A
When @ColName = 'B' Then B
When @ColName = 'C' Then C
When @ColName = 'D' Then D
When @ColName = 'E' Then E
When @ColName = 'F' Then F
When @ColName = 'G' Then G
When @ColName = 'H' Then H
When @ColName = 'I' Then I
When @ColName = 'J' Then J
When @ColName = 'K' Then K
End
From Deleted )
SET @NewValue = (SELECT CASE
When @ColName = 'A' Then A
When @ColName = 'B' Then B
When @ColName = 'C' Then C
When @ColName = 'D' Then D
When @ColName = 'E' Then E
When @ColName = 'F' Then F
When @ColName = 'G' Then G
When @ColName = 'H' Then H
When @ColName = 'I' Then I
When @ColName = 'J' Then J
When @ColName = 'K' Then K
End
From Inserted )
INSERT INTO History ([Date],FieldChanged, OldValue, NewValue) VALUES (GetDate(),@ColName, @OldValue, @NewValue)
End
-- Get the next column name
FETCH NEXT FROM Column_Names INTO @ColName,@ColPos
END
-- Close Columns
CLOSE Column_Names
DEALLOCATE Column_Names
---------------------------------------------------------------------