how to delete in two tables using foreign stored procedure RSS

• • Reply
  

  Smadhu

  Member

  509 Points

  980 Posts

  how to delete in two tables using foreign stored procedure

  Feb 14, 2012 05:08 AM|LINK

  i have 1 table resource columns are id,title,dimension,image,zoomimage,catogary

  other table resourcecolor columns are id,resourceid,color

  so i want to write delete stored procedure to delete all coloumns in resource table based on id

  at the same time the color associated with dat id in resoucecolor table shuold also be delete ,so how to use foreign key 

  plz od help me

  below code i m using but its nt able to delete color row in resourcecolor

  USE [Harlequin]
  GO
  /****** Object: StoredProcedure [dbo].[DELETERESOURCES] Script Date: 02/14/2012 10:04:10 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[DELETERESOURCES]
  
  (
  @Id nvarchar (Max)
  )
  AS
  BEGIN
  SET NOCOUNT ON

  DELETE Resources

  WHERE Id=@Id
  
  END

  • Edited by Smadhu on Feb 15, 2012 04:14 AM
• • Reply
  

  thaicarrot

  Contributor

  5120 Points

  1459 Posts

  Re: how to delete in two tables using foreign stored procedure

  Feb 14, 2012 05:14 AM|LINK

  Hi,

  Smadhu

  Smadhu

   

  Withdraw the answers.

  Best Regards
  Weera
  
  
  • Edited by thaicarrot on Feb 14, 2012 06:36 AM
  • Edited by thaicarrot on Feb 14, 2012 06:36 AM
  • Edited by thaicarrot on Feb 15, 2012 04:14 AM
• • Reply
  

  abhijeetmish...

  Member

  272 Points

  95 Posts

  Re: how to delete in two tables using foreign stored procedure

  Feb 14, 2012 05:15 AM|LINK

  if yor are working on Oracle then while creating foreign key constraint .... wirte this :

  CONSTRAINT fk_res_color
  	FOREIGN KEY (id)
  	REFERENCES resource(id)
  	ON DELETE CASCADE

   This way when u delete data from the parent table .... corresponding data from child table will be automatically deleted.

  Thanks,

  Abhi

  • Edited by abhijeetmishra on Feb 15, 2012 04:14 AM
• • Reply
  

  Alig

  Member

  114 Points

  27 Posts

  Re: how to delete in two tables using foreign stored procedure

  Feb 14, 2012 05:20 AM|LINK

  Resources Table Cloumns ---> id,title,dimension,image,zoomimage,catogary

  ResourceColor columns ---> id,resourceid,color. Here resourceid is your foreign key.

  You can do something like this.

  Delete From ResourceColor Where resourceid = @Id

  Delete From Resources Where id = @Id.

  • Edited by alig on Feb 14, 2012 05:20 AM
  • Edited by alig on Feb 15, 2012 04:14 AM
• • Reply
  

  abhijeetmish...

  Member

  272 Points

  95 Posts

  Re: how to delete in two tables using foreign stored procedure

  Feb 14, 2012 05:20 AM|LINK

  just to have some more idea ... go through below link ...

  http://msdn.microsoft.com/en-us/library/ms186973.aspx

  Thanks,

  Abhi

  • Edited by abhijeetmishra on Feb 15, 2012 04:14 AM
• • Reply
  

  Smadhu

  Member

  509 Points

  980 Posts

  Re: how to delete in two tables using foreign stored procedure

  Feb 14, 2012 05:48 AM|LINK

  thnx 

  i m using sql server can u please help me with the code as i tired to use as per suggestion but its not working

  • Edited by Smadhu on Feb 15, 2012 04:14 AM
• • Reply
  

  Smadhu

  Member

  509 Points

  980 Posts

  Re: how to delete in two tables using foreign stored procedure

  Feb 15, 2012 05:29 AM|LINK

  sorry i tired that but its not working

  USE [Harlequin]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[DELETERESOURCES]
  	
  	(
  	@Id nvarchar (Max)
  	)
  AS
  	BEGIN
  	SET NOCOUNT ON
  
  DELETE Resources WHERE Id=@Id
  DELETE ResourcesColor WHERE Resourcesid=@Id

• • Reply
  

  abhijeetmish...

  Member

  272 Points

  95 Posts

  Re: how to delete in two tables using foreign stored procedure

  Feb 15, 2012 06:58 AM|LINK

  you need to alter the table with foriegn key constraint and apply the cascade delete property.

  Then it will work.

  • Marked as answer by Ming Xu - MSFT on Feb 28, 2012 12:49 PM