Solved

SqlServer 2008 Trigger

Posted on 2012-04-12
16
336 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 39

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 39

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 39

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

759 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

19 Experts available now in Live!

Get 1:1 Help Now