I've got a table Parcel with a key that consists of the combination of "ParcelNo" + "TaxYear". I've just finished implementing cascading updates to about 8 tables so if ParcelNo changes, all the rows in the other tables update properly. Now I realize that if I update TaxYear, it cascades to all the other tables, too. I want to prevent changes to TaxYear if there are any other rows in one specified table. I can't figure out how to do this.
At first I thought I'd create a trigger for AFTER UPDATE on the Parcel table that with a test in it against the other table.
IF EXISTS (select 1 from OtherTable )
with a join with DELETED or INSERTED, but the SQL help says
"All referential cascade actions and constraint checks also must succeed before this trigger fires.". So by the time my trigger checks, the rows in both DELETED and INSERTED have already been updated so I don't have my old value anywhere in the trigger to use to make the trigger fire.
Any ideas on how to retain the ability for cascading updates to work if ParcelNo changes but not allow TaxYear to be changed if there are any rows in OtherTable?
Or is revoking update permissions on the TaxYear table the only way to do this?