Update main account if child account updated

Question

I have sql table designed as below:

----      ---------------------- ---     ----  -----
1000      Assets                  0        0
1001      Cash                    0        0    1000
1002      Banks                   0        0    1000
1003      NCB Bank                0        0    1002
1004      NCB NO 123456789523555  0        0    1003
1005      NER Bank                0        0    1002
1006      NER NO 123659888845555  0        0    1005
-----------------------------------------------------

I want to if account no. 1006 updated this change will reflected on account no. 1005 and also account no 1002 and also account no. 1000 because all these accounts related to each others.

Account no. 1006 child of account 1005 and account 1005 child of account 1002 and account 1002 child of account 1000

Can I do this with trigger?

Always avoid triggers, they'll make your database impossible to maintain. Instead use procedural code, or a recursive CTE, to find the list of related accounts. Then you can run a query to update them all. — Andomar, Aug 20 '14 at 07:31
thank you for reply i am trying to do it with recursive procedure but i could not can you please give me an example for recursive procedure ? — S.M.Oth, Aug 20 '14 at 08:05

score 1 · Accepted Answer · answered Aug 24 '14 at 22:30

Here's an example of how to do this with a recursive CTE:

if object_id('dbo.Account', 'U') is not null
  drop table dbo.Account
go

create table dbo.Account
(
    AccountID int, 
    AccountDESC varchar(25),
    Balance money,
    DidIChangeFLAG char(1),
    ParentAccountID int
)
go

insert into dbo.Account values 
     (1000,'Assets ', 0, 'N', null)
    ,(1001, 'Cash',  0, 'N', 1000)
    ,(1002, 'Banks', 0, 'N', 1000)
    ,(1003, 'NCB Bank',                0, 'N', 1002)
    ,(1004, 'NCB NO 123456789523555',  0,'N', 1003)
    ,(1005, 'NER Bank',                0, 'N',1002)
    ,(1006, 'NER NO 123659888845555',  0,'N',1005)

go


WITH UpdateAccount (AccountID, ParentAccountID)
as
(
    Select 
        AccountID,
        ParentAccountID 
    from 
        dbo.Account 
    where 
    -->Child account updated that we want to be the starting point for the recursive updates
        AccountID=1006
    union all
    Select 
        a.AccountID, 
        a.ParentAccountID 
    from 
        dbo.Account a 
        join UpdateAccount b on a.AccountID=b.ParentAccountID
)

update a set a.DidIChangeFLAG='Y' 
from 
    dbo.Account a
    join UpdateAccount b on a.AccountID=b.AccountID

Select
    AccountID 
    ,AccountDESC  
    ,Balance  
    ,DidIChangeFLAG  
    ,ParentAccountID  
from 
    dbo.Account

Update main account if child account updated

1 Answers1