Record update trigger doesn't work when multiple records are updated in batch
Posted on 2009-02-20
Ok, I'm not really sure how to explain this so I will try to be as clear as possible, but bear with me.. I'm sorta new to SQL syntax. Essentially I have a trigger that updates some stuff on insert / change of one of my tables. It works fine when I update a single row but gives an error when I update multiple rows. It seems that this is because I use 'IN' instead of a join but I can't seem to wrap my mind around how this would be done with a join, so any help is greatly appreciated. Here is the query that works with the single update.
IF(SELECT TrigDate FROM INSERTED) IS NOT NULL
UPDATE RecordsTable SET AudDate = (SELECT TrigDate FROM INSERTED), StrtVal = 'WHATEVER'
WHERE UID = (SELECT UID FROM DELETED)