469,307 Members | 1,823 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Trigger for two column recursively

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:
Expand|Select|Wrap|Line Numbers
  1. --------------------------------------------------------
  2. CREATE TRIGGER [dbo].[UpdateItemsOrKg] 
  3.    ON  [dbo].[UpdateItemsOrKg] 
  4.   AFTER INSERT,UPDATE
  5. AS 
  6. BEGIN
  7.     SET NOCOUNT ON;
  8.  
  9. DECLARE @Factor decimal(8,3) 
  10.  
  11. SELECT @Factor=Factor FROM dbo.CurrentFactors
  12.  
  13. IF UPDATE(Items) 
  14. BEGIN
  15.     UPDATE [dbo].[UpdateItemsOrKg] 
  16.     SET [KG] = @items*@Factors
  17. END
  18.  
  19. IF UPDATE(KG)
  20. BEGIN
  21.     UPDATE [dbo].[UpdateItemsOrKg] 
  22.     SET [Items] = @KG/@Factors
  23. END
  24. --------------------------------------
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.
Aug 10 '10 #1
5 1709
Alex Papadimoulis
26 Expert
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.
Aug 10 '10 #2
NeoPa
32,173 Expert Mod 16PB
Have you considered whether the update within the trigger routine would trigger itself?

You would want to avoid an interminable loop I would suggest.
Aug 11 '10 #3
Alex Papadimoulis
26 Expert
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.
Aug 11 '10 #4
NeoPa
32,173 Expert Mod 16PB
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).
Aug 11 '10 #5
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.
Aug 11 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

18 posts views Thread by Bill Smith | last post: by
reply views Thread by JohnO | last post: by
5 posts views Thread by Bob Stearns | last post: by
5 posts views Thread by Peter Erickson | last post: by
2 posts views Thread by mob1012 via DBMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.