Edit Question - KnowledgeHub

Title Body

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 affe

Tags (comma-separated) Cancel