nested triggers problem

[Mironenko Anton]

Hello all.
I have a problem:
There are 3 tables:
FIRST (columns: m_id, m_name, m_archive),
SECOND (columns: m_id, m_name, m_archive, m_first_id),
THIRD (columns: m_id, m_name, m_archive, m_second_id).

And relations between them are following:
FIRST -------E SECOND -------E THIRD

What I have to do is:
When flag FIRST.m_archive is changed, flag m_archive has to be changed in
all children tables.

I write trigger for table FIRST:

CREATE TRIGGER [FIRST_ONCHANGE] ON [FIRST]
FOR UPDATE
AS

declare @v_archive_old int
declare @v_archive_new int
declare @v_new_id uniqueidentifier
declare @v_count int

select @v_archive_old = deleted.m_archive
from deleted

select
@v_archive_new = inserted.m_archive,
@v_new_id = inserted.m_id
from inserted

if @v_archive_old <> @v_archive_new
begin
update SECOND
set m_ARCHIVE = @v_archive_new
where
m_FIRST_ID = @v_new_id
end
/

And trigger for table SECOND:

CREATE TRIGGER [SECOND_ONCHANGE] ON [SECOND]
FOR UPDATE
AS

declare @v_archive_old int
declare @v_archive_new int
declare @v_new_id uniqueidentifier
declare @v_count int

select @v_archive_old = deleted.m_archive
from deleted

select
@v_archive_new = inserted.m_archive,
@v_new_id = inserted.m_id
from inserted

if @v_archive_old <> @v_archive_new
begin
update THIRD
set m_ARCHIVE = @v_archive_new
where
m_SECOND_ID = @v_new_id
end
/

Suppose, there are next rows in tables FIRST, SECOND, THIRD:
FIRST SECOND THIRD
first_1 first_1_second_1 first_1_second_1_third_1
first_1_second_1_third_2
first_1_second_1_third_3
first_1_second_2 first_1_second_2_third_1
first_1_second_2_third_2

Problem is following:
When I change flag m_archive in row first_1
flag m_archive is changed in all children objects (tables SECOND and THIRD), except rows:
first_1_second_2_third_1
first_1_second_2_third_2

i.e. trigger in table SECOND is executed only once -
when first row first_1_second_1 (table SECOND) is changed.

I have a question:
How to make MS SQL Server 7.0 update hierarchicaly all children objects?

Thank you.
Anton.

[Paul]

Why not store all the values in the same table ??

------------
Mironenko Anton at 3/12/01 4:04:53 AM

Hello all.
I have a problem:
There are 3 tables:
FIRST (columns: m_id, m_name, m_archive),
SECOND (columns: m_id, m_name, m_archive, m_first_id),
THIRD (columns: m_id, m_name, m_archive, m_second_id).

And relations between them are following:
FIRST -------E SECOND -------E THIRD

What I have to do is:
When flag FIRST.m_archive is changed, flag m_archive has to be changed in
all children tables.

I write trigger for table FIRST:

CREATE TRIGGER [FIRST_ONCHANGE] ON [FIRST]
FOR UPDATE
AS

declare @v_archive_old int
declare @v_archive_new int
declare @v_new_id uniqueidentifier
declare @v_count int

select @v_archive_old = deleted.m_archive
from deleted

select
@v_archive_new = inserted.m_archive,
@v_new_id = inserted.m_id
from inserted

if @v_archive_old <> @v_archive_new
begin
update SECOND
set m_ARCHIVE = @v_archive_new
where
m_FIRST_ID = @v_new_id
end
/

And trigger for table SECOND:

CREATE TRIGGER [SECOND_ONCHANGE] ON [SECOND]
FOR UPDATE
AS

declare @v_archive_old int
declare @v_archive_new int
declare @v_new_id uniqueidentifier
declare @v_count int

select @v_archive_old = deleted.m_archive
from deleted

select
@v_archive_new = inserted.m_archive,
@v_new_id = inserted.m_id
from inserted

if @v_archive_old <> @v_archive_new
begin
update THIRD
set m_ARCHIVE = @v_archive_new
where
m_SECOND_ID = @v_new_id
end
/

Suppose, there are next rows in tables FIRST, SECOND, THIRD:
FIRST SECOND THIRD
first_1 first_1_second_1 first_1_second_1_third_1
first_1_second_1_third_2
first_1_second_1_third_3
first_1_second_2 first_1_second_2_third_1
first_1_second_2_third_2

Problem is following:
When I change flag m_archive in row first_1
flag m_archive is changed in all children objects (tables SECOND and THIRD), except rows:
first_1_second_2_third_1
first_1_second_2_third_2

i.e. trigger in table SECOND is executed only once -
when first row first_1_second_1 (table SECOND) is changed.

I have a question:
How to make MS SQL Server 7.0 update hierarchicaly all children objects?

Thank you.
Anton.