Solved

SqlServer 2008 Trigger

Posted on 2012-04-12
16
348 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
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.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
database level memory cache..? 8 46
SQL works but want to get the XML node data separately 11 56
SQL syntax question 6 72
ms sql + help with query 2 44
In this article I will describe the Copy Database Wizard 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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