How to disable a trigger only when doing bulk updates... 2

[tokuzumi]

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
---------------------------------------------------------------------

 

[JohnDTampaBay]

Test the count of rows in the Inserted and Deleted tables. If both are 1, then execute the trigger. Something like this:

CREATE TRIGGER [On_Update]
ON [dbo].[Transaction]
FOR UPDATE
AS
IF (SELECT Count(*) FROM Inserted) = 1
AND (SELECT Count(*) FROM Deleted) = 1
BEGIN
-- Variable to store the Column Names and Values
Declare @ColName varchar(100), @ColPos int

<edited for space>

CLOSE Column_Names
DEALLOCATE Column_Names
END


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rdroske]

Interesting problem, I found the following at

http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm

Sometimes you may want to find out what exact statement updated your table.

DBCC INPUTBUFFER can provide you with this kind of information. You can create a trigger on your table, that uses DBCC INPUTBUFFER command to find out the exact command that caused the trigger to fire.

The following trigger code works in SQL Sever 2000 (In SQL Server 7.0, you can't create tables inside a trigger. So, you'll have to create a permanent table before hand and use that inside the trigger).

CREATE TRIGGER TriggerName
ON TableName
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON

DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)

SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

INSERT INTO #inputbuffer
EXEC (@ExecStr)

SET @Qry = (SELECT EventInfo FROM #inputbuffer)

SELECT @Qry AS 'Query that fired the trigger',

 

[tokuzumi]

I would like to thank both of you for your help. Rdroske, that looks like a pretty cool thing, knowing what statement caused the trigger to fire. That may be useful to me down the road.