2
vojb
7y

!Rant .. I need some quick help and didnt know where to go.. so fellow ranters please help...

So I have created an sql trigger which is supposed to add a kill to a doctor whenever one of his patients changes state to "killed" . But I dont 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 ?

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
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

Comments
  • 2
    Can't help I'm afraid, but you're working on a rather morbid project...
  • 1
    @alwaysmpe I got a sick sense of humour...
  • 1
    It's been a long time since i've worked with triggers, but as far as i can figure this out, you need to change your last line before the END command. Try doing it like this 'where p.PatientState='Killed' and p.PatientId=@PatientID'.
    I'm not sure if this is the right syntax, but the point is: You are looking at ALL the patients that were killed, not just the last one. You need to reference it so the system knows which patient/doctor to update. I might be wrong, but look into it
  • 1
    @nikola1402 thanks figured it out :)
Add Comment