>> I know there is, somewhere, T-SQL that will allow me to update (keep in sync) a field [sic] that appears in two different tables without them continuously triggering each other. <<
Every time I see someone confusing a column and a field, I feel like I wasted over 30 years of my life on this language. Does anybody else read the standards? A column is a scalar value, and a field is a subsection of that scalar value that has some incomplete meaning in its own. In the case of dates, those columns have three fields (year, month, day).
Sorry to tell you this, but an update statement is to be used on one and only one table. If these columns are related, then one can reference the other looking at your DDL… Oh, in violation of basic foreign etiquette you failed to post any DDL . I guess we have to start reading your mind or guessing.
You imply that you're doing this with triggers because you still like writing a lot of procedural code instead of declarative relational code. You also seem to have missed the point that the function of a database (yes, even before there was an SQL) is to reduce redundancy, not increase it with copies. You're probably not old enough to remember what it was like to have multiple part carbon paper forms, the same data in multiple files on different magnetic tapes, etc.
> The field [sic] on both tables is date; <<
The date of what? There is no such thing and a proper relational model as a generic, universal, one-size-fits-all "date"; if you want to follow ISO 11179 naming rules instead of depending on a COBOL style context, we need to have names look like <attribute>_<attribute property>.
>> If table A field [sic] date is updated, the table A field [sic] date trigger copies and updates table B field [sic] date.
If table B field [sic] date is updated, the table B field [sic] date trigger copies and updates table A field [sic] date. <<
You've just become a man with two wristwatches, that can be set to different times! Which of these two tables holds the truth? What we do ln RDBMS is to make sure that one fact appears in one place, in one format, one time only. This follows directly from the law of identity (remember that from your first logic class?) we know that "to be is to be something in particular; to be several things or nothing in particular, is to be nothing at all."
In SQL we can reference a single occurrence with a reference clause, which can also include DRI (declarative referential integrity) actions.
CREATE TABLE Events
(event_name VARCHAR(20) NOT NULL,
event_date DATE NOT NULL,
PRIMARY KEY (event_name, event_date),
Let's book some bands for our events. We will reference, not repeat, the events to which were sending these bands. Here is a quick skeleton:
CREATE TABLE Bookings
(band_name VARCHAR(20) NOT NULL,
event_name VARCHAR(20) NOT NULL,
event_date DATE NOT NULL,
REFERENCES Events (event_name, event_date DATE NOT NULL
ON UPDATE CASCADE
ON DELETE CASCADE,
Look up how the DRI actions work. We created the DRI when somebody bothered to look at actual SQL code and found out that updates and deletes were over 80% of the trigger code. That was procedural code. There was no way to optimize it in the database engine.
Please post DDL and follow ANSI/ISO standards when asking for help.