The Mirror table has a primary key constraint. When we try to insert two records with the same primary key ID in the mirror table, it generates an error. I want to track only the last change made to a record. Any previous inserts/updates that have a corresponding mirror table record need to be deleted.
Thank you for your response.
My first work with triggers involved working with some very robust-looking code (written, of course, by someone long gone) that had all kinds of error handling built into every trigger. I used these as a template to develope my own triggers, but my error handling never worked. I went nuts trying to figure out why before I realized it is a waste of time.
A trigger is considered an extension of the original statement. The original statement (and the trigger) are aborted when an error occurs. You can detect the error in the code containing the DML statement, but not in the trigger.
If you need to put extra code in the trigger or elsewhere to avoid errors, go right ahead. But it is pointless to put any "IF @@ERROR > 0" type of code in a trigger. If there is an error, it won't get that far.
Woiuld this work?
UPDATE MirrorTable SET a = inserted.a b = inserted.b c = inserted.cFROM InsertedWHERE Inserted.id = MirrorTable.id
INSERT INTO MirrorTable (id,a,b,c,d) SELECT id,a,b,c,dFROM Inserted LEFT JOIN MirrorTable ON MirrorTable.id = Inserted.idWHERE MirrorTable.id IS NULL
Its late, im tired, so I didnt check if I did the sql right. I'm sure its close though.
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.
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.