February 2, 2011 at 2:12 am
Hi,
Please help me,
create table parenttable(id int,name varchar(10),[address] varchar(10),constraint [primarykeyvalue] primary key(id))
--creating child table with on update cascade
create table childtable1(id int,name varchar(10),CONSTRAINT foreignkey
FOREIGN KEY(id) REFERENCES parenttable(id) ON UPDATE CASCADE)
differnt approches by which:
-> if we update values in parenttable that should effect the child table(
Are there any approches other than TRIGGERS,on update CASCADE)
February 2, 2011 at 2:17 am
If I understand your question correctly, you have two (easy) options:
* triggers
* UPDATE CASCADE
You can also write a second UPDATE statement in your application logic, that takes the updated IDs of the parent table and updates the child table accordingly.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2011 at 2:23 am
I got the answer with both the options . Is there any option other than Trigger,on update cascade
February 2, 2011 at 2:25 am
nandakrishnacherukuri (2/2/2011)
I got the answer with both the options . Is there any option other than Trigger,on update cascade
You can also write a second UPDATE statement in your application logic, that takes the updated IDs of the parent table and updates the child table accordingly.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2011 at 2:41 am
Hi,
If there are many child tables which are reffering to the parent table then?
February 2, 2011 at 2:45 am
Then UPDATE CASCADE would be the most obviously easy solution.
How many times are you updating a primary key?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2011 at 2:50 am
Not only changing the primary key column, we want to change remaining columns in the record..
By using on update cascade we can update the primary & foreign key column
through trigger we can update any no columns..
I wanna any another method to do this update(which updates remaining columns)
February 2, 2011 at 2:52 am
I'm here......Werzz ur progress standing now.....;-)
February 2, 2011 at 2:57 am
shanureddy (2/2/2011)
Not only changing the primary key column, we want to change remaining columns in the record..By using on update cascade we can update the primary & foreign key column
through trigger we can update any no columns..
I wanna any another method to do this update(which updates remaining columns)
Allright, when I read back the original requirements (from another poster) and the requirement you post here, I can conclude that you want to update the ID column and the name column in the parent table, and you want those changes reflected in the child tables. Right? Correct me if I'm wrong.
But for some reason you want other alternatives than triggers and UPDATE CASCADE? Right?
What about normalization? If you normalize your design, the name column shouldn't be in your child tables. You retrieve the necessary columns by using JOINS. So, when you update your parent table, there is no need updating your child tables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2011 at 3:04 am
create TRIGGER parent_trigger ON parenttable
FOR update AS
begin
declare @id int
DECLARE @name VARCHAR(10)
declare @id1 int
select @id=id from inserted
select @name=name from inserted
select @id1=id from deleted
update childtable1 set id=@id,name=@name where id=@id1
update childtable2 set id=@id,name=@name where id=@id1
end
it can be useful
February 2, 2011 at 3:09 am
Okay.. Now tel me if any other alternative other than dis two approaches..
Not on update cascade & trigger.
Is there any other method???????????????
February 2, 2011 at 3:11 am
shanureddy (2/2/2011)
create TRIGGER parent_trigger ON parenttableFOR update AS
begin
declare @id int
DECLARE @name VARCHAR(10)
declare @id1 int
select @id=id from inserted
select @name=name from inserted
select @id1=id from deleted
update childtable1 set id=@id,name=@name where id=@id1
update childtable2 set id=@id,name=@name where id=@id1
end
it can be useful
This will work for a single row update, but what if multiple rows are updated at once?
How will your trigger handle that? (hint: it will not. It will crash)
All of this can be avoided by having a proper database design, in other words, by normalizing your tables.
The issues faced here are exactly the reason why normalization exists, so that you won't have to do the same update at multiple places.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2011 at 3:22 am
I need the another approach(other than trigger and update cascade)..If any Please do reply
February 2, 2011 at 4:14 am
You have only a few options. Triggers & CASCADE as you already know, or you have to write your code so that it updates both at the same time. You can use the OUTPUT clause in an update against the parent table to get the rows and values affected and then update the child table in a second statement, all within one batch. That's the best way to deal with multi-row updates/inserts/deletes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2011 at 4:19 am
Apparently you are not understanding the responses you are being given to your question. Several people have given you other options than the two you don't want. The primary option being to redesign your schema.
Is there a reason why you don't want to use Update Cascade or Triggers?
Is there a reason why you're not reading the other responses? If you don't understand the other options, please tell us what part of them confuses you so we can find a way to explain them better. Otherwise, we assume that you are ignoring these other options and will stop responding to your question.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply