how to delete in two tables using foreign stored procedure

Question

User-1458744719 posted

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

Answer 1

User1492915735 posted

Hi,

Smadhu

 

Withdraw the answers.

Answer 2

User1480927888 posted

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

Answer 3

User1663493495 posted

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.

Answer 4

User1480927888 posted

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

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

Thanks,

Abhi

Answer 5

User-1458744719 posted

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

Answer 6

User-1458744719 posted

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

Answer 7

User1480927888 posted

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

Then it will work.