mysql 触发器 避免递归,防止相互递归执行触发器？

Suppose you have the tables Presentations and Events. When a presentation is saved and contains basic event information, such as location and date, an event will be created automatically using a trigger. (I'm afraid it's impossible for technical reasons to simply keep the data at one place and use a view.) In addition, when changing this information later on in the presentation, the trigger will copy the updates over to the event as well, like so:

CREATE TRIGGER update_presentations

ON Presentations

AFTER UPDATE

AS

BEGIN

UPDATE Events

SET Events.Date = Presentations.Date,

Events.Location = Presentations.Location

FROM Presentations INNER JOIN Events ON Presentations.EventID = Events.ID

WHERE Presentations.ID IN (SELECT ID FROM inserted)

END

Now, the customer wants it so that, if a user ever changes the information in the event, it should go back to the presentation as well. For obvious reasons, I can't do the reverse:

CREATE TRIGGER update_events

ON Events

AFTER UPDATE

AS

BEGIN

UPDATE Presentations

SET Presentations.Date = Events.Date,

Presentations.Location = Events.Location

FROM Events INNER JOIN Presentations ON Events.PresentationID = Presentations.ID

WHERE Events.ID IN (SELECT ID FROM inserted)

END

After all, this would cause each trigger to fire after each other. What I could do is add a column last_edit_by to both tables, containing a user ID. If filled by the trigger with a special invalid ID (say, by making all user IDs of actual persons positive, but user IDs of scripts negative), I could use that as an exit condition:

AND last_edit_by >= 0

This might work, but what I'd like to do is indicate to the SQL server that, within a transaction, a trigger should only fire once. Is there a way to check this? Or perhaps to check that a table has already been affected by a trigger?

Answer thanks to Steve Robbins:

Just wrap the potentially nested UPDATE statements in an IF condition checking for trigger_nestlevel(). For example:

CREATE TRIGGER update_presentations

ON Presentations

AFTER UPDATE

AS

BEGIN

IF trigger_nestlevel() < 2

UPDATE Events

SET Events.Date = Presentations.Date,

Events.Location = Presentations.Location

FROM Presentations INNER JOIN Events ON Presentations.EventID = Events.ID

WHERE Presentations.ID IN (SELECT ID FROM inserted)

END

Note that trigger_nestlevel() appears to be 1-based, not 0-based. If you want each of the two triggers to execute once, but not more often, just check for trigger_nestlevel() < 3 in both triggers.

解决方案

I'm not sure about doing it per transaction, but do you need nested triggers switched on for other parts? If you switch them off on the server then a trigger won't fire from another trigger updating a table.

EDIT (answer from the comments): You will need to alter trigger A to use TRIGGER_NESTLEVEL