CODEWITHSUNDEEP
sqlsql-servertriggers
Vojb-
Vojb-

Reputation: 19

SQL Server : how to update table b , when a single row is updated in table a?

I have created a SQL Server trigger which is supposed to add a kill to a doctor whenever one of his patients changes state to "killed". But I don't know how to just get the one row that is updated. As it looks now, the doctor get patientKilled++ for every patient he ever had before as well.

How can I solve this ?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[PatientKilled] 
ON [dbo].[Patient]
AFTER UPDATE 
AS
BEGIN
    DECLARE

    SET NOCOUNT ON;

    UPDATE Doctor 
    SET PatientsKilled += 1 
    FROM Doctor d 
    INNER JOIN inserted p ON d.DoctorId = p.DoctorId 
    WHERE p.PatientState = 'Killed' 
END

Upvotes: 1

Views: 64

Answers (2)

Martin Smith
Martin Smith

Reputation: 453067

The word “kill” implies the doctor is homicidal but disregarding that issue this should do what you need.

You should check the previous state and only increment the counter if this is changed. I have assumed that “Killed” is a permanent state so no need to add logic to decrement. You should consider if you need that.

WITH p AS
(
SELECT COUNT(*) AS NewKills, 
               I.DoctorId
FROM INSERTED I JOIN DELETED D
ON I.PatientId = D.PatientId
WHERE I.PatientState = 'Killed'  
    AND (D.PatientState <> 'Killed' OR D.PatientState IS NULL)
GROUP BY I.DoctorId
)
UPDATE d
SET PatientsKilled += NewKills
FROM Doctor d 
    INNER JOIN p ON d.DoctorId = p.DoctorId

Upvotes: 1

Pete Carter
Pete Carter

Reputation: 2731

USE [AD17_Hospital]
GO
/****** Object:  Trigger [dbo].[PatientKilled]    Script Date: 2017-10-21 19:51:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PatientKilled] ON [dbo].[Patient]
AFTER UPDATE 
AS
BEGIN

SET NOCOUNT ON;
UPDATE Doctor 
SET PatientsKilled (SELECT COUNT(*) FROM Patients WHERE DoctorID = INSERTED.DoctorID AND PatientState = 'Killed')
FROM Doctor d 

END

Upvotes: 0

Related Questions