Solved

SqlServer 2008 Trigger

Posted on 2012-04-12
16
339 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
  • 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
 

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
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.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now