DTS not triggering TRIGGER

[KCWMS]

using SQL SERVER 2K,
dev machine: WINXP PRO
prod machine: WIN SERVER 2003

I have a DTS package which transforms a text file into a table :
1.CONNECTION OBJECT TO TEXT FILE
2.TRANSFORM DATA TASK OBJECT MAPPING FIELDS
3.CONNECTION OBJECT TO DB

This works.

The trigger on that table, though is not running. I added INSERTS within the trigger to write progress to another table and this only happens when I insert records via an INSERT statement within Query Analyzer.

Is this by design? Do I need to modify the DTS package? I can right SQL/TSQL to make yo' mamma scream but I am a bit fuzzy on SQL SERVER ADMIN/RIGHTS issues.

Thanks in advance

 

[Catadmin]

AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.

Reading this, I would say doublecheck the imported data to see if it violates any table constraints. Also, are you using a FOR/AFTER Insert or an INSTEAD OF Insert trigger? I'm assuming the former, but wanted to double-check.

Does your DTS job drop & recreate the trigger after each insert?

Also, make sure your DTS job isn't deleting and recreating the table. This might also get around the trigger issue if the data is inserted before the trigger is created. (Not sure of the order if DTS does the drop/create thing).

What Recovery Mode is the DB set to? If Bulk Insert, then it's entirely possible that when the DB gets an insert from DTS, it's not only skipping the "record of data to Transaction Log" step, but the trigger step too.

Some of my suggestions may be way off base, but it never hurts to verify the above information and test it as a "just in case". You never know what MS has going on behind the scenes.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[KCWMS]

Catadmin,

Thanks for the quick response and there are no trivial suggestions as you pointed me in the right direction.

This is an AFTER TRIGGER.
The DTS does not drop and recreate the table but...

The TRANSFOR DATA TASK had the FAST LOAD and CHECK CONSTRAINTS option set. I unchecked these and it looks like the trigger actually ran.

Thanks a lot!!

I have a client for whom I created an intranet site. They had last minute staffing issues (read someone got fired) and asked me to step in and troubleshoot code/processes I was never involved with. This was a major issue.

 

[Catadmin]

I'm glad I was able to help. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???