asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sachinpatil10d

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

Edarn

ASKER

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