locked
how to delete in two tables using foreign stored procedure RRS feed

  • 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

    Tuesday, February 14, 2012 1:08 AM

Answers

  • User1480927888 posted

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

    Then it will work.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 15, 2012 2:58 AM

All replies

  • User1492915735 posted

    Hi,

     

    Withdraw the answers.

    Tuesday, February 14, 2012 1:14 AM
  • 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

    Tuesday, February 14, 2012 1:15 AM
  • 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.

    Tuesday, February 14, 2012 1:20 AM
  • User1480927888 posted

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

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

    Thanks,

    Abhi

    Tuesday, February 14, 2012 1:20 AM
  • 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

    Tuesday, February 14, 2012 1:48 AM
  • 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
    Wednesday, February 15, 2012 1:29 AM
  • User1480927888 posted

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

    Then it will work.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 15, 2012 2:58 AM