Help with a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a trigger

Author

Topic

B1987
Starting Member

3 Posts

Posted - 2012-04-23 : 06:02:03

Hello,

I am completely new to SQL and am trying to create a trigger to update a field when a field in another table is changed...

I have the following two tables with these fields in them...

PATIENTS
Patient_ID
Date_Of_Last_Appointment (Date)

APPOINTMENTS
Appointment_ID
Patient_ID
Date_Of_Appointment (Date)
Status

When a patient attends their appointment, the Status field is changed to "Arrived". When this happens I want the date of the appointment to be inserted into the "Date_Of_Last_Appointment" field for the appropriate patient in the Patients table.

I would really appreciate some help in writing this. I am afraid I haven't got anything at the moment as I have no clue where to even begin. Usually I try to write something first to get help on so sorry!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-23 : 06:12:35

[code]
create trigger ti_APPOINTMENTS
on APPOINTMENTS
for update
as
begin
update p
set Date_Of_Last_Appointment = i.Date_Of_Appointment
from PATIENTS p
inner join inserted i on p.Patient_ID = i.Patient_ID
inner join deleted d on i.Appointment_ID = d.Appointment_ID
where i.Status <> d.Status
and i.Status = 'Arrived'
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

B1987
Starting Member

3 Posts

Posted - 2012-04-23 : 06:24:36

Thank you very much for you help, but I got the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on APPOINTMENTS for update as begin update p set Date_Of_' at line 2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-23 : 06:26:28

SQLTeam.com is on Microsoft SQL Server. For MySQL question, you should try http://forums.mysql.com/ or dbforums.com

KH
[spoiler]Time is always against us[/spoiler]

B1987
Starting Member

3 Posts

Posted - 2012-04-23 : 06:31:04

Oh sorry...thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources