Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Triggers

Author

Topic

gmetaj
Starting Member

33 Posts

Posted - 2004-08-09 : 17:23:22

I am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement inside the if followed by an insert to the audit table. When I leave only a few IF-s in the trigger and comment the rest of the code performance increased dramatically. It seems like it is checking every single UPdate() statement. Assuming that this was slowing down due to doing a select for every update i tried to do to seperate selects in the beginning from Deleted and Inserted and assigning columns name to specific variables and instead of doing
if Update(fieldName) i did
if @DelFieldName <> @InsFieldName
begin
INSERT INTO AUDIT
SELECT WHAT I NEED
END

This did not improve performance. If you have any ideas on how to get around this issue please let me know.

Below is an example of what my triggers look like.

------------------------------------
Trigger 1 -- this was my original design
CREATE trigger1 on Table
FOR UPDATE
AS

if update(field1)
begin
insert into Audit
SELECT What I need
END

if update(field2)
begin
insert into Audit
SELECT What I need
END

.
.
. Repeated about 65 more times

if update(field67)
insert into Audit
SELECT What I need
END
---------------------------------------

------------------------------------
Trigger 2 -- this is what i tried but did not improve performance
CREATE trigger2 on Table
FOR UPDATE
AS

Declare @DelField1 varchar
Declare @DelField2 varchar
.
.
Declare @DelField67 varchar

Select
@DelField1 = DelField1,
@DelField2 = DelField2,
...
@DelField3 = DelField3
From Deleted

Declare @InsField1 varchar
Declare @InsField2 varchar
.
.
Declare @InsField67 varchar

Select
@insField1 = InsField1,
@insField2 = InsField2,
...
@InsField3 = InsField3
From Inserted

-- I do not do if Update() but instead compare variables

if @DelField1 <> InsField1
begin
Insert into AUDIT
SELECT what I need
end

if @DelField2 <> InsField2
begin
Insert into AUDIT
SELECT what I need
end
...
...
...

if @DelField67 <> InsField67
begin
Insert into AUDIT
SELECT what I need
end

----------------------------------------------

IF you have any idea how to optimize this please let me know. Any input is greatly appreciated.

Thanks,

Gent

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 17:28:09

Duplicate:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38410

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources