Cascade ac_InputChoices updates to ac_ProductTemplateFields

[nickc]

Finally got around to dealing with an old annoyance - when you make a change to the name or value of a product template choice item (element in a drop down or checkbox list), that change does not cascade to the products that have been assigned the product template, and the item being changed. Here's a trigger that will perform that cascade (run as SQL query against your database):

Code: Select all

/****** Object:  Trigger [dbo].[triggerInputChoices]    Script Date: 03/17/2009 09:21:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[triggerInputChoiceChanged] 
   ON [dbo].[ac_InputChoices]
   FOR UPDATE
AS 

DECLARE @before nvarchar(100)
DECLARE @after nvarchar(100)
DECLARE @fieldId int

IF UPDATE(ChoiceText)
BEGIN
   SET NOCOUNT ON;

	SELECT @before = (SELECT ChoiceText FROM DELETED)
	SELECT @after = (SELECT ChoiceText FROM INSERTED)
	SELECT @fieldId = (SELECT InputFieldId FROM INSERTED)

	UPDATE ac_ProductTemplateFields 
	SET ac_ProductTemplateFields.InputValue = @after
	WHERE ac_ProductTemplateFields.InputValue = @before
	AND ac_ProductTemplateFields.InputFieldId = @fieldId
END

IF UPDATE(ChoiceValue)
BEGIN
   SET NOCOUNT ON;

	SELECT @before = (SELECT ChoiceValue FROM DELETED)
	SELECT @after = (SELECT ChoiceValue FROM INSERTED)
	SELECT @fieldId = (SELECT InputFieldId FROM INSERTED)

	UPDATE ac_ProductTemplateFields 
	SET ac_ProductTemplateFields.InputValue = @after
	WHERE ac_ProductTemplateFields.InputValue = @before
	AND ac_ProductTemplateFields.InputFieldId = @fieldId
END

Note that this trigger does not deal with deleting a choice - but should provide enough detail for you to create your own separate "delete cascade" trigger if needed.