1

I have 2 tables:

Table Product

ProductID     ProductName      CategoryName    Price
102           keyboard         PC parts        800
103           mouse            PC parts        900

Table Category

CategoryID   CategoryName
981          PC parts

If I delete a category, How do I would like to automatically delete the products under it using trigger...

John Woo
  • 258,903
  • 69
  • 498
  • 492
mitche027
  • 123
  • 1
  • 2
  • 11

2 Answers2

3

Actually you may not use Trigger here, the only way you do create a FOREIGN KEY constraint that has DELETE CASCADE flag set

CREATE TABLE Category
(
    CategoryID   INT,
    Categoryname VARCHAR(50),
    CONSTRAINT tb_pk PRIMARY KEY (CategoryID),
    CONSTRAINT tb_uq UNIQUE (Categoryname)
)
GO

CREATE TABLE Product
(
    ProductID    INT,
    ProductName  VARCHAR(50)  ,  
    CategoryID   INT ,
    Price        DECIMAL(10,2),
    CONSTRAINT p_pk PRIMARY KEY (ProductID),
    CONSTRAINT p_uq UNIQUE (ProductName),
    CONSTRAINT p_fk FOREIGN KEY (CategoryID)
         REFERENCES Category(CategoryID) ON DELETE CASCADE
)
GO

when you try to delete some Category on the Category table, it automatically deletes its child rows since table Product is dependent on table Category

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • And why would a trigger be forbidden here? If he creates it as an instead of delete trigger, you won't run into the FK violation and can do the original delete at the end, no? – Ben Thul Nov 12 '12 at 13:36
  • @BenThul yep good point, but i not fan of triggers [*see here*](http://stackoverflow.com/a/460343/491243) :D – John Woo Nov 14 '12 at 05:28
0

You can use on delete cascade to delete data from both tables.

Hanson Fong
  • 9
  • 1
  • 2