|
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!
| 
