SQL Trigger (with PK)
Posted on 2009-05-20
I have two tables, one tracks the location of animals and one tracks the the death of the animal. The table that tracks the location is keyed on (Animal ID + Date Inserted) and the one that tracks death is of course primary keyed on Animal ID.
I would like to make a trigger on the "death table" that removed the animal from its current location (updated (Date Removed).
One way I know to do this is just left join death table to the location table and update all NULL (Date Removed) fields to the same date as the mortality. However, it seems like I would get better performance if I could avoid the join and simply do an update where Animal ID = (Animal ID of death record that was just added) instead of the broad sweeping statements I know how to do. Also, multiple death records may be added at once.