SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trigger giving random results with / & %
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davor
Starting Member

2 Posts

Posted - 01/18/2014 :  16:51:23  Show Profile  Reply with Quote
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)

Singapore
17584 Posts

Posted - 01/18/2014 :  18:48:27  Show Profile  Reply with Quote
can you show us your trigger ?


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/19/2014 :  02:51:35  Show Profile  Reply with Quote

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


PK is primary key of the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

davor
Starting Member

2 Posts

Posted - 01/19/2014 :  15:48:14  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/20/2014 :  06:59:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000