Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I delete an occurences of a key in another table IF the key that is beeing deleted is the last one?

Posted on 2011-10-20
3
Medium Priority
?
212 Views
Last Modified: 2012-05-12
Hi!

I have a question regarding SQL triggers. I am using MS SQL.

I have two tabels.
TableA consists of two fields
table_B_Id and table_A_uniqe_Id (Primary key)

TableB consists of two similar fields:
name and table_B_unique_Id (Primary key)

Open in new window


Table A contains several rows that references the same TableB row. So the relation is: TableA(n) <---->TableB(1)

Now I would like to create a DELETE trigger on TableA which will do:
-After a row is deleted from TableA it checks in TableA if there are any other rows that has the same table_B as the one that was deleted. And if so it deletes the row in Table B which has [Deleted Row] in TableA.Table_B_Id == TableB. table_B_unique_Id

CREATE TRIGGER Trigger1
ON TableA
AFTER DELETE
AS
	IF EXISTS (SELECT * FROM TableA WHERE deleted.table_B_Id  == table_B_Id)
	BEGIN
	  DELETE FROM TableB WHERE deleted.table_B_Id == table_b_unique_Id
	END

Open in new window


but this does not work at all :-)

any help is highly apprechiated.

Kind regards
Thomas
0
Comment
Question by:Edarn
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36999837
this should work:
CREATE TRIGGER Trigger1
ON TableA
AFTER DELETE
AS
    -- delete from tBombCase, aliased b
  DELETE b 
    FROM tBombCase b 
    -- join on DELETED table to match on the caseRevisionId value that are deleted
    JOIN deleted d 
      ON d.caseRevisionId = b.caseRevisionId
      -- but delete ONLY if there are no remaining rows in table A for that caseRevisionId 
      AND NOT EXISTS( SELECT NULL FROM TableA a WHERE a.caseRevisionId = d.caseRevisionId  )

Open in new window

0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36999841
CREATE TRIGGER Trigger1
ON TableA
AFTER DELETE
AS
	if (select COUNT(*) from TableA inner join deleted on tablea.table_A_uniqe_Id = deleted.table_A_uniqe_Id where tablea.table_B_Id = deleted.table_B_Id) = 0 
		delete from TableB 
			where table_B_unique_Id in (select deleted.table_B_Id from deleted)

Open in new window

0
 

Author Closing Comment

by:Edarn
ID: 37000239
A few minutes after adding my question I realized that I copy pasted too much in my non working trigger written in the question above. So I modifyed it but then angel had already answered. that´s why the table names is not TableA and TableB.

How ever the answer worked perfectly and I will now save lots of time.

Many Thanks
Thomas
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question