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 SP results into variable?

Is it possible to take the result from an SP that returns 1 value and
insert it into a variable?
DECLARE @.x varchar(9), @.sql1 nvarchar(200)
SELECT @.sql1=N'select @.x=count(*) from tt'
EXEC sp_executesql @.sql1, N'@.x varchar(9) OUTPUT', @.x output
Select @.x

Madhivanan|||<jw56578@.gmail.com> wrote in message
news:1111709552.442972.201630@.l41g2000cwc.googlegr oups.com...
> Is it possible to take the result from an SP that returns 1 value and
> insert it into a variable?

If a procedure returns one value, then you should use an output variable:

create proc dbo.p_out
@.i int output
as
set @.i = 1
go

declare @.num int
exec dbo.p_out @.i = @.num output
select @.num

This is usually the best approach for procedures which return only one row
of data:

http://www.sommarskog.se/share_data.html#OUTPUT

Simon