Insert trigger not working...

can someone shed some light on this? i want this to work on update and
insert, but it only works on update. When i run a simple insert
statement, i get this
<root>The specified statement did not generate any data</root>
Here is the trigger:
CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
FOR UPDATE, INSERT
--This trigger is used to export an xml file with system_qty, part_name
AS
DECLARE @.trans_date datetime,
@.part_name varchar(50),
@.Q varchar (255),
@.file varchar (255)
--IF NOT UPDATE(system_qty)
--RETURN
SELECT @.trans_date =trans_date FROM Inserted
SELECT @.part_name = part_name From Inserted
SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
@.query = @.Q,
@.templatefile = 'c:\scripts\template1.tpl'On 8 May 2006 15:26:56 -0700, lytung@.gmail.com wrote:

>can someone shed some light on this? i want this to work on update and
>insert, but it only works on update. When i run a simple insert
>statement, i get this
> <root>The specified statement did not generate any data</root>
Hi lytung,
Not a definitive answer, but I have some comments below:

>Here is the trigger:
> CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
>FOR UPDATE, INSERT
(snip)

>SELECT @.trans_date =trans_date FROM Inserted
>SELECT @.part_name = part_name From Inserted
This can give unexpected results for multi-row updates or multi-row
inserts. Since the trigger is fired only once per statement execution,
the inserted pseudo-table will hold several rows. You might well get
@.trans_date from one row and @.part_name from another. Plus, you probably
want to execute the trigger's logic for all affected rows.

>SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
>as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
>+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Use style 126 for the converstion of @.trans_date. Style 120 is not
guaranteed to be unambiguous in all locale settings.

>Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
>@.query = @.Q,
>@.templatefile = 'c:\scripts\template1.tpl'
Add a PRINT @.Q statement before, after or instead of this statement for
debugging purposes. Or insert @.Q into a table, if you are testing from a
front-end that doesn't expose the output of PRINT. Check if the SQL that
is generated matches your expecations.
Hugo Kornelis, SQL Server MVP

Insert Question.

Hello. I am extending an existing proc and encountering a strange issue. I
hope someone can shed some light on this. Here's my problem :
insert into : table with decimal(14,2) columns ( where you see math )
sql statement :
TRUNCATE TABLE dbo.tbl_met_wos
INSERT INTO dbo.tbl_met_wos
SELECT
b.TopDivision as Division,
b.TopDivisionName as DivisionName,
b.Div as Department,
b.DivName as DepartmentName,
b.Dept as Class,
b.DeptName as ClassName,
a.fponhandTW,
a.fponhandMTD,
a.fponhandTQ,
a.fponhandTS,
a.fponhandTY,
c.mdonhandTW,
c.mdonhandMTD,
c.mdonhandTQ,
c.mdonhandTS, --STD
c.mdonhandTY,
d.FPSalesTW,
d.FPSalesMTD,
d.FPSalesTQ,
d.FPSalesSTD,
d.FPSalesTY,
e.MDSalesTW,
e.MDSalesMTD,
e.MDSalesTQ,
e.MDSalesSTD,
e.MDSalesTY,
--RH Added.
--cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in (0,null)
then 1 else d.FPSalesTW end) AS decimal(14,2)),
'1.25',
Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
then 1 else e.MDSalesTW end) AS varchar),
(a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
else d.FPSalesMTD end,
(c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
else e.MDSalesMTD end,
Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
(c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
else e.MDSalesTQ end,
(a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
else d.FPSalesSTD end,
--(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then 1
else e.MDSalesSTD end,
'1.37',
(a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
else d.FPSalesTY end,
(c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
else e.MDSalesTY end
FROM #HIERARCHY b
LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
a.Department
LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
c.Department
LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department =
d.Department
LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
e.Department
ORDER BY
b.Div,
b.Dept
as you can see, this is not the hottest proc in the world, but support sucks
sometimes :) anyhow, when this insert statement runs, It will round off to
whole numbers where you see math in the fields. Where you see '1.25' and
'1.37' they insert fine as a decimal(14,2) when you select from the table. I
have tryed converting and casting the entire field like :
Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
then 1 else e.MDSalesTQ end AS Decimal(14,2)
but it still rounds off to a whole number. if it were trying to insert
100.65 I get 101.00 when I select from the table.
Any IDEAS? I'm really with this one.
Robert H"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
>
...

> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
>
The sum or product of two decimals requires more precision than the
individual operands. As the required precision approaches 36, scale is
trimmed. So after a few operations your results quickly become converted to
decimal(36,0) unless you are diligent about converting intermediate results
to remove unneeded precision.
If you are sticking with decimal(14,2), just convert each intermediate
result to decimal(16,4), or something, to preserve your scale.
See:
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||Robert H wrote:
> Hello. I am extending an existing proc and encountering a strange
> issue. I hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW
> in(0,null) then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null)
> then 1 else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null)
> then 1 else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ
> in(0,null) then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then
> 1 else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null)
> then 1 else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null)
> then 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then
> 1 else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then
> 1 else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND
> b.Department = a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND
> b.Department = c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND
> b.Department = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND
> b.Department = e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but
> support sucks sometimes :) anyhow, when this insert statement runs,
> It will round off to whole numbers where you see math in the fields.
> Where you see '1.25' and '1.37' they insert fine as a decimal(14,2)
> when you select from the table. I have tryed converting and casting
> the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ
> in(0,null) then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
Don't use integer type values in the calculation unless you want an
integer result. Make sure all integer values are first converted/cast to
decimal (14, 2) first (you can do this inline). If it's a hard-coded
value, then use "0.00" instead of "0".
Also, the TRUNCATE TABLE operation can only be run by system
administrators, table owner, db_owner, and db_ddladmin. So you won't be
able to grant execute on the procedure to users without those rights.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks Guys ! I now know a little more about sql precision! The solution
to the problem based on what you guys have said is to convert
each field to a real data type. This solved all problems. that truncate
table was allready in the proc, and was in production so Someone gave it the
proper access.
Thanks!
Robert H
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
> then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
> else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
> else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
> then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
> else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
> else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then
> 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
> else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
> else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
> a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
> c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department
> = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
> e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but support
> sucks sometimes :) anyhow, when this insert statement runs, It will round
> off to whole numbers where you see math in the fields. Where you see
> '1.25' and '1.37' they insert fine as a decimal(14,2) when you select from
> the table. I have tryed converting and casting the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
> then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
>
>|||Robert H wrote:
> Thanks Guys ! I now know a little more about sql precision! The
> solution to the problem based on what you guys have said is to convert
> each field to a real data type. This solved all problems. that
> truncate table was allready in the proc, and was in production so
> Someone gave it the proper access.
No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eySIpGYrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Robert H wrote:
> No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
>
Actually, using floating point in a calculation is a very reasonable thing
to to. It frees you from having to manually adjust the precision and scale
after each operation. At the end you can round off and convert back to a
decimal type, knowing that th accumulation of error in the calculation was
minimized by the floating point arithmetic.
David