Trigger Problems

[Steve Bishop]

I have an Insert, Update and delete trigger on a table that works fine.
It records the changes into another table called tblTracking.
tblTracking records the changes so they can be uploaded to another
server off site. The problem is that it fires each time my DTS package
fires (every 15 minutes). The DTS package imports data from an ODBC
database then compares what is in the source/destination table with a
Insert Into, select not in (for new records) and Update statements for
changed records.

My tblTracking gets pretty big by mid day and doesn't tell the clear
picture of what has really changed or been added with all the update and
inserts flying around. I can't define this original ODBC data source as
a linked server. Any suggestions on how I can make this more efficient.
Thanks
Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"Steve Bishop" <st****@viper.com> wrote in message
news:40***********************@news.frii.net...

I have an Insert, Update and delete trigger on a table that works fine.
It records the changes into another table called tblTracking.
tblTracking records the changes so they can be uploaded to another
server off site. The problem is that it fires each time my DTS package
fires (every 15 minutes). The DTS package imports data from an ODBC
database then compares what is in the source/destination table with a
Insert Into, select not in (for new records) and Update statements for
changed records.

My tblTracking gets pretty big by mid day and doesn't tell the clear
picture of what has really changed or been added with all the update and
inserts flying around. I can't define this original ODBC data source as
a linked server. Any suggestions on how I can make this more efficient.
Thanks
Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It's not clear from your post what the DTS package is doing, and what your
issue with the trigger is. Are you asking how to prevent a DTS package
firing a trigger? If so, then checking 'Use Fast Load' in a Transform Data
Task means that triggers won't fire. Or ALTER TABLE can disable the trigger
temporarily, but that may not be acceptable as it wouldn't fire at all while
the package is running.

If this doesn't help, perhaps you could clarify what the DTS package is
doing, as well as where in the sequence of events the trigger is fired.

Simon

[John Bell]

Hi

Sounds like your trigger is working well!

If you disable it then you would have to stop all other users changing the
data in the table to make sure that your audit data was compete (apart from
DTS information!). If your DTS package is being run on the server it may be
possible to use HOST_NAME() or USER_NAME() as a filter. If you make your
trigger insert audit data conditionally on these it will leave a rather
large whole in your auditing, but if you included it in the audit table then
you could filter your queries and still have all the data available.

John

"Steve Bishop" <st****@viper.com> wrote in message
news:40***********************@news.frii.net...

I have an Insert, Update and delete trigger on a table that works fine.
It records the changes into another table called tblTracking.
tblTracking records the changes so they can be uploaded to another
server off site. The problem is that it fires each time my DTS package
fires (every 15 minutes). The DTS package imports data from an ODBC
database then compares what is in the source/destination table with a
Insert Into, select not in (for new records) and Update statements for
changed records.

My tblTracking gets pretty big by mid day and doesn't tell the clear
picture of what has really changed or been added with all the update and
inserts flying around. I can't define this original ODBC data source as
a linked server. Any suggestions on how I can make this more efficient.
Thanks
Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Bishop]

DTS package steps:
1. Empty imported ODBC tables.
2. Re-import ODBC tables.
3. Insert Into (Select ...Not In) fires between a view of the imported
odbc tables and the destination table.
4. Update query fires between view of the imported odbc tables and the
destination table.
5. Some other queries fire that I have selectivity over so they don't
hit every row.

I think my main problem is coming from the update query. The update
trigger on the destination table makes an entry for every row in the
table. It would be cool if it just looked at the records that have
changed. Maybe it's because my trigger has syntax like:
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
My insert statement....
Help appreciated. Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Steve Bishop (st****@viper.com) writes:

DTS package steps:
1. Empty imported ODBC tables.
2. Re-import ODBC tables.
3. Insert Into (Select ...Not In) fires between a view of the imported
odbc tables and the destination table.
4. Update query fires between view of the imported odbc tables and the
destination table.
5. Some other queries fire that I have selectivity over so they don't
hit every row.

I think my main problem is coming from the update query. The update
trigger on the destination table makes an entry for every row in the
table. It would be cool if it just looked at the records that have
changed. Maybe it's because my trigger has syntax like:
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
My insert statement....
Help appreciated. Thanks.

The trigger does not know which rows that have changed. To that end
you can compare the inserted table to the deleted table:

IF EXISTS (SELECT *
FROM inserted i
JOIN deleted d ON i.keycol = d.keycol
WHERE i.col1 <> d.col1 OR
i.col2 <> d.col2 ...

But involving both inserted and deleted in a query may be expensive.
It may be cheaper to declare two table variables and inserted into
these the data from inserted/deleted.

If you simply want the trigger to not bother about DTS at all, you
can create a temp table from DTS, call it #DTS$loading. Then in the
trigger, you do this:

IF object_id('tempdb..#DTS$loading') IS NULL
BEGIN
-- Do auditing.

Note here that how the table looks like is irrelevant, the mere existence
of a table acts as a flag. The advantage with this over ALTER TABLE
DISABLE TRIGGER is that you don't miss auditing of other users playing
around while the load is running.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp