update foreign keys using triggers

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

SQL Server 2005 Forums

Transact-SQL (2005)

update foreign keys using triggers

Author

Topic

ias0nas
Starting Member

36 Posts

Posted - 2008-05-18 : 07:37:37

Hello,

I would like to update foreign keys using triggers. At least thats what I think is the solution when having multiple references to a table in order to avoid the "cycles or multiple cascade paths" error message.

Anyway, here are three tables
Dentist table
dentist_id int identity (auto increment)

Patient table:
patient_id int identity (auto increment)

Appointment:
apointment_id int identity (auto increment)
id_dentist int FK to dentist_id
id_patient int FK to patient_id

I am gooling but cant find a way to make a trigger run only when the dentist_id from dentist table is updated. Also, is there a way to get the new id and old id somehow? I saw some posts with new.dentist_id and old.dentist_id but apparently is not for sql server.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-18 : 13:24:12

[code]CREATE TRIGGER YourTrigger
ON Dentist
AFTER UPDATE
AS

IF UPDATE(dentist_id)--checks if dentist_id was updated

--your code here
Also you can get old id as DELETED.dentist_id & new id as INSERTED.dentist_id

GO[/code]

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-05-20 : 05:15:03

I don't thik you you even can update identities.
Anyway - 99.999% of the time your PK should be unique and immutable. If you are changing it you probably have your data model a bit wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources