How to write a delete trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to write a delete trigger

Author

Topic

mirfath92
Starting Member

2 Posts

Posted - 2012-11-12 : 02:38:35

Hi,
I have two tables.
1. Customer Table
2. Order Table
The two tables are linked by a foreign key in the Order table (Customer ID which is the primary key in the Customer Table).
I have a c# application interface where i can delete orders.
I want to know how to write a trigger that can delete the child table records(Order Table) without the parent table(Customer Table) getting affected. Or is it possible to delete through a Delete Cascade command?

Thanks in advance

**Mirfath**

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 06:45:49

Deleting records in Order table should not cause anything to be deleted or changed in the Customers table.

If you have other requirements - for example, if you want to delete a Customer when ALL the orders associated with that customer are deleted - then you would need to do something more.

Casade deletes are done when you want to go the other way - i.e., when you delete a customer if you want all the orders associated with that customer to be deleted, then you would set up cascade delete.

mirfath92
Starting Member

2 Posts

Posted - 2012-11-12 : 23:19:05

Thanks for the reply. So what is the best way to do it?? Is it by writing a trigger? if so hw should i write it? i want to delete the orders made by the customer not the customer.

**Mirfath**

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 06:44:15

You don't need a trigger to do that. You can create a stored procedure like shown below, or even use the delete statement. I am making assumptions about the table names, column names etc. Change as appropriate:

CREATE PROCEDURE dbo.DeleteCustomerOrders
	@CustomerID INT 
AS
	SET NOCOUNT ON;
	
	DELETE FROM dbo.Orders
	WHERE CustomerId = @CustomerID;
	
	SET NOCOUNT OFF;
Go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources