It's time for the third T-SQL Tuesday, managed this time by Rob Farley. I thought I wasn't going to make this deadline, since I have been swamped since the end of last week, and over the entire weekend. However, I've got a break with nothing urgent for the next hour or so, so I thought I could just write something short.
My first two T-SQL Tuesday posts dealt with SQL Server history, so I will continue along those lines, and this post will just be a very short history lesson.
In honor of the impending Valentine's Day holiday, Rob asked us to write something about relationships. I will admit, that my first thought when I read the topic was "Hmmm…. I wonder what Kim and Paul are going to write about…" as theirs is one of the most famous SQL Server relationships.
But I kept thinking, and realized that even though I didn't marry someone who is into SQL Server big time, I have enjoyed some wonderful personal relationships thanks to my involvement with SQL Server, with Roy Harvey, Tony Rogerson (and his wife Alex) and Tibor Karaszi (and his wife Catrin) at the top of that list. I have friendships that will endure even if I stop all SQL Server work at some point down the road, and Cindy Gross knows that I am mainly talking about her when I say that. And in addition to relationships that started because of SQL Server, it works the other way around… I have a relation that I introduced to SQL Server! I got my nephew Brian an interview with a consulting company that a friend of mine ran, down in Southern California, and he then spent over a decade with SQL Server, some of it spent being a DBA and some of it spent doing SQL Server consulting.
So now for the history lesson, and enough with the sappy stuff already… in SQL Server when we talk about relationships, we're usually referring to primary and foreign keys. Good relationships between PKs and FKs is referred to as referential integrity (RI). SQL Server 6 introduced "Declarative Referential Integrity", that is, the ability to declare, as part of your table definitions, your primary keys, and the foreign keys that reference them. The original version of declarative RI (DRI) was only preventative. If anyone tries to modify data in a table in a way that would violate the referential integrity, SQL Server disallows that modification and returns an error message. Possible violations include deleting a row from the referenced (PK) table that has matching rows in the referencing (FK) table, or inserting a row into the referencing table with a FK value that doesn't match a PK value.
SQL Server 2000 extended DRI to allow you to specify CASCADE with your FK definition, either on UPDATE or DELETE, or both. This means that if someone deletes a row from PK table, all the related rows in the FK table would be deleted, or if someone updates a value of a PK, the corresponding FK values would automatically be updated.
I actually wanted to go back in time a bit further. Prior to version 6 and the introduction of DRI, all RI validation had to be handwritten, usually with triggers. So if you deleted a row in the PK table, a DELETE TRIGGER would fire and depending on your design, either 1) delete all the rows in the FK table, or 2) check for matching rows and generate an error if any were found. If you wanted to go the route of deleting matching rows, the FK table might have its own DELETE TRIGGER, that would delete rows in any tables that referenced that one. It seems like a lot of work now, but it was all we had back in SQL Server 4 and 4.2.
But even further back, in the original released product, which was Sybase 3.0 and Microsoft SQL Server 1.0 and 1.1, actions within a trigger would _not_ cause other triggers to fire. So imagine Table1 has a PK on Column1, which is referenced by a FK, Column2 in Table2. Table2's PK might be Column3, which might be referenced by Column4 in another table, Table3. Table3 might have its own PK referenced by Table4. Rather than just writing a simple trigger for each table, that only needed to examine and act upon tables that directly referenced the table that caused the trigger to fire, each trigger would have to include all the code for all the referencing tables … because triggers could not be nested. Once you were running code from a trigger, no additional triggers could fire. The trigger for Table1 would have to deal with Table2, Table3 and Table4. The trigger for Table2 would have to deal with Table3 and Table4, and the trigger for Table3 would deal with Table4. And what if you added a new Table5 with a FK that referenced Table4? All the triggers would have to be rewritten; we couldn't just simply add a trigger to Table4 (although we would have to do that, too).
So although it was a lot of work, that's what people had to do. And there were large, complex (for their time) applications written using this process for managing RI. But then what happened when these developers upgraded to SQL Server/Sybase version 4, where triggers could cause other triggers to fire? In order not to break everything when a trigger on Table1 was fired that updated Table2, Table3 and Table4, we wouldn't want Table2's and Table3's own triggers to ALSO fire.
So Microsoft/Sybase gave us a new configuration option called 'nested triggers'.
The default value of 1 means that the actions in one trigger can cause another trigger to fire. Changing this value to 0 will take you back in time, to when all actions to be taken had to be coded into a single trigger. The 'nested triggers' option is still around, but I haven't seen anybody even touch it for almost 2 decades. In fact, I bet there a few people out there who never even noticed it was there.
Wishing you all great relationships,
and a Happy Valentine's Day