March 9, 2010 at 4:44 am
I have this table MAD_MOV that will have all my warehouse movements of my products.
In addition to this table, I have a table MAD_SAL that will have, for each product code and location, its stock.
So let's see what I will have
If I enter 100 units of my product XXX to location LOC001, I will put on MAD_MOV this information:
codigo=XXX
local=LOC001
qttin=100
and the trigger should do this to MOV_SAL
if I already have a record for the combination of codigo+local, I must do an UPDATE don the field STOCK of this table, adding the amount of this movement to the stock that I already have.
If there is no record for this combination, an INSERT must be made.
If I make single INSERTS, the trigger works ok, but I get <b>Subquery returned more than 1 value</b> if I make a sql statement liike this:
insert into mad_mov (cm, cmdesc, codigo, ref, design, local, qttin, qttout, data)
select top 10 cm, cmdesc, case when st.codigo='' then st.ref else st.codigo end, st.ref, st.design, lote,
(case when cm<50 then qtt else 0 end), (case when cm>50 then qtt else 0 end), datalc
from sl join st on st.ref=sl.ref
This is my trigger:
<b>
alter trigger WDBP_MADMOV_in on MAD_MOV
with ENCRYPTION after INSERT AS
begin tran
declare @temregisto numeric(12,0)
set @temregisto=(select count(*) from MAD_SAL where MAD_SAL.codigo = (select inserted.codigo from inserted)
and MAD_SAL.local= (select inserted.local from inserted))
if @temregisto=0
begin
insert into MAD_SAL (MSStamp, ref, design, local, codigo, stock)
select newid(), ref, design, local, codigo, qttin-qttout from inserted
end
if @temregisto>0
begin
update MAD_SAL set stock=stock+(select qttin-qttout from inserted) where MAD_SAL.codigo=(select codigo from inserted)
and MAD_SAL.local=(select local from inserted)
end
commit tran
</b>
How can I get a INSERT with a SELECT with a lot of values to work properly (One line of the SELECT should deploy my trigger once at a time)
Thanks
March 9, 2010 at 8:34 pm
When a trigger fires, it is already in a transaction. So you don't need to BEGIN TRANSACTION or the COMMIT.
The INSERTED view contains a record for every record that was inserted or updated by the transaction that caused the trigger to fire. So, if 2 or more records get inserted or updated, your problem occurs. To get around this, you need to treat INSERTED and, in this case, MAD_CAL as two tables that you join together (on the primary key of MAD_CAL) to produce the desired inserts and updates.
In my code, the update comes first. If the INSERT was first, the update would attempt to change records that the trigger inserted which may not be desirable
Try the following for your trigger
alter trigger WDBP_MADMOV_in on MAD_MOV
with ENCRYPTION after INSERT AS
update MAD_SAL
set stock= stock + I.qttin-I.qttout
FROM MAD_SAL
INNER JOIN inserted I
ON MAD_SAL.codigo=I.codigo
and MAD_SAL.local= I.local
insert into MAD_SAL (MSStamp, ref, design, local, codigo, stock)
select newid(), ref, design, local, codigo, qttin-qttout
from inserted I
WHERE NOT EXISTS (SELECT * FROM MAD_SAL MS
WHERE MS.Local = I.Local
AND MS.codigo = I.codigo)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply