Create Trigger to delete

I have 2 tables:

Tbl_Recipes :    recipes_ID, recipes_title, recipes_catID, recipes_proc
and
Tbl_Recipes_Cats :  catID, cat_desc

There is a relationship b/w Tbl_Recipes.recipes_catID   and  Tbl_recipes_cats.catID

I want to set a trigger that will execute when a record in Tbl_recipes is DELETED.
The trigger will delete the corresponding CatID record from Tbl_Recipes_Cats if there are no more records referenceing the deleted catID.
LVL 1
bjv211Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dqmqConnect With a Mentor Commented:
Slight syntax error, let me try again
Create trigger YourTrigger on tbl_recipes
After Delete
AS
Delete from tbl_recipes_cats 
from tbl_recipes_cats c  
inner join deleted on c.catID =  deleted.recipes_catID
where not exists (select 1 from tbl_recipes r where
r.recipes_catID = deleted.recipes_catID)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why no directly use the foreign key to do that for you, without trigger?


ALTER TABLE Tbl_Recipes_Cats
ADD CONSTRAINT fk_catID
FOREIGN KEY (recipes_catID) 
REFERENCES Tbl_Recipes_Cats (catID) ON DELETE CASCADE 

Open in new window

0
 
bjv211Author Commented:
where in that statement does it reference the Tbl_recipes table or is it understood?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry:
ALTER TABLE Tbl_Recipes
ADD CONSTRAINT fk_catID
FOREIGN KEY (recipes_catID) 
REFERENCES Tbl_Recipes_Cats (catID) ON DELETE CASCADE 

Open in new window

0
 
bjv211Author Commented:
Ok, I'm not sure what's going on...

I executed your sql with no errors, however, when i delete a recipe record, if it is the last recipe referencing that CatID, it does not delete the Cat record from Tbl_recipes_cats
0
 
dqmqCommented:
Try this:
Create trigger YourTrigger on tbl_recipes
After Delete
AS
Delete from tbl_recipes_cats c 
inner join deleted on c.catID =  deleted.recipes_catID
where not exists (select x from tbl_recipes r where
r.recipes_catID = deleted.recipes_catID)
 

Open in new window

0
 
dqmqCommented:
Note: cascade delete is not the correct solution for your requirements--you indeed need a trigger.
0
 
bjv211Author Commented:
I'm getting the error:  incorrect syntax near 'c'.
0
 
bjv211Author Commented:
Works great! thanks for the quick replies
0
All Courses

From novice to tech pro — start learning today.