"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 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!
>
>
>
>
>
>

"Alejandro Mesa" wrote:

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

"Alejandro Mesa" <AlejandroM***@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:
>
>> 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
>> > 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!
>> >
>> >
>> >
>> >
>> >
>> >