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?
LVL 4
GordonPrinceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://msdn.microsoft.com/en-us/library/ms188338.aspx
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...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
likewise: disable the field in any application for "edit" ...
0
GlobaLevelProgrammerCommented:
I have to review this every now and then:
http://support.microsoft.com/kb/142480
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

GlobaLevelProgrammerCommented:
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...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
GlobalLevel:
The DENY ALL syntax is deprecated. ...
so, the DENY surely will stay.
0
GordonPrinceAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
IF EXISTS(SELECT NULL FROM DELETED d join othertable o ON o.TaxYear = d.TaxYear AND o.ParcelNo = d.ParcelNo)
BEGIN
  ...
END

Open in new window

0
GordonPrinceAuthor Commented:
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?
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 */

Open in new window

0
GordonPrinceAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, I see only these solutions:
* disable cascading updates, and do the "cascading update" via trigger OR application code
* disable the TaxYear update in the application ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alpesh PatelAssistant ConsultantCommented:
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' WITH NOCHECK ADD  CONSTRAINT  ' + CONSTRAINT_NAME +
' FOREIGN KEY(' + (Select Top 1 COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = information_schema.table_constraints.CONSTRAINT_NAME) + ')' +
' REFERENCES ' + TABLE_SCHEMA + '.' +
(Select OBJECT_NAME(rkeyid) from sys.sysreferences WHERE OBJECT_NAME(constid) = information_schema.table_constraints.CONSTRAINT_NAME) +
'(' + (SELECT
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 AND OBJECT_NAME(ic.OBJECT_ID) = (Select OBJECT_NAME(rkeyid) from sys.sysreferences WHERE OBJECT_NAME(constid) = information_schema.table_constraints.CONSTRAINT_NAME)) + ')' +
' ON UPDATE CASCADE ' +
' ON DELETE CASCADE '
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.