I have two tables with relationship
Test is parent table with testNum as a primary key and class is the child table witch contain testNum as a forgien key. If I try to update the testNum on test and update the testNum in class test, how I do it?

Actually I created a update stored procedure on test table and trigger to update the testNum in class table however I’ve got an error as
“Update statement conflict with column reference constraint ‘fk_class_testNum’ in database”

How can I solve this problem?
Please Help.

**************************************************
My DDL, stored procedure and trigger are down below

class table DDL
create table class(
classNum char(4) not null primary key,
className varchar(25) not null,
classDay char(3) not null,
classTime char(8) not null,
testNum char(5) not null
);


alter table class
add constraint fk_class_testnum
foreign key (testNum)
references test (testNum)
;

**********************************************
test table DDL
create table test(
testNum char(5) primary key,
testName varchar(50) not null,
testDate smalldatetime not null,
testFee money not null,
);

******************************************
My update stored procedure is
create proc sp_update_testNum_test
@testNum char(5),
@new_testNum char(5)
as

if not exists (select testNum
from test
where testNum =@testNum)
begin
print 'testNum is not a valid Number'
return -1
end

begin transaction
update test set testNum = upper(@new_testNum)
where testNum = upper(@testNum)

/* Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print 'Update is failed'
return -1
end
print 'testNum has been updated'
commit transaction
return 0
go

**************************************************
My trigger is

create trigger tri_up_testNum_class
on test
for update
as
If update (testNum)
Begin declare @old_testNum char(5), @new_testNum char(5)
Select @old_testNum =
(select testNum
from deleted)
select @new_testNum =
(select testNum
from inserted)
update class
set testNum = @new_testNum
where testNum = @old_testNum
end

************************************************** ***
execution is
execute sp_update_testNum_test
‘GT109’,’GT209’

what database is this?

because, you know, this is why ON UPDATE CASCADE was invented

I thought that I used the update cascade on my trigger in my first message, didn't I?
That is why i was kind of supprised to have the error.
I knew, if i use the cascade, i will be able to delete or update the related child table columns.

I referenced example of cascading update trigger to create my update trigger but i had thaterror. Could you tell me what i did wrong on my code?



thanks

i'm sorry, i cannot help you with the code

all i know is that if you update the primary key value of some row, then the related foreign key value(s) are automatically updated without any trigger or stored proc

all you have to do is declare ON UPDATE CASCADE when you define the foreign key

Before I offer any technical thoughts, I think that it is a VERY poor design choice to ever let users change PK (Primary Key) values. It is an enormous amout of work to manage this kind of change, and its long term effect is to sabotage the project... I've never seen any application that allowed this that wasn't an ongoing nightmare to maintain.

Technically, the problem is that Referential Integrity fires before standard triggers in MS-SQL. If you are using SQL 2000, you should be able to use an "INSTEAD OF" trigger. It is still a bad idea, but it will probably work.

-PatP

pat, i have no idea what you're talking about

ON UPDATE CASCADE is as natural an operation as ON UPDATE DELETE or ON UPDATE RESTRICT

(and none of these requires a trigger)

it is "an enormous amount of work to manage this kind of change" ???

to which my response is: WTF?

are we living on the same planet? change the PK value, and the related FKs update automatically -- how is this a lot of work?

i suppose you ensure unique values by doing a SELECT before you INSERT?

please explain the "enormous" part

The problem is that they aren't using CASCADE (check the code they posted).

Trying to do cascades with either stored procedures or triggers is like teaching a pig to dance on ice. It annoys the pig, and wears you down fast... Even when you get it to work for a bit, it will fail the next time you aren't watching it closely!

-PatP

This is very poetic in a muddy sort of way.

Poetry tends to be muddy. Part of the fun is that you can't get good traction in the stuff!

-PatP

well, you see, there's the whole crux, isn't it

never mind what the code does or does not do, look at what the code was purportedly trying to do --that sure sounds like it should be using ON UPDATE CASCADE to me

I don't know which DBMSs support ON UPDATE CASCADE. I do know that Oracle doesn't, which is a pity. But the OP here isn't using Oracle.

i have to say something here because this board won't let you reply with just a quote, it says "The message you have entered is too short. Please lengthen your message to at least 10 characters"

Hi, guys
Thank you very much for all your input of my question. By the way this all start it because of my ignorance of SQL2000.
I did not know I could able to put delete / update cascade on my foreign key alter code. After second message I try to find the way to do cascade and that was that simple. After I found how to do it, I was amazed how simple that was. I did not even need triggers for delete and update process which I initially plan to do.


Today, I came back to my question; I’ve got all these thoughts... sorry I did not respond sooner.

Thanks for your help guys... sincerely

Now, I am cruising..

That always gets me into trouble (not like I need any help there!). I've learned to use the code that people post instead of what they meant while I'm debugging. That works a lot better for me, since compilers and database engines tend to take their code rather literally!

Yes, you are correct that a CASCADE will make this easier to code (and it won't be the freight wreck I described to manage). That doesn't mean that I consider it to be a good answer, since the cost of cascades can still be rather ugly at run time.

I know that you (Rudy) prefer natural keys, but in my opinion any use of CASCADE just begs for a surrogate key!

-PatP

I can't agree with either of you, though the constraint clauses are so elegant it's hard to ignore their presence.

Having to update PK's and subsequently FK's tells me that the app tends to ignore the history. And we all know that forgetting the history will lead to repeating it, and yet, I'd add, without even knowing it, because there is NO RECORD OF IT!!!

So, if a part number, for example, used to have value of 1, and then HAD to be changed (for whatever bizarre reason) to 2, it means...can't you all see what it will mean? Isn't it a prime example of a design initiative that undermines and probably does not even conform to business requirements?