Trigger for two column recursively

[cureben]

Hi all,
I am beginner on SQL and I am stuck with trigger.
I'm trying to create a Trigger for two columns (KG ,Items)
Basicly if column KG is inserted/updated then compute column Items. If column Items is inserted/updated then compute column KG.

It is kind of converter.I have tried as below:

--------------------------------------------------------
CREATE TRIGGER [dbo].[UpdateItemsOrKg] 
   ON  [dbo].[UpdateItemsOrKg] 
  AFTER INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
 
DECLARE @Factor decimal(8,3) 
 
SELECT @Factor=Factor FROM dbo.CurrentFactors
 
IF UPDATE(Items) 
BEGIN
    UPDATE [dbo].[UpdateItemsOrKg] 
    SET [KG] = @items*@Factors
END
 
IF UPDATE(KG)
BEGIN
    UPDATE [dbo].[UpdateItemsOrKg] 
    SET [Items] = @KG/@Factors
END
--------------------------------------

Is no problem when colums is updated (procedure from excel spreadsheet), updated is only one column KG or Items, but when is new record then updated column has a null value and another computed column is calculate absolutely correctly.

Thanks for any advice.

[Alex Papadimoulis]

There is a way to do this, but it's not pretty.

Actually, what you're doing isn't very pretty either. You'd probably be best computing this in intermediary code (stored procedure, applicationc code, etc), since it's a pretty strange feature of a table.

That said, there is a function called COLUMNS_UPDATED which will indicate which column was updated. If you use it in conjunction with COLUMNPROPERTY, you can tell which columns on your table were updated.

[NeoPa]

Have you considered whether the update within the trigger routine would trigger itself?

You would want to avoid an interminable loop I would suggest.

[Alex Papadimoulis]

Good point, NeoPa. To add to my previous reply...

An AFTER UPDATE trigger on table that updates the same table (a "direct recursion") will not, by default, trigger itself. However, if you were to set the database option RECURSIVE_TRIGGERS (I don't recommended it), then a direct recusion could occur, allowing for the trigger to trigger itself. This would then cause a stack overflow error, or as SQL Server calls it, "exceeding maximum nesting level."

Of course, if RECURSIVE_TRIGGERS is set, then you could simply do an INSTEAD OF UPDATE trigger, as INSTEAD OF triggers cannot cause direct recursion. If you go with an INSTEAD OF trigger, you will need to update all columns yourself.

[NeoPa]

It sounds like less of a problem than I'd imagined actually. Excuse my relative inexperience with SQL Server. I'm just getting back into it again since getting MCP in 2000 years ago (I mention that merely to illustrate how little an MCP can know - especially after a lapse of a few years).

[cureben]

Alex I agree with you, it is not pretty what I've tried to do.
It seems that both the best and fastest way (for me) it will be computing this in application code.
I just thought that maybe in SQL is feature which I don't know yet. As I have mentioned I am beginner( I started play with SQL 2 months ago). Unfortunately I have to finish project quickly so I have no time to check COLUMNS_UPDATED, but it seems that it should be a good option.If it comes to "avoid an interminable loop" I think it would be stop by
if @@Nestlevel>1
RETURN

Thanks for all advice.