My trigger does not work correctly

[Jaheel22]

Hi guys

Please assist in case of a trigger in the follwing situation. I'm new to triggers. Thanks

Parent Table
=============

employeeId employeeName
---------- ------------
1 aaaaaaaa
2 bbbbbbbb


Link Table
============
employeeId empPointer
---------- ----------
1 5
2 5


Child Table
============
empPointer empCity
---------- -------
5 zzzzzz

employeeId in Parent table is primary key while it is foreign key in Link table. So Parent and Link are integrated/connected but Child table is out of loop.
Moreover, when a record is deleted from Parent table, its child record is deleted from Link table by cascading delete.
My requirement is that, when a employee aaaaaaa is deleted from Parent table, its depenedent record with employeeId 1 is also deleted from Link table due to
cascading delete, Here i want a trigger on Link table which will also delete a record from Child table if and only if employeeId 1 is the only record with empPointer
5 in Linke table which is not true in this case as employeeId 2 is also have empPointer 5. So in perfect situation, when employeeId 1 is deleted from Link table, then trigger should
not delete record in Child table with empPointer 5. But if employeeId 2 is deleted from Link table, trigger must delete record in Child table with empPointer 5 becasue
this time, there is only one employeeId 2 with empPointer 5.

Following trigger deletes record with empPointer 5 in Child table no matter employeeId 1 is the only record with empPointer 5 in Link table or there is another employeeId
with empPointer 5. Please assist as i'm new to triggers. Thanks


CREATE TRIGGER Delete_ChildRecords ON dbo.Link
FOR DELETE
AS
Delete Child
from Child, Link
where Child.empPointer = Link.empPointer AND
(Select count(empPointer) from Link where Child.empPointer = Link.empPoiner) = 1

 

[JarlH]

Your DBMS's implementation of triggers is not ANSI/ISO compliant. I suggest you try a vendor specific forum instead, I'm sure you can get help there.