trigger - logical problem - 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

trigger - logical problem

Author

Topic

BigCharlie
Starting Member

2 Posts

Posted - 2009-03-03 : 15:10:05

Hi all,

I'm a newbie to SQL server (2005 Express edition) and I'm trying to get used to triggers. I've come to this problem. I've three tables:

company
address
company_address

First stores company details, second address details, the last one relations between companies and addresses. One company could have 0..n addresses. So far so good.

Now the problem:
- on company_address there is cascade constraint (deleted company = deleted relations with addresses)
- I would like to write trigger, that deletes address, when there are no other relations to this address

I was thinking that I could wrote "for delete" trigger on company_address. Works perfectly, when there is only one company with one address. But then I realized that there could be more than one address -> more records in virtual deleted table. And I evidently couldn't handle that with my poor knowledge. So how to wrote that trigger?

Thanks for good ideas!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-03 : 15:25:02