SQLTeam.com | Weblogs | Forums

How to update a value on another table due to updated value for the same fk value?

Hi

I have these columns in these two tables:

CREATE TABLE [dbo].[Nzeel](
[Id] [decimal](9, 0) NOT NULL,
[HalaId] [int] NOT NULL,
--To the last

CREATE TABLE [dbo].[Cases](
[Id] [int] IDENTITY(1,1) NOT NULL,
[NzeelId] [decimal](9, 0) NOT NULL,
[CaseStatusId] [int] NOT NULL,
[CloseStatus] [bit] NOT NULL,
--Some code

ALTER TABLE [dbo].[Cases]  WITH CHECK ADD  CONSTRAINT [FK_Cases_Nzeel_NzeelId] FOREIGN KEY([NzeelId])
REFERENCES [dbo].[Nzeel] ([Id])
ON DELETE CASCADE

I want when all of Cases.CaseStatusId values = 3 of the same Cases.NzeelId value, then update the value of CloseStatus = 1 and Nzeel.HalaId = 2

I'm trying this trigger:

 AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
IF exists (select 1 from Cases where CaseStatusId = 3 and inserted.NzeelId = NzeelId)
BEGIN
	UPDATE t
	SET HalaId = 2
	FROM [Nzeel] t
	WHERE exists (select 1 from inserted a where a.NzeelId=t.Id);
	UPDATE c
	SET CloseStatus = 1
	FROM [Cases] c
	WHERE Id = inserted.Id;
END

but this gives me there errors:

Msg 4104, Level 16, State 1, Procedure TRG_CasesAftIns, Line 7 [Batch Start Line 4]
The multi-part identifier "inserted.NzeelId" could not be bound.
Msg 4104, Level 16, State 1, Procedure TRG_CasesAftIns, Line 16 [Batch Start Line 4]
The multi-part identifier "inserted.Id" could not be bound.

How to solve issue please?

Change this

to this

IF exists (select 1 from cases c JOIN  inserted i on c.Nzeelid = i.Nzeelid  where c.CaseStatusId = 3)

Thanks
but this one updates the HalaId if one value of CaseStatusId = 3 for the same NzeelId
I want to fire the trigger only if all of the values of CaseStatusId = 3 for the same NzeelId.
How to perform this please?

IF exists (select 1 from cases c JOIN  inserted i on c.Nzeelid = i.Nzeelid  where c.CaseStatusId <> 3)
       RETURN

UPDATE t
	SET HalaId = 2
	FROM [Nzeel] t
	WHERE exists (select 1 from inserted a where a.NzeelId=t.Id);

UPDATE c
	SET CloseStatus = 1
	FROM [Cases] c
	WHERE Id = inserted.Id;

I don't know how but this one gives the same result!
even if one value of CaseStatusId = 3 for some NzeelId then the trigger fired and the value if Nzeel.HalaId is changed which I don't want!
I want only if all of the values of CaseStatusId = 3 for the same NzeelId.