How implement Delete Trigger in sql server

Question

I want write a delete trigger in sql actually i have two tables "products" and "productdetail" and using the primary key of products as a foreign key in productdetail. product detail can contain multiple records against single product object.

now i want to do like that if i deleted one records "says having primary key 1 " in product table then all other records which have foreign key "1" in productdetail table automatically deleted

I never use trigger before this. that's means unfamiliar with its syntax i searched about it and go through to different articles but unable to understand. please give me its complete solutions i.e. its complete syntax

In this case you should use delete cascade. You can see th answer here. http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server — Andrew Paes, May 19 '17 at 14:49
FWIW: I prefer to use stored procedures (SPs) for this sort of thing. The user doesn't need direct access to the tables, just to the SP. Additional business rules can be implemented. It separates the implementation into testable pieces and hides the details, e.g. if you decide to just set an `Obsolete` bit instead of deleting rows. A stupid mistake in an ad-hoc query won't cheerfully cascade unintentionally. ... — HABO, May 19 '17 at 20:42

score 3 · Accepted Answer · answered May 19 '17 at 14:55

3

In this case you should use delete cascade.

alter table ProductDetail 
add constraint fk_product foreign key (productID) 
references Product (productID) 
on delete cascade;

But if you really want to use trigger on delete so it is like this:

create trigger sampleTriggerName on dbo.Product
for delete
as
delete
from dbo.ProductDetail
where productID in (
        select deleted.id
        from deleted
        )
go

answered May 19 '17 at 14:55

Andrew Paes

1,940
1
15
20

Thanks for actually providing what he needs and what he asked for!! – Mardoxx May 20 '17 at 01:10
Just trying to be helpfull as possible. Tnx u too. – Andrew Paes May 20 '17 at 01:23

score 1 · Answer 2 · answered May 19 '17 at 14:57

Full working example:

IF OBJECT_ID('[dbo].[DataSourceParent]') IS NOT NULL
BEGIN;
    DROP TABLE [dbo].[DataSourceParent];
END;

CREATE TABLE [dbo].[DataSourceParent]
(
    [ParentID] TINYINT
);

GO

IF OBJECT_ID('[dbo].[DataSourceChild]') IS NOT NULL
BEGIN;
    DROP TABLE [dbo].[DataSourceChild];
END;

CREATE TABLE [dbo].[DataSourceChild]
(
    [ChildID] TINYINT
   ,[ParentID] TINYINT
);

GO


CREATE TRIGGER trg_DataSourceParent_A_D ON [dbo].[DataSourceParent] AFTER DELETE 
AS
BEGIN;

    DELETE [dbo].[DataSourceChild]
    FROM [dbo].[DataSourceChild] DC
    INNER JOIN deleted D
        ON DC.[ParentID] = D.[ParentID];

END;

GO

INSERT INTO [dbo].[DataSourceParent] ([ParentID])
VALUES (1)
      ,(2)
      ,(3);

INSERT INTO [dbo].[DataSourceChild] ([ParentID], [ChildID])
VALUES (1, 10)
      ,(2, 20)
      ,(2, 30)
      ,(3, 20);

DELETE FROM [dbo].[DataSourceParent]
WHERE [ParentID] = 2;

SELECT *
FROM [dbo].[DataSourceParent];

SELECT *
FROM [dbo].[DataSourceChild] ;

How implement Delete Trigger in sql server

2 Answers2