• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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?
0
GordonPrince
Asked:
GordonPrince
  • 5
  • 3
  • 2
  • +1
3 Solutions
 
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
 
GlobaLevelCommented:
I have to review this every now and then:
http://support.microsoft.com/kb/142480
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GlobaLevelCommented:
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
 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now