Trigger FOR DELETE Operation

when we want to create a trigger for delete operation on any table we can do it as shown below

and by useing "FROM deleted" as we did in our example ("SELECT stor_id FROM deleted") we can access the ID of record which just deleted and trigger got fired.

CREATE TABLE storesmaster(

stor_id char(4) NOT NULL,

stor_name varchar(40) NULL,

stor_address varchar(40) NULL,

city varchar(20) NULL,

)

GO

insert storesmaster values('1','B','567 Ave.','Tustin')

insert storesmaster values('2','N','577 St.', 'Los Gatos')

insert storesmaster values('3','T','679 St.', 'Portland')

insert storesmaster values('4','F','89 St.', 'Fremont')

GO

CREATE TABLE discounts(

discounttype varchar(40) NOT NULL,

stor_id char(4) NULL ,

lowqty smallint NULL,

highqty smallint NULL,

discount dec(4,2) NOT NULL

)

GO

insert discounts values('Initial Customer', NULL, NULL, NULL, 10.5)

insert discounts values('Volume Discount', NULL, 100, 1000, 6.7)

insert discounts values('Customer Discount', '8042', NULL, NULL, 5.0)

GO

CREATE TRIGGER myTrigger ON stores

FOR DELETE

AS

DECLARE @intRowCount int

SELECT @intRowCount = @@RowCount

IF @intRowCount > 0

BEGIN

DELETE sales

WHERE stor_id IN (SELECT stor_id FROM deleted)

DELETE discounts

WHERE stor_id IN (SELECT stor_id FROM deleted)

END

GO