triggers

[Wally]

I have two tables (STOCK_ITEM and SEASONAL):

STOCK_ITEM
Barcode
Description
Price
Quantity_on_Order
Quantity_in_Stock

SEASONAL
Barcode
Season
Start_Date
End_Date

I want to use a trigger to delete one row out of SEASONAL when one row out
of STOCK_ITEM is deleted. The primary keys of the two rows that are to be
deleted must be the same.

Can you please tell me what is the code for the trigger?

Thanks,

Wally.

 

[Garth Wells]

I have two tables (STOCK_ITEM and SEASONAL):...<<


How about using a Cascading Delete...


-- Code
CREATE TABLE StockItems
(
Barcode varchar(20) PRIMARY KEY,
Description varchar(20) NOT NULL,
Price smallmoney NOT NULL,
Quantity_on_Order smallint NULL,
Quantity_in_Stock smallint NOT NULL
);

INSERT StockItems VALUES ('123456789','cool product',11.95,1,10);

CREATE TABLE Seasons
(
SeasonID smallint PRIMARY KEY,
Barcode varchar(20) FOREIGN KEY REFERENCES StockItems(Barcode) ON DELETE
CASCADE,
Season varchar(10) NOT NULL,
Start_Date smalldatetime NOT NULL,
End_Date smalldatetime NOT NULL,
);

INSERT Seasons VALUES (1,'123456789','summer','06/23/06','09/22/06');

DELETE StockItems WHERE Barcode = '123456789';

SELECT COUNT(*) FROM Seasons;

-- results --

0
--- End of Code


Garth
www.SQLBook.com