August 26, 2003 at 6:03 am
I have a table with the following fields (Op_id,Op_active,op_parent_id) that mean each operation have many sub operation what I need is when operation active change (op_active) i need to change all the sub operation that relate to the mean operation. (the main operation may have sub operation and the sub operation have also sub and so on)
August 26, 2003 at 6:24 am
Hi deemo,
quote:
I have a table with the following fields (Op_id,Op_active,op_parent_id) that mean each operation have many sub operation what I need is when operation active change (op_active) i need to change all the sub operation that relate to the mean operation. (the main operation may have sub operation and the sub operation have also sub and so on)
just to make things clear to me.
This is your only table?
So a parent project has op_parent_id = NULL, and a subproject op_parent_id <>NULL ?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 26, 2003 at 6:36 am
Thanks Frank,
Yes the parent project has op_parent_id = NULL, and a subproject op_parent_id = op_id of the parent. and to make it more clear this is an example
op_id op_active op_parent_id
===================================
1 0 NULL
2 0 1
3 0 2
4 1 NULL
5 1 4
what I need is when op_active of op_id 1 changed from 0 to 1 all its child to change to 1 also (op_id 2,3)
August 26, 2003 at 7:58 am
There is no easy way to do this in a single operation. What possibilities exist?
1. Use a trigger on the table. If you have cascaded triggers, you are sure to change all the records (up to 32 levels), but this can be quite some overhead. If you have the same child for different parents you might update the same record (and all records below) more than once.
CREATE TRIGGER tr_UpdateOperation
ON Operation
FOR UPDATE
AS
if UPDATE(op_active)
UPDATE Operation
SET Operation.op_active = inserted.op_active
FROM inserted INNER JOIN Operation
ON inserted.op_parent_id = op_id
2. Write a stored procedure to do the same.
The only possibility is to use sort a trigger to run through all the sublevels. There is no such thing as recursion in a set based approach.
August 26, 2003 at 9:08 am
Thank you NPeeters for your clearfication,
but each child belond to one pernt.
can you please tell me more about cascaded triggers, I think it will help solve the problem.
August 26, 2003 at 11:44 pm
Hi deemo,
quote:
Thank you NPeeters for your clearfication,but each child belond to one pernt.
can you please tell me more about cascaded triggers, I think it will help solve the problem.
sorry, I forgot a meeting, so I couldn't come back yesterday.
What NPeeters said is correct.
I think you'll need update triggers that catch the change from 0 to 1.
But I think in addition you have to consider what happens when multiple rows at once are updated. In this scenario a trigger only fires once!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 27, 2003 at 9:09 am
you could try writing a stored procedure that looks something like this.(I have used the table datatype. you could use a temporary table if u want.)
-------------------------------------
create procedure update_active_operation
@op_id int,
@op_active int
as
begin
declare @temp_Oper table (op_id int)
insert into @temp_oper
/* The actual record to be updated*/
select op_id
from operation
where op_id = @op_id
while @@ROWCOUNT <> 0
begin
/* select the ids of all the child
records that are affected
insert op_id into temp table only
if it is not already present*/
insert into @temp_oper
select op_id
from operation
where op_parent in (select op_id
from @temp_oper)
and op_id not in (select op_id
from @temp_oper)
end
update operation
set op_active = @op_active
where op_id in (select op_id
from @temp_oper)
end
-----------------------------------
Hope this helps!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply