Multirow Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Multirow Triggers

Author

Topic

sqlhorror
Starting Member

5 Posts

Posted - 2007-03-23 : 05:19:57

Hi,

I am a sql near novice and have a problem in a project I am working on. The exact details of the tables would be too confusing to explain here so I'll use a hypothetical case,

I have a table with 4 columns reccid (int,pk), apples (int),pears(int),oranges (int), lemons (int), totfruit (int), totcitrus(int)

I have a trigger that whenever I update apples,pears,oranges or lemons columns the totfruit and totcitrus are updated with the totals.
---------------------
after update
as
BEGIN
SET NOCOUNT ON;
update fruit set
totcitrus=lemons + oranges,
totfruit=apples+pears+lemons+oranges
END
---------------------

This works fine for single row updates, but if I run a stored procedure against the table such as;

update fruit set lemons=lemons*2

Then I ger an error message of Subquery returned more than 1 value.

I know I havent written the trigger correctly for multirow updates but dont understand enough to see what I have to do, any advice would be very welcome, also can anyone recommend a suitable book.

Thanks a lot.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 05:24:54

Your trigger need to handle multiple rows.

refer to these to learn more on trigger
http://www.sqlteam.com/item.asp?ItemID=3850

If your requirement is as what you describe here, you don't really required trigger if you want to update totfruit, totcitrus column.
You can use computed column for totfruit and totciturs

sqlhorror
Starting Member

5 Posts

Posted - 2007-03-23 : 05:32:19

Thanks, but I have already read parts I and II of the article.

There are other issues that mean I cannot use computed columns.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 05:35:23

then in that case, use an INSERT / UPDATE TRIGGER

sqlhorror
Starting Member

5 Posts

Posted - 2007-03-23 : 05:49:52

Which is the point to my question, thanks anyway.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 06:00:57

[code]
update u
set totcitrus = lemons + oranges,
totfruit = apples + pears + lemons + oranges
from fruits u inner join inserted i
on u.reccid = i.reccid
[/code]
Any possibility of nulls in the data ? If yes, use ISNULL(colname, 0)

sqlhorror
Starting Member

5 Posts

Posted - 2007-03-23 : 06:48:19

Perfect, simple and elegant.

Many thanks.

Kristen
Test

22859 Posts

Posted - 2007-03-23 : 06:55:41

"Then I ger an error message of Subquery returned more than 1 value."

I don't reckon your "hypothetical case" will do that, so you either need to post the actual code you are using, or make a full worked example that "breaks"

Folk here will appreciate having a CREATE TABLE, some INSERT statements for test data, the code for the Trigger, and then the statement that causes the error.

"I know I havent written the trigger correctly for multirow updates"

Again, your example looks fine. Except that it is updating the whole table, rather than just the records which are effecting the trigger. See khtan's example for how to only update the appropriate records.

Kristen

sqlhorror
Starting Member

5 Posts

Posted - 2007-03-23 : 08:38:28

I think the problem I had was that I was declaring variables in my trigger and assigning values , once I adopted KH's plan and took out the declarations (which werent do anything anyway) it worked fine.

The actual trigger is around 120 lines long, and the tables have circa 40 columns but point taken about steps to recreate and thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources