Prev: Simple division is not returning actual value
Next: Recommendations Reporting Services 2008
From: Ben on 27 Apr 2010 13:33
Hi All,

I have a table A with a float column and a trigger to write to a log on
inserts to table A.
I am using the sql INSERTED table to get the value of the float column,
and insert it into a varchar column in the log (cant be changed, its
generic and used for multiple tables).
I am having a weird issue where the value in the log gets rounded to the
next integer in the audit table, it inserts correctly on Table A though.
Is this a known issue? Is there a workaround? If not, what am i doing
wrong?

Thanks in advance,
Ben


*** Sent via Developersdex http://www.developersdex.com ***
From: TheSQLGuru on 27 Apr 2010 13:49
Absolutely no way we can help you without seeing both table schemas and the
trigger code.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Ben" <nospam(a)devdex.com> wrote in message
news:ejeYG$i5KHA.1424(a)TK2MSFTNGP04.phx.gbl...
> Hi All,
>
> I have a table A with a float column and a trigger to write to a log on
> inserts to table A.
> I am using the sql INSERTED table to get the value of the float column,
> and insert it into a varchar column in the log (cant be changed, its
> generic and used for multiple tables).
> I am having a weird issue where the value in the log gets rounded to the
> next integer in the audit table, it inserts correctly on Table A though.
> Is this a known issue? Is there a workaround? If not, what am i doing
> wrong?
>
> Thanks in advance,
> Ben
>
>
> *** Sent via Developersdex http://www.developersdex.com ***


From: Ben on 27 Apr 2010 14:07


Table
CREATE TABLE [dbo].[limit_def](
[id] [int] NOT NULL,
[lower_limit] [float] NOT NULL,
[upper_limit] [float] NOT NULL,
[timestamp] [timestamp] NULL,
)

Trigger code

INSERT INTO log_table (
change ,
old_data ,
new_data ,
column_name ,
table_name ,
[key] )

SELECT
'INSERT' ,
NULL,
CAST(i.lower_limit AS VARCHAR(30)),
'lower_limit' ,
'[limit_def]' ,
i.id
FROM inserted i

INSERT INTO log_table (
change ,
old_data ,
new_data ,
column_name ,
table_name ,
[key] )

SELECT
'INSERT' ,
NULL,
CAST(i.upper_limit AS VARCHAR(30)),
'upper_limit' ,
'[limit_def]' ,
i.id
FROM inserted i


*** Sent via Developersdex http://www.developersdex.com ***
From: Ben on 27 Apr 2010 14:11


Table
CREATE TABLE [dbo].[limit_def](
[id] [int] NOT NULL,
[lower_limit] [float] NOT NULL,
[upper_limit] [float] NOT NULL,
[timestamp] [timestamp] NULL,
)

Trigger code

INSERT INTO log_table (
change ,
old_data ,
new_data ,
column_name ,
table_name ,
[key] )

SELECT
'INSERT' ,
NULL,
CAST(i.lower_limit AS VARCHAR(30)),
'lower_limit' ,
'[limit_def]' ,
i.id
FROM inserted i

INSERT INTO log_table (
change ,
old_data ,
new_data ,
column_name ,
table_name ,
[key] )

SELECT
'INSERT' ,
NULL,
CAST(i.upper_limit AS VARCHAR(30)),
'upper_limit' ,
'[limit_def]' ,
i.id
FROM inserted i


*** Sent via Developersdex http://www.developersdex.com ***
From: --CELKO-- on 27 Apr 2010 15:25
The whole nature of a FLOAT is that it is not exact. You get some
rounding errors when you convert to strings. This is why you have to
use the log files for audit trails.
 |  Next  |  Last
Pages: 1 2
Prev: Simple division is not returning actual value
Next: Recommendations Reporting Services 2008