Board index MS SQL Server

CASCADE changes to foreign key?

CASCADE changes to foreign key?

Post by Tibor Karasz » Wed, 16 Apr 1997 04:00:00



Tianshan,

(1) No.

(2) No. Only after-triggers.

(3) Constraints are checked before triggers. The constraint will halt the
command and the trigger will never execute. Remove the constraint or do the
modification through stored procedures and perform the "cascading" function
in you stored procedure.

There exist a knowledgebase article regading this subject, but I don't
remember it's number. Hopefully someone else might post the number here...
--

Instructor (MCT, MCSE, MCSD)
Cornerstone Sweden AB



Quote:> I'm sorry if these are stupid questions.

> (1)Can I change foreign key Delete-Rule
> to "CASCADE changes to foreign key"?

> (2)Can SqlServer triggers be used like
> QRACLE such as "BEFORE/AFTER insert"?
>                 ~~~~~~~~~~~~
> (3)How to CASCADE delete to foreign key
> using triggers?
>    Every time I'll receive an error when
> I deleted a record which contain a
> primary-key.The trigger will not execute
> before delete.

> Thanks in advance.

 
 
 
Top

1. Changing Primary Key Values when foreign key constraints exist

Given the following tables :
--
create table table1 (a int not null primary key, b int null)
create table table2 (a int not null, c int not null, d int null primary
key(a,c)
   foreign key (a) references table1 (a))

insert table1 values (1, 1)
insert table2 values (1, 1, 11)
insert table2 values (1, 2, 12)
--
Now, I want to be able to do something like this :
update table1 set a = 2 where a = 1
update table2 set a = 2 where a = 1

, but the foreign key constraint does not allow it.
I can think of some ways around this :

a) disable the foreign key constraint, perform the updates, then re-enable
the constraint.

b) create a new table1 record, do the updates, then delete the original
table1 record. eg
begin tran
insert table1 values (2, 1)
update table2 set a = 2 where a = 1
delete table1 where a = 1
commit tran

I don't much like either solution.
Is there a better approach ?

TIA,
Stephen

2. rst.AddNew code problem

3. Cascading updates on 2 foreign keys in one table

4. DB connection error with 40 Users

5. Using SQL DMO to create a foreign key with cascading INSERTs and UPDATEs

6. Errors

7. newbie: foreign key "on delete cascade"?

8. Stored Procedure Builder / AIX 5L & DB/2 V 7.1

9. Urgent: Cascade deleting with the presence of a foreign key

10. Integrity Cascading and foreign key

11. Enforce Cascade Update/Delete on Foreign Keys

12. Question: Cascading updates across foreign key

13. Cascade deletes with foreign key constraints.


All times are UTC
Board index