sql - recursivo - instrucción terminada. se agotó la recursividad máxima(100) antes de finalizar la instrucción.

¿Prevenir la ejecución mutuamente recursiva de desencadenantes?

Question

Supongamos que tiene las tablas Presentations y Events . Cuando se guarda una presentación y contiene información básica del evento, como la ubicación y la fecha, se creará un evento automáticamente con un desencadenador. (Me temo que es imposible por razones técnicas simplemente mantener los datos en un solo lugar y usar una vista.) Además, al cambiar esta información más adelante en la presentación, el desencadenador copiará las actualizaciones al evento también, al igual que:

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

Ahora, el cliente lo quiere para que, si un usuario alguna vez cambia la información en el evento , también debería volver a la presentación. Por razones obvias, no puedo hacer lo contrario:

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

Después de todo, esto provocaría que cada disparador disparara uno tras otro. Lo que podría hacer es agregar una columna last_edit_by a ambas tablas, que contenga una ID de usuario. Si se completa con el desencadenador con una identificación inválida especial (por ejemplo, haciendo positivos todos los ID de usuario de las personas reales, pero los ID de usuario de los guiones son negativos), podría usar eso como una condición de salida:

    AND last_edit_by >= 0

Esto podría funcionar, pero lo que me gustaría hacer es indicarle al servidor SQL que, dentro de una transacción, un disparador solo debe disparar una vez. ¿Hay alguna manera de verificar esto? ¿O tal vez para verificar que una tabla ya haya sido afectada por un disparador?

Responde gracias a Steve Robbins:

Simplemente envuelva las instrucciones UPDATE potencialmente anidadas en una condición IF verificando trigger_nestlevel() . Por ejemplo:

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

Tenga en cuenta que trigger_nestlevel() parece estar basado en 1, no en 0. Si desea que cada uno de los dos desencadenantes se ejecute una vez, pero no con mayor frecuencia, simplemente verifique que trigger_nestlevel() < 3 en ambos desencadenantes.

Answer 1

No estoy seguro de hacerlo por transacción, pero ¿necesita activadores anidados para otras partes? Si los apaga en el servidor, no se disparará un disparador desde otro activador al actualizar una tabla.

EDIT (respuesta de los comentarios): deberá modificar el desencadenador A para usar TRIGGER_NESTLEVEL