A Trigger Question

hello there, i am trying to write a swl trigger for update in my database
application...
there are 2 tables like this:
Table1: Stock
Column1: StockCode
Column2: Stockquantity
Table2: Group
Column1: GroupCode
Column2: StockCode
Column3: StockQuantity
Column4: GroupQuantity
Column5: OrderAmount
now i wrote a sql trigger like this:
----
--
create trigger StockUpdate
on [Group]
for update
as
declare @.Code char(10)
declare @.SCode char(10)
declare @.sq decimal(9)
declare @.Ch decimal(9)
set @.Code = (select GroupCode from Inserted)
set @.SCode = (Select StockCode from Inserted)
set @.sq = (select StockQuantity from Inserted)
set @.ch = (select OrderAmount from Inserted)
begin
update Stock
set StockQuantity = STockQuantity - @.sq*@.ch
from Stock
where Stock.Stockcode = @.Scode
end
----
--
now ets say i have tables filles like this:
Table1: Stock
StockCode: StockQuantity:
Stock001 10000
Stock002 8908
Stock003 20000
Table2:
GroupCode: StockCode: StockQuantity:
GroupQuantity: OrderAmount:
Group001 Stock001 20
1 0
Group001 Stock002 20
1 0
Group001 Stock003 1
1 0
Group002 Stock001 5
2 0
now when i write this command:
update [Group] set OrderAmount = 2 where GroupCode = 'Group002'
The trigger will increase the GroupQuantity Value by 2;
and also decrease the StockQuantity by (2*5 = 10) so the stockquantity will
be 9990. this trigger works quite well if theres only
one 'Group002' in the second table. but i want this trigger to work for all
columns when i write the command:
update [Group] set OrderAmount = 2 where GroupCode = 'Group001'
i get this error:
"Server: Msg 512, Level 16, State 1, Procedure StockUpdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
..
so what can i do to make this trigger for when theres more then 1 querry
results'
THANK YOU SO MUCH FOR YOUR TIME ON READING AND THANKS FOR YOUR HELP
EFFOERTS...
have a nice day!Serdar C.,
A trigger is not fired by each row affected, instead it is fired by each dml
operation (insert, update, delete), so you have to take in mind that
multirows can be affected bby the operation. Supposing that column
[StockCode] is the pk of table [Stock], then try:
update Stock
set StockQuantity = STockQuantity - (select i.StockQuantity * i.OrderAmount
from inserted as i where i.Stockcode = stock.Stockcode)
where exists(select * from inserted as i where i.Stockcode = stock.Stockcode
)
go
AMB
"Serdar C." wrote:

> hello there, i am trying to write a swl trigger for update in my database
> application...
> there are 2 tables like this:
> Table1: Stock
> Column1: StockCode
> Column2: Stockquantity
> Table2: Group
> Column1: GroupCode
> Column2: StockCode
> Column3: StockQuantity
> Column4: GroupQuantity
> Column5: OrderAmount
> now i wrote a sql trigger like this:
> ----
--
> create trigger StockUpdate
> on [Group]
> for update
> as
> declare @.Code char(10)
> declare @.SCode char(10)
> declare @.sq decimal(9)
> declare @.Ch decimal(9)
> set @.Code = (select GroupCode from Inserted)
> set @.SCode = (Select StockCode from Inserted)
> set @.sq = (select StockQuantity from Inserted)
> set @.ch = (select OrderAmount from Inserted)
> begin
> update Stock
> set StockQuantity = STockQuantity - @.sq*@.ch
> from Stock
> where Stock.Stockcode = @.Scode
> end
> ----
--
>
>
> now ets say i have tables filles like this:
> Table1: Stock
> StockCode: StockQuantity:
> Stock001 10000
> Stock002 8908
> Stock003 20000
> Table2:
> GroupCode: StockCode: StockQuantity:
> GroupQuantity: OrderAmount:
>
> Group001 Stock001 20
> 1 0
> Group001 Stock002 20
> 1 0
> Group001 Stock003 1
> 1 0
> Group002 Stock001 5
> 2 0
>
> now when i write this command:
> update [Group] set OrderAmount = 2 where GroupCode = 'Group002'
> The trigger will increase the GroupQuantity Value by 2;
> and also decrease the StockQuantity by (2*5 = 10) so the stockquantity wil
l
> be 9990. this trigger works quite well if theres only
> one 'Group002' in the second table. but i want this trigger to work for al
l
> columns when i write the command:
> update [Group] set OrderAmount = 2 where GroupCode = 'Group001'
> i get this error:
> "Server: Msg 512, Level 16, State 1, Procedure StockUpdate, Line 12
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression
.
> The statement has been terminated."
> ...
> so what can i do to make this trigger for when theres more then 1 querry
> results'
>
> THANK YOU SO MUCH FOR YOUR TIME ON READING AND THANKS FOR YOUR HELP
> EFFOERTS...
> have a nice day!
>
>
>|||Correction,
Sorry, I am making same mistake. We have to use an aggregate function here.
update Stock
set StockQuantity = STockQuantity - (select sum(i.StockQuantity *
i.OrderAmount)
from inserted as i where i.Stockcode = stock.Stockcode)
where exists(select * from inserted as i where i.Stockcode = stock.Stockcode
)
go
AMB
"Alejandro Mesa" wrote:
> Serdar C.,
> A trigger is not fired by each row affected, instead it is fired by each d
ml
> operation (insert, update, delete), so you have to take in mind that
> multirows can be affected bby the operation. Supposing that column
> [StockCode] is the pk of table [Stock], then try:
> update Stock
> set StockQuantity = STockQuantity - (select i.StockQuantity * i.OrderAmou
nt
> from inserted as i where i.Stockcode = stock.Stockcode)
> where exists(select * from inserted as i where i.Stockcode = stock.Stockco
de)
> go
>
> AMB
> "Serdar C." wrote:
>|||Thanx so much... it is working now...
god bless you :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:563B331D-1FE1-43F5-B00C-2AA7E1EC9750@.microsoft.com...
> Correction,
> Sorry, I am making same mistake. We have to use an aggregate function
> here.
> update Stock
> set StockQuantity = STockQuantity - (select sum(i.StockQuantity *
> i.OrderAmount)
> from inserted as i where i.Stockcode = stock.Stockcode)
> where exists(select * from inserted as i where i.Stockcode =
> stock.Stockcode)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>