0

EDITEDIT: I have explained my problem incorrectly. The question in hand -

Create a trigger on the Appointment table that will update LastContactDate on the Patient table each time a new record is added to the Appointment table. The value of the LastContactDate should be the date the record is added.

How do you create a trigger to update LastContactDate column to record the date every time a new record is added to an Appointment table?

This is what i currently have.

CREATE TRIGGER tr_Appointment_AfterInsert
ON Appointment
AFTER INSERT
AS
BEGIN
    INSERT INTO Appointment
    SET LastContactDate = GETDATE()
    FROM Appointment o
    INNER JOIN Inserted i
        ON o.AppDate = i.AppDate
        AND o.AppStartTime = i.AppStartTime
END

Could you help fix this code?

robert404
  • 1
  • 2
  • 3
    If it's just on `INSERT`, why not just use a `DEFAULT` `CONSTRAINT` instead? – Thom A Nov 22 '22 at 11:31
  • I agree, have a look here: https://stackoverflow.com/questions/4888277/add-default-value-of-datetime-field-in-sql-server-to-a-timestamp – Jonas Metzler Nov 22 '22 at 11:32
  • 1
    I also agree a default constraint is more appropriate. With the trigger, your statement should be `UPDATE Appointment` instead of `INSERT INTO Appointment` since the row was already inserted. – Dan Guzman Nov 22 '22 at 11:37
  • Thanks guys and I appreciate your answers. I understand what youve recommended is the most optimal way to do it but I have been tasked by the teacher to create a trigger when a record is added. edit: whether it makes sense or not is beyond me – robert404 Nov 22 '22 at 11:39
  • ```Create a trigger on the Appointment table that will update LastContactDate on the Patient table each time a new record is added to the Appointment table. The value of the LastContactDate should be the date the record is added.``` Maybe I explained poorly but this is the question – robert404 Nov 22 '22 at 11:44
  • 1
    Please do not put clarifications in comments, but edit your question for that. Your question isn't - or wasn't - about a "patient" table, your attempt only includes the table "appointment". So you will need to extend this. – Jonas Metzler Nov 22 '22 at 11:52

1 Answers1

0

this query should be what you are searching for.

CREATE TRIGGER tr_Appointment_AfterInsert
ON Appointment
AFTER INSERT
AS
BEGIN
    UPDATE p
    SET LastContactDate = GETDATE()
    FROM Inserted i
    INNER JOIN Patient p
        -- Here the condition between Patient and Appointment (Inserted has the same columns and the new row of Appointment)
        ON ...
END
Samuele Coassin
  • 96
  • 1
  • 4