To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q120899-3.shtml

The SQL Guru Answers your Questions...


I heard a rumor that I'm supposed to send you weird sql questions in order to make your brain explode so here goes.

After poking around with foreign key and primary key constraints, a trigger I had wouldn't work properly. Here's the basic structure... In sql server 6.5 and ASP:

I had a main table, let's call it computers, which contained a unique id (serial number) as primary key.

I had a related table, let's call it oldNames, which contained a listing of old "servernames" for servers, the structure being ID (serial number in computers) and oldName.

I made a trigger so that any time the Name field of computers was changed, it would insert a row into oldNames with the old name.

This is all fine and dandy, but here's the real stinker. In building the command to update a record in computers on my ASP page, I had inadvertently included serialnumber as a field to update (I was iterating through the fieldnames so I didn't have to type in update computers set field1=Request(field1)) for each of the 50 or so fields. So the query was updating the serialNumber to it's current value, kind of useless but oh well.

Ok so what? well here's the weird thing: the sql command would run without an error, but the trigger I had created would not be called even though I had changed the name field! I took out the serialnumber=x portion of the query/command and suddenly the trigger is fired.

I could plug in some code here, but to make a long story short I ended up just excluding serial number, and everything is fine. My question is, why the heck wasn't the trigger firing when I included the serial number? It had referential integrity enforced by way of a foreign key, so if there was a problem there, you would think it would spit an error. Very odd!

This is certainly a weird one. Any time you encounter something like this, it is usually best to make sure you're using the latest service pack.

There are a fair number of problems using triggers, especially using triggers with DRI constraints. Without looking at your code, it's hard to tell what the problem might be. However, you may want to check out knowledge base article Q138763. If you use the UPDATE() function in your trigger, it may not fire.

Also, remember that constraints are handled before triggers, so a trigger won't fire if a referential integrity constraint (or any other constraint, for that matter) is violated.

Hope this helps!

Sean


Article Information
Article Title: SQL Guru: Help! My Triggers Aren't Firing!
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q120899-3.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers