How to get parent ID in child after delete trigger?

Question

Suppose I have tables:

Employee(empid, ....)
Phone(phoneid, ...) 
EmployeePhone(empid, phoneid, ...)

there is foreign key created and cascade deleting defined on foreign key relationship.

Now I delete a phone from phone table. I want to record the deletion in Delete trigger of table Phone.

But I can't get the empid in Phone After delete trigger like

select empid from deleted d join EmployeePhone e on d.phoneid = e.Phoneid

because record in EmployeePhone was deleted by cascade delete with the foreign key relationship.

How to got the empid in Phone after delete trigger?

Answer 1

Try adding another delete trigger on the EmployeePhone table - the data you require will be available to you there.