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

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
EdarnAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
sachinpatil10dCommented:
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
 
EdarnAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.