Trigger giving random results with / & %

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 2008 Forums

Transact-SQL (2008)

Trigger giving random results with / & %

Author

Topic

davor
Starting Member

2 Posts

Posted - 2014-01-18 : 16:51:23

Have a table with 3 columns called Pallets,Boxes,Units and 10 units = 1 box, 20 boxes = 1 pallet.

What I'm trying to do is build a trigger which takes the "Units/10" add the whole number (if any) to Boxes whilst leaving the remainder in the Units with "Units%10" and then same for Boxes into Pallets.

Problem is I am not getting the right values appearing and I cant even figure out from the resulting numbers how its even arriving at those values

I've tried a variety of methods from declaring local variables and setting the values to the local vars before setting them back to the table columns after the division and modulus but that just results in nulls appearing.

So any suggestions how this should be done in a trigger.
eg before trigger

Pallets,Boxes,Units
0, 19, 25

Trigger should change column values to
Pallets,Boxes,Units
1, 1, 5

ie 25 units = 5 units + 2 boxes
19 boxes + 2 boxes = 1 box + 1 pallet.

I've tried over 20 different variations from around the web and my SQL books ranging from just using the table columsn to inserting an inner join to the inserted I table when the row is inserted, but no joy with any other them. I must be missing something really obvious but cant see what and having spent a week on this and getting no where, I'm beginning to wonder if this is a bug in MS SQL 2008r2.

So any suggestions?

TIA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-18 : 18:48:27

can you show us your trigger ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 02:51:35

[code]
CREATE TRIGGER TriggerName
ON Table
FOR INSERT,UPDATE
AS
BEGIN
UPDATE t
SET t.Pallets = i.Pallets + FLOOR((i.Boxes + (Units/10))/20),
t.Boxes = (i.Boxes + (i.Units/10))%20,
t.Units = i.Units % 10
FROM Table t
INNER JOIN INSERTED i
ON i.PK = t.PK
WHERE i.Units > 10
OR (i.Boxes + (i.Units/10)) > 20
END
[/code]

PK is primary key of the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

davor
Starting Member

2 Posts

Posted - 2014-01-19 : 15:48:14

The Floor did the trick, everything else is identical to my earliest attempt before I started getting more complex with declared local vars, etc etc.

I did try the trigger without the "Where i.Units > 10..." and it seems to work fine without it.

Is there a particular reason for the Where code or is it a defensive programming technique?

Either way Thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 06:59:18

quote:
Originally posted by davor

The Floor did the trick, everything else is identical to my earliest attempt before I started getting more complex with declared local vars, etc etc.

I did try the trigger without the "Where i.Units > 10..." and it seems to work fine without it.

Is there a particular reason for the Where code or is it a defensive programming technique?

Either way Thanks for your help!

It just restricts update to only records where reallocation needs to happen (ie either Boxes/units count exceeding the threshold). if you dispense with that it will simply fire update on lots of unwanted records ie counts already correct and needing no reallocation. Resultwise no issues in both cases.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources