davewill said:qwert231 said:*snip*
There is something else going on. You described "change my data" 2 times with the exact same set of steps. In one it works. In the second it doesn't work. My assumption being that the same triggers are fired in the same sequence for both and therefore (being a computer) the result should be the same.
As I look more at the trigger I'm wondering why. So I'll just ask. Why not simplify the trigger to simply write ApplicantAgencyHistory after Insert, Update and then pass the GetDate() for ApplicantAgency.LastUpdatedOn in the original update sql?
I agree with davewill. Simplify the trigger, set ApplicantAgency.LastUpdatedOn after the actual insert or use GetDate() as input value depending on your choice. And do the ApplicantAgencyHistory insert in the same after insert/update trigger. You have a nice scenario in four steps, which will serve as a excellent "unit test"