Inserting rows with identity column and instead of trigger
DennisRehm
Posts: 13 Bronze 2
I have a table with an identity column. This value is generated by the database on an INSERT. The table also has an INSERT INSTEAD OF trigger to provide referential integrity for some values of a column. When I use Data Compare to compare the table in two databases, and rows need to be inserted in one, the insert fails. Data Compare sets the table so the identity column value can be inserted (good) but the instead of trigger does not include the identity column in its INSERT statement. After I disable the instead of trigger, everything works fine.
If you're looking for an enhancement to the product, you could disable any INSERT instead of triggers automatically when the table contains an identity column because the insert will always fail. The trigger can be enabled afterwards. Obviously I can do it manually, but it seems I usually forget once and all the changes get made to other tables and then rolled back when the inserts to this table fail.
Thanks.
If you're looking for an enhancement to the product, you could disable any INSERT instead of triggers automatically when the table contains an identity column because the insert will always fail. The trigger can be enabled afterwards. Obviously I can do it manually, but it seems I usually forget once and all the changes get made to other tables and then rolled back when the inserts to this table fail.
Thanks.
This discussion has been closed.
Comments
Have you checked out the Disable DML triggers? This may help you.
Regards
David
PS if this does help, thank RichardM.
Project Manager
Red Gate Software Ltd