MS SQL Server - Using triggers for hirechical data

Author

Using triggers for hirechical data

ayende@gmail.com

2005-10-30, 1:23 pm

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].[RuleId] =
@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?

ayende@gmail.com

2005-10-30, 1:23 pm

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

Hugo Kornelis

2005-10-30, 8:23 pm

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)

Sponsored Links