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
I made a trigger so that any time the
Namefield of computers was changed, it would insert a row into
oldNameswith 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
serialNumberto 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=xportion 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!