SqlServer 2008 Trigger

Hello Guys,

Does some one could help me please with this trigger.

if i set this line ( set @PickId = (select top 1 inserted.id from inserted) )  will not work.

But if i do  - ( set @PickId = 900 ) - Will only work when is hard code like this.

I had test already and there is a value on @PickId.

I'm i bit lost

Many thanks guys !

--------------------------------------------------

ALTER trigger [dbo].[ToTask_Insert]
on [dbo].[Picking]
for insert
as
declare @PickId int;
declare @Pick_Value decimal;
DECLARE @magento_order_id varchar(30);
               

set @magento_order_id = (select OH.magento_order_id from OrderHeader as OH where order_id = (select inserted.order_id from inserted))
set @PickId = (select top 1 inserted.id from inserted);
set @Pick_Value  = (select sum(totalsku)as Total from iWMS_Managment_Dashboard where id = cast(@PickId as int));
 
  insert into task(tasktype,
                  createdat,
                  doneat,
                  userid,
                  position,
                  targetid,
                  msg,
                  magentoOrderId,
                  PickValue
                  )
                   values
                        (1,
                         Getdate(),
                         null,
                         5,
                         0,
                         @PickId,
                          'Pick #: '+ cast(@PickId as varchar(20)) + '  -  Order #: ' + @magento_order_id,
                          @magento_order_id,
                          cast(@Pick_Value as decimal)
                       )
MortarelloAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
what about

select top 1 @PickId = inserted.id from inserted
MortarelloAuthor Commented:
Hi, Thanks ralmada

But my problem is the Pick_Value does not work when I'm passing dynamic value like the line below.

set @Pick_Value  = (select sum(totalsku)as Total from iWMS_Managment_Dashboard where id = cast(@PickId as int));


if I do like

set @Pick_Value  = (select sum(totalsku)as Total from iWMS_Managment_Dashboard where id = 100;

will work. I dont know why
Kyle AbrahamsSenior .Net DeveloperCommented:
Try this:
set @Pick_Value  = (select sum(isnull(totalsku,0))as Total from iWMS_Managment_Dashboard where id = @PickId );


Is the pickId inserted correctly?  Are there rows in the table for iWMS_Management?  Is this done part of a transaction?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

MortarelloAuthor Commented:
Hi ged325,

Tks to try to help me. The view ( iWMS_Managment_Dashboard ) is already doing this if the value is null they will replace by 0.

This is very strange cos, if I run ( select sum(totalsku)as Total from iWMS_Managment_Dashboard where id = 1490 ) will return a value.

I did many test and only works and I pass a hard code. I dont know why.

Tks guys
ralmadaCommented:
it looks like the problem is in the WHERE clause. @PickID will only receive a value if the INSERTED table has any row, otherwise it will be null, so maybe doing

set @Pick_Value  = (select sum(totalsku)as Total from iWMS_Managment_Dashboard where (id = cast(@PickId as int)) or @PickID is null);
Kyle AbrahamsSenior .Net DeveloperCommented:
Is the whole thing not working?  Or does something get inserted?  

What do you mean by "not work".  Do you get an error, or wrong value inserted?
ralmadaCommented:
and btw, no need to cast as int as it's already an int

set @Pick_Value  = (select sum(totalsku)as Total from iWMS_Managment_Dashboard where (id = @PickId) or @PickID is null);
SJCFL-AdminCommented:
I'd recommend clearing up the code a bit and being clear as to what you want it to do when the PickId is null. I was not exactly clear that it should be the same calculation.  then when its clearly defined, if it is still as not properly executing, i'd open a profiler session because i expect one of your variables isnt set exactly as you thought it was and it will be immediately apparent as soon as you see it in the profile session ;-)


Declare @order_Id int?
select top 1 @PickId = inserted.id from inserted;
select top 1 @order_id =  inserted.order_id from inserted;

set @magento_order_id = (select OH.magento_order_id from OrderHeader as OH where order_id = @order_id)
IF @PickId  is null
   DO WHAT ???
ELSE
set @Pick_Value  = (select sum(totalsku)as Total from iWMS_Managment_Dashboard where id = @PickId ));
END
Kyle AbrahamsSenior .Net DeveloperCommented:
This is a trigger . . . there should always be at least one id in inserted for this to fire unless i'm missing something.
ralmadaCommented:
If not, I'm thinking that you're missing an order by there. if you have multiple lines which one should be the first one?

set @PickId = (select top 1 inserted.id from inserted order by ....);

Or you can try without variables doing something like

ALTER trigger [dbo].[ToTask_Insert]
on [dbo].[Picking]
for insert
as
 
  insert into task(tasktype,
                  createdat,
                  doneat,
                  userid,
                  position,
                  targetid,
                  msg,
                  magentoOrderId,
                  PickValue
                  )
select 1, Getdate(), null, 5, 0, 
	'Pick #: '+ cast(a.id as varchar(20)) + '  -  Order #: ' + b.magento_order_id,
        b.magento_order_id,
        (select sum(totalsku) from  iWMS_Managment_Dashboard where id = a.id)
from inserted a
inner join OrderHeader b on a.order_id = b.order_id
	

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Mortarello,

Clearly you do not understand how TRIGGERs function.  The TRIGGER will fire once per statement and not once per row as you have it coded.  This is a fundemental error and I am glad to see that ralmada is addressing it.
MortarelloAuthor Commented:
Hi guys,

Thanks for the answers.

Hi  acperkins,  I was thinking to do some thing like this. Always when the picking table has a new record inserted, i will grab  the pickID and then get the value of the pick using view or sp to insert in another table called Task.

I will try do implement the ralmada code.

Tks guys
MortarelloAuthor Commented:
Hi Guys,

I'm just sending you a picture of the code. You can see clearly that the  @pickID has a value cos on msg field i've got ( Pick #: 1526  -  Order #: 200000093 ).

Many thanks
Untitled.png
ralmadaCommented:
I'm only seeing that you've not tried my suggestion. Can you please give it a try?
MortarelloAuthor Commented:
Hi ralmada, I'm gonna do it again carfully.

Tks
MortarelloAuthor Commented:
Hi ralmada, thank you very much man.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.