CREATE TRIGGER utr_UpdateReferenceStatus on Sample For Update
AS 
IF UPDATE(SampleStatus)
BEGIN
	
	SET NOCOUNT ON;
	If (Select s.SampleStatus From Sample s Join inserted i on s.SampleID = i.SampleID) = 5		
		If (SELECT CASE
				WHEN NOT EXISTS(SELECT *
					FROM Sample
					WHERE (Select SampleStatus from Sample where SampleReferenceID = (Select s.SampleReferenceID from Sample s Join inserted i on s.SampleID = i.SampleID))  <> 5) THEN 1
				ELSE 0) = 1
			Update Sample Set SampleReferenceStatus = 1 where SampleID = (Select s.SampleID From Sample s Join inserted i on s.SampleID = i.SampleID)

END
GO

Thanks for the help.

UPDATE s SET
	SampleReferenceStatus = 1 -- 1 = completed SampleReferenceStatus
FROM
	[Sample] s
WHERE
	s.SampleReferenceID IN (SELECT DISTINCT SampleReferenceID FROM INSERTED)
	AND NOT EXISTS
	(
		SELECT * FROM [Sample] s2
		WHERE s2.SampleReferenceID = s.SampleReferenceID
		AND ISNULL(s2.SampleStatus,0) <> 5 -- 5 = completed SampleStatus
	);

Trigger on Update of Sample Table
{
    If (BottleStatus of record being updated = 5)
    {
	If (If BottleStatus of all bottles tied to the same reference number = 5)
	{
		Update RefStatus to 1
	}
    }
}

quote:

---

Originally posted by visakh16

CREATE TRIGGER utr_UpdateReferenceStatus on Sample For Update
AS 

Update s
set s.RefStatus = '1'  
From Sample s 
Join inserted i 
on s.SampleID = i.SampleID
outer apply (select count(1) as cnt
             from Sample
             where SampleReferenceID = s.SampleReferenceID 
             and SampleStatus <> '5'
            )tmp
where i.SampleStatus = '5'
and coalesce(tmp.cnt,0)=0		
END
GO

Make sure you've nested trigger set to false in db

---

quote:

---

Originally posted by visakh16

ok. then let us know on what basis (columns) you link reference table to Sample table?

---