prevent change to one column in table that's part of key with cascading updates
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?
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?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
likewise: disable the field in any application for "edit" ...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AngeIII...your the master..but I thought that DENY was being phased out by MS....im not sure it can even be used in 2008...
GlobalLevel:
The DENY ALL syntax is deprecated. ...
so, the DENY surely will stay.
The DENY ALL syntax is deprecated. ...
so, the DENY surely will stay.
ASKER
I want to enable TaxYear to be updated IF there aren't any rows with ParcelNo + TaxYear in the OtherTable. So DENY update on TaxYear is more restrictive than what I want.
From reading the link GlobalLevel included, it doesn't seem like it would work to remove the cascading update from the OtherTable and try to make a trigger do updates to OtherTable.ParcelNo but not do updates to OtherTable.TaxYear, either, since the cascading updates on Parcel would fail before the trigger fires. Or is there something in here I'm missing?
I hate to go back to relying on application logic to test whether or not there are any rows in OtherTable before I allow TaxYear to be updated, since there are other methods of working with this data other my application.
From reading the link GlobalLevel included, it doesn't seem like it would work to remove the cascading update from the OtherTable and try to make a trigger do updates to OtherTable.ParcelNo but not do updates to OtherTable.TaxYear, either, since the cascading updates on Parcel would fail before the trigger fires. Or is there something in here I'm missing?
I hate to go back to relying on application logic to test whether or not there are any rows in OtherTable before I allow TaxYear to be updated, since there are other methods of working with this data other my application.
well, then, 2 options:
* INSTEAD OF trigger (for update)
* normal trigger (for update)
in both cases, you could use the EXISTS clause, and for the INSTEAD of trigger, you would say:
IF NOT EXISTS ( ... )
BEGIN
INSERT ...
END
in the other case, you would say:
IF EXISTS( ... )
BEGIN
ROLLBACK
RAISERROR ...
END
* INSTEAD OF trigger (for update)
* normal trigger (for update)
in both cases, you could use the EXISTS clause, and for the INSTEAD of trigger, you would say:
IF NOT EXISTS ( ... )
BEGIN
INSERT ...
END
in the other case, you would say:
IF EXISTS( ... )
BEGIN
ROLLBACK
RAISERROR ...
END
IF EXISTS(SELECT NULL FROM DELETED d join othertable o ON o.TaxYear = d.TaxYear AND o.ParcelNo = d.ParcelNo)
BEGIN
...
ENDASKER
I tried that and it doesn't work. The TaxYear column is updated by the cascading update BEFORE the trigger fires. In the SQL help it says you can't use an INSTEAD OF trigger if the table is subject to cascading updates, so I didn't that would work, either.
When I put this code in the trigger, there are no rows with the old TaxYear, but there are rows with the new TaxYear value. Which matches the documentation that the casdading updates have all occured BEFORE the trigger fires. So OtherTable has already been updated.
Now that I write this, I'm thinking that if OtherTable has rows with the NEW TaxYear, it's only because the cascading update has just changed it, therefore if I roll back the transaction, I would be rolling back the cascading updates, too. Does that logic sound like it would hold?
When I put this code in the trigger, there are no rows with the old TaxYear, but there are rows with the new TaxYear value. Which matches the documentation that the casdading updates have all occured BEFORE the trigger fires. So OtherTable has already been updated.
Now that I write this, I'm thinking that if OtherTable has rows with the NEW TaxYear, it's only because the cascading update has just changed it, therefore if I roll back the transaction, I would be rolling back the cascading updates, too. Does that logic sound like it would hold?
declare @dyear int, @iyear int,
@dPar varchar(35), @iPar varchar(35)
set @dyear = (select TaxYear from deleted)
set @dPar = (select ParcelNo from deleted)
print '@dyear = ' + convert(char(4),@dyear) + ', ' + @dPar
set @iyear = (select TaxYear from inserted)
set @iPar = (select ParcelNo from inserted)
print '@iyear = ' + convert(char(4),@iyear) + ', ' + @iPar */ASKER
But now I remember -- I tried this, too. If the trigger fires AFTER the cascading updates have occured, I don't have a way to telling whether or not TaxYear was updated, since it's the same from both DELETED and INSERTED by this time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
deny UPDATE on TaxYear column should do the job
will though only work if the application does not connect as sa/dbo of the table...