My Trigger does not work (sql server 2000)

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 2000 Forums

SQL Server Development (2000)

My Trigger does not work (sql server 2000)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-17 : 13:23:28

Mack writes "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"

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-17 : 13:51:04

OK, that hurt to read....and I'm now just emerging from the fog...

Sounds like you have more issues with design rather than having to worry about the TRIGGER

Can you show us some DML examples and what the before and after images of the tables should look like?

Brett

8-)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-17 : 16:31:06

create trigger dLink on dbo.Link
for delete
as
begin
delete dbo.Child
where empPointer in (
select d.empPointer
from Deleted d
where not exists (select * from dbo.Link l where l.empPointer = d.empPointer)
)
end
go

HTH

=================================================================

Happy Holidays!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources