Using triggers for hirechical data

I've a table that looks like this:

CREATE TABLE [Rules] (
[RuleId] [int] IDENTITY (1, 1) NOT NULL ,
// lots more fields
[Severity] byte,
[TemplateRuleId] [int] NULL ,
CONSTRAINT [PK_Rules] PRIMARY KEY  CLUSTERED
(
[RuleId]
)  ON [PRIMARY] ,
CONSTRAINT [FK_Rules_Rules] FOREIGN KEY
(
[TemplateRuleId]
) REFERENCES [Rules] (
[RuleId]
)[RuleTypeId]
)
) ON [PRIMARY]

Some of the rules are templates, and some are based on templates, if I
update a template rule, I want all the rules based on it to be updated
as well. If a non-template rule is updated, I want it template rule id
to be nulled.

I thought to use a trigger to do this, and I tried to do this:

CREATE TRIGGER  TR_Update_RuleTempla
te_On_Rule_Update
ON [Rules]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @UpdatedRuleId int,
@UpdatedSeverity tinyint,
 @UpdatedTemplateRule
Id int

SELECT @UpdatedRuleId = [RuleId],
@UpdatedSeverity = [Severity],
 @UpdatedTemplateRule
Id = [TemplateRuleId]
FROM [Inserted]

IF  (@UpdatedTemplateRul
eId IS NULL) -- is a template rule
BEGIN
UPDATE [Rules] SET
[Severity] = @UpdatedSeverity
WHERE [TemplateRuleId] =  @UpdatedTemplateRule
Id
END
ELSE
UPDATE [RULES] SET [TemplateRuleId] = NULL WHERE [Rules].[Ru
leId] =
@UpdatedRuleId
SET NOCOUNT OFF
go

This is the first time that I'm writing a trigger, and I'm pretty sure
that I made some big mistakes with it.
First, is that the recommended style regarding first selecting from the
inserted and then using it?
Second, I can't make the first part of the IF work, any ideas?

Ignore the second part, I'd a typo in the update statement.

On 30 Oct 2005 09:51:18 -0800, ayende@gmail.com wrote:

 (snip)
>This is the first time that I'm writing a trigger, and I'm pretty sure
>that I made some big mistakes with it.
>First, is that the recommended style regarding first selecting from the
>inserted and then using it?

Hi ayende,

No. A trigger will fire once PER STATEMENT, not once per row affected.
So your trigger should handle an update that affects 1 row just as well
as it does an update that affects 0 or 10,000 rows. That means that you
can't select from inserted into variables (unless you want to throw
performance and scalability overboard and use a trigger to iterate
through all the rows in inserted).

Here's a quick shot at what your trigger should look like:

CREATE TRIGGER  TR_Update_RuleTempla
te_On_Rule_Update
ON [Rules]
FOR UPDATE
AS
SET NOCOUNT ON
-- Nullify template rule id for updated non-template rows
UPDATE   Rules
SET      TemplateRuleId = NULL
WHERE    EXISTS
(SELECT *
FROM   inserted AS i
WHERE  i.RuleId = Rules.RuleId
AND    i.TemplateRuleId IS NOT NULL)
-- Propagate changes in template rows to rowsa based on them
UPDATE   Rules
SET      Severity =
(SELECT Severity
FROM   inserted AS i
WHERE  i.RuleID = Rules.TemplateRuleID)
WHERE    EXISTS
(SELECT *
FROM   inserted AS i
WHERE  i.RuleId = Rules.RuleId
AND    i.TemplateRuleId IS NULL)

PS: The above assumes that you have disabled recursive trigger
execution.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)