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?