sql code/ logic

Question

i have a self referencing table called project that has a master detail relationship between parent projects, and sub-projects, sub-sub-projects...

one of the fields is releaseId and i need to make sure that the parent projects release id is the same for all its subs...

table fields are projectId, parentProjectId, releaseId

anyone have a clue on how to run such a report in sql 2005?

Answer 1

Adjacency Lists can be dealt with quite effectively using a Common Table Expression. This might be a way to start off.

Answer 2

Ok this is untested and makes some assumptions. First assumption is that the id numbers are integers and that the releaseid will never be 0. Next assumption is the projectid is the PK of the table. Final assumption is that you can tell the parentproject because the parentid is null.

Now the only correct way to do this is in trigger. That way it will always be correct no matter how the data is changed. There is no other completely reliable way to ensure the data stays in synch. Triggers must be written to handle multiple records. This trigger is a rough approximation and is not tested. Be sure to thoroughly test a trigger (including tesiting a multiple rcord insert and a multiple record update) before using it.

create trigger testtrigger on mytable
for insert, update
as

select inserted.projectid as parentid, inserted.releaseid as newreleaseid
into #changedparentreleaseid
from inserted i 
join deleted d 
  on i.projectid = d.projectid
where i.parentid is null
and isnull(d.releaseid,0) <> isnull(i.releaseid,0)

update m
set releaseid = c.newreleaseid
from mytable m
join #changedparentreleaseid c
  on c.parentid = m.parentid