What processes can update records without firing a trigger

Question

What processes can update records without firing a trigger

kmclaren

Valued Member

Points: 67
More actions
March 25, 2014 at 5:37 am

#305361

I am working on a problem where we have a table that periodically gets updated with incorrect data. The table is a lookup table that maps Units of Measure in an EDI components of Microsoft Dynamics NAV. The updates insert data that makes no sense in the current environment and often uses units of measure that make no sense.
I have implemented a trigger for INSERT and UPDATE in order to identify the username and time that these changes are being made. The trigger seems to be working and I catch a lot of data as the admins fix the problem, but I don't think I am catching the problem. For instance, I had lots of data this morning updated to be ASSY (which is an Assembly in manufacturing) and yet I had no rows logged that had ASSY anywhere.
I have place the trigger code below just in case I might have an error. However, I think that my bigger question is how data can get updated in a SQL Server 2008 R2 DB without firing a trigger? Bulk Updates? Some sort of Programatic connection? Basically I am looking for places to look since we have code from numerous vendors and I am starting to suspect the data is coming from wonky code that may be inserting the data in some programatic way that is not firing my trigger.
Thanks in advance for your assistance!
CREATE TRIGGER tr_EDIUOM_update
ON [Omega Environmental Tech_$E_D_I_ Trade Partner UOM]
FOR INSERT, UPDATE
AS
INSERT INTO [zzzEDI_Trade_Partner_UOM_Audit]
([Trade Partner No_]
,[EDI Unit of Measure]
,[Navision Item No_]
,[Navision Unit of Measure]
,[Order Unit of Measure]
,[Unassigned]
,[Date Changed]
,[User ID]
,[chgSUser]
,[chgCurUser]
,[chgDateTime]
)
SELECT [Trade Partner No_]
,[EDI Unit of Measure]
,[Navision Item No_]
,[Navision Unit of Measure]
,[Order Unit of Measure]
,[Unassigned]
,[Date Changed]
,[User ID]
,SUSER_NAME()
,CURRENT_USER
,CURRENT_TIMESTAMP
FROM inserted;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 1

I'd check the trigger - could there be an error in the SQL in the UPDATE trigger itself? Is it an AFTER trigger or an INSTEAD OF? Does some stored procedure or job disable the trigger, then enable it when it is done?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Bulk inserts (bcp) don't by default fire triggers, but afaik all update operations fire update triggers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

I agree with Gail. Bulk operations don't fire triggers by default. There is a "Fire Triggers" option that you can use for such things. Other than TRUNCATE TABLE (which isn't an update but still worth mentioning), all other inserts and updates would be caught by your trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)