SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to write a delete trigger		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mirfath92
Starting Member

Sri Lanka
2 Posts
Posted - 11/12/2012 :  02:38:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/12/2012 :  06:45:49  Show Profile  Reply with Quote
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.
Go to Top of Page

mirfath92
Starting Member

Sri Lanka
2 Posts
Posted - 11/12/2012 :  23:19:05  Show Profile  Reply with Quote
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**
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/13/2012 :  06:44:15  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000