Trigger Fires for each insert update and delete action

[Fsoumia]

I am doing some auditing to find out which tables are actually being used/referenced in SQL Server 2000 Database. I have an audit table with the following fields:
ID PK
OBjectName. The name of the table
OBjectType. The Object Type. Table
AuditDate. GetDate()
Initiator. SYS_USER

The Trigger I set on each table

CREATE TRIGGER MyTriger
on MyTableName
FOR INSERT UPDATE DELETE

INSERT MYAUDITTABLE(OBjectName, ObjectType,AuditDate,Initiator )
VALUES('MyTableName', 'Table',GetDate(), SYS_USER)
The problem is that the trigger fires for each batch insert, update and delete. My Audit Table is getting extremely large. If there is a batch insert of 100 000 records, the trigger fires 100 000. times. Is there a way, to force the trigger to fire once only regardless of the number of records that are being processed by the query?
I will appreciate any input on this
Thank you

 

[gmmastros]

How are you inserting those 100,000 records? Are you doing it in a loop, so there are actually 100,000 insert statements getting executed?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Fsoumia]

Hi George

I actually have no control over those stored procedures that run through scheduled jobs, and some them are run manually on a weekly basis. They do a batch inserts and updates. The assumption is that the trigger fires every time a record gets inserted into the tables. So if we have 100000 records insert operation it implies the trigger will fire 100000 times. ?
Not sure how I can rewrite my trigger so that it fires only once regardless of how many records are affected. Is this something that can accomplished? I read about some type of triggers in PL/SQL database (Statement Trigger) which does the same thing. Can it be replicated in SQL Server 2000?

 

[markros]

Trigger fires on every INSERT command. One INSERT can insert one record and can insert 1000+. In both cases trigger will fire only once.

It looks like in the code there is a loop that inserts one record at a time. If so, the trigger also fires every time.



PluralSight Learning Library

 

[Fsoumia]

So if I understand correctly, there is no way around it?

 

[markros]

Only if you change your current approach to not do one record insert at a time.

PluralSight Learning Library

 

[Fsoumia]

ok. But I am not sure I understand. How can I rewrite my trigger so that it does not do one record insert at a time? Below is my trigger that fires for each insert, update, and delete

CREATE TRIGGER MyTriger
on MyTableName
FOR INSERT UPDATE DELETE

INSERT MYAUDITTABLE(OBjectName, ObjectType,AuditDate,Initiator )
VALUES('MyTableName', 'Table',GetDate(), SYS_USER)

 

[markros]

The trigger is OK, you need to ensure that you don't insert one record at a time when you run your batch process.

PluralSight Learning Library

 

[Fsoumia]

I see. Unfortunately I have no control over those batch processes.
I was hoping to find a way to handle it at the trigger level. It's part of an audit to find out which db objects are actually being used prior to migration to SQL Server 2005. Running a trace in production is not an option.
Thank you again for your input

 

[gmmastros]

If you only want to know that a table is being used, why don't you do an exists check on a table and only insert if the row isn't there?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom