January 19, 2022 at 9:55 pm
I know there is, somewhere, T-SQL that will allow me to update (keep in sync) a field that appears in two different table without them continuously triggering each other.
The field on both tables is date;
If table A field date is updated, the table A field date trigger copies and updates table B field date. Then stops
If table B field date is updated, the table B field date trigger copies and updates table A field date. Then stops
I know if we don't somehow force a stop, then the two triggers will keep going back and forth.
Can anyone help or point me in the right direction?
Thank you,
January 20, 2022 at 1:38 pm
Simple question. Why not just update both places as opposed to using a trigger?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 20, 2022 at 5:32 pm
You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this method, let me know.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 23, 2022 at 10:28 pm
>> 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,
CONSTRAINT Band_Bookings
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.
January 24, 2022 at 6:28 am
Update a column only when the new value is different from the current one
UPDATE
...
WHERE NOT (SourceTable.Col = TargetTable.Col)
If the column Col allows NULLs make sure you handling them right.
_____________
Code for TallyGenerator
January 26, 2022 at 4:16 pm
You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this method, let me know.
Or you can use it directly, I don't know if one is preferable.
IF TRIGGER_NESTLEVEL() >= 1
RETURN
ELSE
BEGIN
UPDATE ....
END
The second trigger will be fired, but it won't do anything. There is a Nested Triggers Enabled database setting. I am not familiar with it, but I assume it prevents triggers from firing triggers, which may or may not work with the use of triggers elsewhere in the database.
January 28, 2022 at 7:22 pm
ScottPletcher wrote:You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this method, let me know.
Or you can use it directly, I don't know if one is preferable.
IF TRIGGER_NESTLEVEL() >= 1
RETURN
ELSE
BEGIN
UPDATE ....
ENDThe second trigger will be fired, but it won't do anything. There is a Nested Triggers Enabled database setting. I am not familiar with it, but I assume it prevents triggers from firing triggers, which may or may not work with the use of triggers elsewhere in the database.
Let me read up on both of these. This wasn't an issue until our ERP change security.
I appreciate your help,
January 28, 2022 at 8:21 pm
Ed B wrote:ScottPletcher wrote:You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this method, let me know.
Or you can use it directly, I don't know if one is preferable.
IF TRIGGER_NESTLEVEL() >= 1
RETURN
ELSE
BEGIN
UPDATE ....
ENDThe second trigger will be fired, but it won't do anything. There is a Nested Triggers Enabled database setting. I am not familiar with it, but I assume it prevents triggers from firing triggers, which may or may not work with the use of triggers elsewhere in the database.
Let me read up on both of these. This wasn't an issue until our ERP change security.
I appreciate your help,
You've got to be careful here. Since one trigger will always be fired by another one, the second one's nesting level will always be 2+.
I think you'll have to specify the type of trigger in the TRIGGER_NESTLEVEL function. Even then you may need some additional logic, but I have gotten this to work in the past (especially before session settings were available).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply