• I wouldn't put any constraints on the Mirror / Audit Log tables.

    Think about it. All the constraints that were against the original table have already been applied before the trigger has been called. So all constraints have already been met.

    If your Mirror table is only to track the LAST change, then your trigger should attempt updating rows in the Mirror table before inserting new ones. Again in this case I still wouldn't apply and constraints to the Mirror table.

    Logic:

       UPDATE MirrorTable

         SET MirrorTable values equal to Inserted/Deleted     

       IF @@ROWCOUNT equals 0 -- There are no previous changes in MirrorTable

         INSERT  into MirrorTable from Inserted/Deleted

     

    If you want your MirrorTable to be tracking all changes and someone updates a row, then updates the row again, your going to end up with rows in your mirror table that wont adhere to a unique primary key / unique constraint. If you are auditing the changes to records, not just insert/deletes then duplicate primary key records should be expected, and OK.


    Julian Kuiters
    juliankuiters.id.au