Solved

SqlServer 2008 Trigger

Posted on 2012-04-12
16
349 Views
Last Modified: 2012-04-13
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)
                       )
0
Comment
Question by:Mortarello
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +2
16 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 37838433
what about

select top 1 @PickId = inserted.id from inserted
0
 

Author Comment

by:Mortarello
ID: 37838529
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37838565
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?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:Mortarello
ID: 37838589
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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37838766
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);
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37838790
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?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37838828
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);
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37839017
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37839333
This is a trigger . . . there should always be at least one id in inserted for this to fire unless i'm missing something.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 37839543
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

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37840757
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.
0
 

Author Comment

by:Mortarello
ID: 37841558
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
0
 

Author Comment

by:Mortarello
ID: 37841692
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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37842150
I'm only seeing that you've not tried my suggestion. Can you please give it a try?
0
 

Author Comment

by:Mortarello
ID: 37842289
Hi ralmada, I'm gonna do it again carfully.

Tks
0
 

Author Comment

by:Mortarello
ID: 37843918
Hi ralmada, thank you very much man.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question