473,588 Members | 2,448 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trigger for two column recursively

3 New Member
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 1812
Alex Papadimoulis
26 Recognized Expert New Member
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,566 Recognized Expert Moderator MVP
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 Recognized Expert New Member
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_TRIGG ERS (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_TRIGG ERS 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,566 Recognized Expert Moderator MVP
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
cureben
3 New Member
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

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

Similar topics

6
3441
by: Dave C. | last post by:
Hello, I have created the following trigger: CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE AS DECLARE @foobar varchar(100) SELECT @foobar= foobar FROM inserted IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')
6
7134
by: Mary | last post by:
We are developing a DB2 V7 z/OS application which uses a "trigger" table containing numerous triggers - each of which is activated by an UPDATE to a different column of this "trigger" table. When the triggers are fired, various other operations are performed on other tables in the database. The triggers are not created on these other tables because other programs perform updates to these tables and we do not want the triggers to fire...
18
5961
by: Bill Smith | last post by:
The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query against a different table (that uses the LIKE predicate) but cannot get around the SQL0132 error . I have tried the hex notation after the LIKE such as (without the quotes)... " where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " ,...
0
2467
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as I've found it educational. Note: - I build this for use in a JDEdwards OneWorld environment. I'm not sure how generic others find it but it should be fairly generic. - I use a C stored procedure GETJOBNAME to get some extra audit data,
5
3288
by: Bob Stearns | last post by:
I have two (actually many) dates in a table I want to validate on insertion. The following works in the case of only one WHEN clause but fails with two (or more), with the (improper? inappropriate?) error message: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space> which is interpreted as: An unexpected token "CREATE TRIGGER IS3.date_later_001i NO C" was found
5
3950
by: Peter Erickson | last post by:
I am running postgresql 7.4.2 and having problems creating a trigger function properly. I keep getting the following error: ERROR: OLD used in query that is not in rule I have a table called journal_entries with a foreign key to a table called journals. When a entry is added to journal_entries, I am trying to get it to update the 'mtime' field of the corresponding entry in the journals table.
3
3717
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code in the application, but I'd rather not! DDL for table and trigger below. TIA
2
4962
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is still not working correctly. Here is my code: create trigger emp_update_id BEFORE update on emp_update REFERENCING NEW AS N for each row SET unique_id = Generate_unique();
9
9301
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate this - by iterating through the collection of tables and passing the tablename to something that...
0
2081
Brad Orders
by: Brad Orders | last post by:
Hi all Here is my situation: When table A is updated, I need to record some data in table B, then apply the update to table A Normally I would use a FOR UPDATE trigger, but the table has a TEXT field in it. Therefore, I must use an INSTEAD OF trigger. Columns may be added to this table without my knowledge or control, and without warning.
0
7929
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8228
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8357
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7987
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
6634
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5729
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3887
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2372
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.