May 24, 2009 at 2:50 pm
so what would you suggest ? How to solve this problem, because I need these triggers...
May 24, 2009 at 4:38 pm
You might need to rewrite the trigger. That error cannot magically go away, you need to write sub-queries that do not return more than one value.
If you need help, you need to post code.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 24, 2009 at 5:10 pm
peter478 (5/24/2009)
so what would you suggest ? How to solve this problem, because I need these triggers...
You may replace badly written triggers with better written ones.
_____________
Code for TallyGenerator
May 25, 2009 at 2:04 am
Here are 2 triggers which are applied on table.
ALTER TRIGGER Cis_OP_No_Trigger1
ON dbo.Cis_OP_No
FOR INSERT, UPDATE
AS
IF (UPDATE (N_Vyrobok) or UPDATE (N_Kplan))
update Cis_OP_No set N_Vyrobok2 = (select ( i.N_Vyrobok + '/' +i.N_KPlan) from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID)
from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID
ALTER TRIGGER Cis_OP_No_Trigger2
ON dbo.Cis_OP_No
FOR INSERT, UPDATE
AS
IF UPDATE (N_Cas100ks)
update Cis_OP_No set N_KS_Hod = (select (((450/i.N_Cas100ks)*100)/8) from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID)
from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID
Table Cis_OP_No:
N_ID int
N_Cas100ks float
N_Vyrobok nvarchar
N_Kplan nvarchar
N_KS_Hod int
N_vyrobok2 nvarchar
N_rucne int
N_ID N_Cas100ks N_vyrobok N_kplan N_KS_Hod N_Vyrobok2 N_rucne
1----------5.73----------456000------001------982-------------456000/001---0
2----------5.73----------123000------001------982-------------123000/001---0
3----------7.71----------888000------003b-----730-------------888000/003b--0
ALTER Procedure dbo.UPdata
(
@e_Nplan char(20),
@e_Ncas char(20)
)
As
Update Cis_OP_NO
SET N_Cas100ks = convert(float,replace(@e_Ncas,',','.'))
Where N_Kplan = @e_Nplan
return
Error message:
Subquery returned more than 1 value. This is not premitted when the subquery follows =, =! , ....
Thank you
May 25, 2009 at 3:02 am
There is your problem. I bet you that when you run
select (((450/i.N_Cas100ks)*100)/8) from Cis_OP_No WHERE N_ID = @e_Nplan
you will get more than one row returned. As the error message tells you "This is not premitted when the subquery follows =, =! , ...."
As a matter of interest, why do you have two INSERT/UPDATE triggers, one for handling of updates to N_Vyrobok and one for N_Cas100ks? If I were you I would combine them into one.
May 25, 2009 at 4:10 am
First of all you don't need 2 triggers.
As well as correlated subqueries.
ALTER TRIGGER Cis_OP_No_Trigger
ON dbo.Cis_OP_No
FOR INSERT, UPDATE
AS
IF (UPDATE (N_Vyrobok) or UPDATE (N_Kplan))
update p
set N_Vyrobok2 = i.N_Vyrobok + '/' +i.N_KPlan
from Cis_OP_No p
inner join inserted i on p.N_ID = i.N_ID
IF UPDATE (N_Cas100ks)
update p
set N_KS_Hod = 450/i.N_Cas100ks*100/8
from Cis_OP_No p
inner join inserted i on p.N_ID = i.N_ID
GO
Second, you don't need triggers at all.
Use computed columns instead:
CREATE TABLE Table Cis_OP_No (
N_ID int,
N_Cas100ks float,
N_Vyrobok nvarchar,
N_Kplan nvarchar,
N_KS_Hod AS 450/N_Cas100ks*100/8 ,
N_vyrobok2 AS N_Vyrobok + '/' + N_KPlan,
N_rucne int
)
Ths way you'll always have you calculations done instantly and correctly.
P.S. Did not test the code, there may be some syntax errors.
If you find ones refer to BOL , topic "CREATE TABLE".
_____________
Code for TallyGenerator
May 25, 2009 at 5:39 am
May 25, 2009 at 5:43 am
Thank you, Sergiy I will try it, what does it mean BOL?
May 25, 2009 at 6:15 am
peter478 (5/25/2009)
what does it mean BOL?
Books On Line, "Help" for SQL Server, press F1 to open.
_____________
Code for TallyGenerator
May 27, 2009 at 3:25 pm
oh yes Sergiy, thank you , btw
the procedure works properly.
May 27, 2009 at 3:35 pm
peter478 (5/27/2009)
oh yes Sergiy, thank you , btwthe procedure works properly.
You're welcome.
Did you try computed columns?
Much less hassle.
To give it a try you don't need to delete existing ones, just add 2 and see if it has what you'd expect.
_____________
Code for TallyGenerator
May 28, 2009 at 1:24 pm
Not yet , but I will test it
June 1, 2009 at 2:50 am
Hi, I've just tried computed columns and it works as well.
yes, you are right it is much better.
Thank you.
June 1, 2009 at 9:05 am
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply