Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Basically I am expanding an existing system that has every template as its own template, however there is becoming a lot of overlap (same template exists in multiple categories and has a new record for each) and I am going to introduce a Master / Child system hopefully without having to redo much if any of their existing.

The client doesn't want to change how the database is working, but he doesn't want to have to update the same template 8 times for each category, he wants to just update the master once and have it cascade down. (I did not develop the first database)

I added a column MasterID to the table, and plan on having the MasterID reference the template_id of the master or itself if it is the master.

My real question is when it comes to updating a child and cascading the updates, would it be best to use a stored procedure that finds all records with that same MasterID and updates them?

table {
   template_id uniqueidentifier primary_key,
   name nvarchar(256) not null,
   email nvarchar(256) not null,
   text nvarchar(MAX) not null,
   masterID unqiueidentifier
}

Lets say I have something simple like that,I am thinking about doing a stored procedure I can call from my C# that goes through and updates all the values that have that same masterID?

USE [Template]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Jacob Steele
-- Create date: 11/18/2013
-- Description: Cascading updates for 
-- =============================================
ALTER PROCEDURE [dbo].[UpdateTemplateValues]
@name nvarchar(256),
@email nvarchar(256),
@text nvarchar(256),
@masterID uniqueidentifier,

AS
BEGIN
SET NOCOUNT ON

UPDATE [dbo].[Templates]
SET
name = @name,
email = @email,
text = @text
WHERE masterID = @masterID

END
GO

Is that the proper way to do a cascading update in this case? The masters masterID will equal itself.

Would this be the proper way to go about this in this case?

share|improve this question
    
The key here is how you define your Referential Integrity Constraints. TO do this, you can follow any of the below approach : a. Create FK with ON UPDATE CASCADE, b.Create trigger on UPADATE –  Kin Nov 18 '13 at 23:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.