 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Peet Koekemoer
Guest
|
 Posted: Mon Apr 05, 2004 6:44 am Post subject: Please Help with MSSQL 2000 Update Trigger |
 |
|
Hi all,
This is my first app with triggers on a database. I'm using MSSQL2000 and D7
and trying to write an update trigger. The trigger must update the Status
field when the HoursCompl field is updated. Please find bellow the trigger I
wrote that gives me the following error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
CREATE Trigger SetStatus
On Quotes
For Update As
begin
IF ((Select HoursCompl from Quotes) = 0)
Begin
Update Quotes Set Status = 'N'
end
IF ((Select HoursCompl from Quotes) > 0)
Begin
Update Quotes Set Status = 'B'
end
IF ((Select HoursCompl from Quotes) =
(Select HrsQuoted from Quotes))
Begin
Update Quotes Set Status = 'C'
end
end
Can someone please help me as the SQL help file is not to great on this
trigger
Thanx,
Peet
|
|
| Back to top |
|
 |
Scott Duncan
Guest
|
| Posted: Mon Apr 05, 2004 7:02 am Post subject: Re: Please Help with MSSQL 2000 Update Trigger |
|
|
"B" and "C" will both be true if "C"
"Peet Koekemoer" <peetmce (AT) telkomsa (DOT) net> wrote
| Quote: | Hi all,
This is my first app with triggers on a database. I'm using MSSQL2000 and
D7
and trying to write an update trigger. The trigger must update the Status
field when the HoursCompl field is updated. Please find bellow the trigger
I
wrote that gives me the following error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
CREATE Trigger SetStatus
On Quotes
For Update As
begin
IF ((Select HoursCompl from Quotes) = 0)
Begin
Update Quotes Set Status = 'N'
end
IF ((Select HoursCompl from Quotes) > 0)
Begin
Update Quotes Set Status = 'B'
end
IF ((Select HoursCompl from Quotes) =
(Select HrsQuoted from Quotes))
Begin
Update Quotes Set Status = 'C'
end
end
Can someone please help me as the SQL help file is not to great on this
trigger
Thanx,
Peet
|
|
|
| Back to top |
|
|
Peet Koekemoer
Guest
|
| Posted: Mon Apr 05, 2004 7:15 am Post subject: Re: Please Help with MSSQL 2000 Update Trigger |
|
|
Thanx,
But that does not solve the problem I still get the problem even if I only
try to execute "A" only.
"Scott Duncan" <accman (AT) tampabay (DOT) rr.com> wrote
| Quote: | "B" and "C" will both be true if "C"
"Peet Koekemoer" <peetmce (AT) telkomsa (DOT) net> wrote in message
news:40710058 (AT) newsgroups (DOT) borland.com...
Hi all,
This is my first app with triggers on a database. I'm using MSSQL2000
and
D7
and trying to write an update trigger. The trigger must update the
Status
field when the HoursCompl field is updated. Please find bellow the
trigger
I
wrote that gives me the following error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an
expression.
CREATE Trigger SetStatus
On Quotes
For Update As
begin
IF ((Select HoursCompl from Quotes) = 0)
Begin
Update Quotes Set Status = 'N'
end
IF ((Select HoursCompl from Quotes) > 0)
Begin
Update Quotes Set Status = 'B'
end
IF ((Select HoursCompl from Quotes) =
(Select HrsQuoted from Quotes))
Begin
Update Quotes Set Status = 'C'
end
end
Can someone please help me as the SQL help file is not to great on this
trigger
Thanx,
Peet
|
|
|
| Back to top |
|
|
Mike Walsh
Guest
|
| Posted: Mon Apr 05, 2004 1:05 pm Post subject: Re: Please Help with MSSQL 2000 Update Trigger |
|
|
| Quote: | IF ((Select HoursCompl from Quotes) = 0)
(Select HrsQuoted from Quotes))
Begin
Update Quotes Set Status = 'C'
end
end
|
Peet,
I believe that your problem is with understanding what the trigger is doing.
When a trigger fires, it can look at more than just the records that have
been changed. Your problem is that your are not really looking at the whole
table.
IF ((Select HoursCompl from Quotes) = 0
This statement will try to return multiple records in the DB. You will
get the aforementioned error. Take this statement and execute it in the QA
program. Change the update statements to be print instead. You'll see what I
mean.
However, your trigger also has the effect that if the IF statments worked,
they would arbitrarily change all of the records in the DB. That is after
the recursion error was resolved. If the IF statement had worked, this would
have modified Quotes, which would have called this trigger again, which
would have modified Quotes, which would have called the trigger ad
infinitum.
To get around these you could try something like this
update inserted set
Status =
case
when (HoursCompl = 0) then 'N'
when (HoursCompl > 0) then 'B'
when (HoursCompl = HrsQuoted) then 'C'
else '?'
This is untested, and I'm not sure that it handles all possible values.
The inserted and deleted tables are there to tell you which records are
affected by your INSERT, UPDATE or DELETE statement. Also you need to keep
in mind that a trigger needs to be able to handle the fact that these
statements may affect more than one row.
Mike Walsh
|
|
| Back to top |
|
|
Peet Koekemoer
Guest
|
| Posted: Mon Apr 05, 2004 2:16 pm Post subject: Re: Please Help with MSSQL 2000 Update Trigger |
|
|
Mike,
Thanx for the help so far, but now I get the following error when updating
one record:
Server: Msg 217, Level 16, State 1, Procedure TR_Quotes_Set_Status, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded
(limit 32).
This is my new trigger:
Create Trigger TR_Quotes_Set_Status
On Quotes
after Insert, update as
update Quotes set
Status =
case
when (Quotes.HoursCompl = 0) then 'N'
when (Quotes.HoursCompl > 0) then 'B'
when (Quotes.HoursCompl = Quotes.Hours) then 'C'
end
from Inserted where Inserted.RefNo = Quotes.RefNo
Peet
"Mike Walsh" <techs.msllib.com (AT) verizon (DOT) net> wrote
| Quote: | IF ((Select HoursCompl from Quotes) = 0)
(Select HrsQuoted from Quotes))
Begin
Update Quotes Set Status = 'C'
end
end
Peet,
I believe that your problem is with understanding what the trigger is
doing.
When a trigger fires, it can look at more than just the records that have
been changed. Your problem is that your are not really looking at the
whole
table.
IF ((Select HoursCompl from Quotes) = 0
This statement will try to return multiple records in the DB. You will
get the aforementioned error. Take this statement and execute it in the QA
program. Change the update statements to be print instead. You'll see what
I
mean.
However, your trigger also has the effect that if the IF statments worked,
they would arbitrarily change all of the records in the DB. That is after
the recursion error was resolved. If the IF statement had worked, this
would
have modified Quotes, which would have called this trigger again, which
would have modified Quotes, which would have called the trigger ad
infinitum.
To get around these you could try something like this
update inserted set
Status =
case
when (HoursCompl = 0) then 'N'
when (HoursCompl > 0) then 'B'
when (HoursCompl = HrsQuoted) then 'C'
else '?'
This is untested, and I'm not sure that it handles all possible values.
The inserted and deleted tables are there to tell you which records are
affected by your INSERT, UPDATE or DELETE statement. Also you need to keep
in mind that a trigger needs to be able to handle the fact that these
statements may affect more than one row.
Mike Walsh
|
|
|
| Back to top |
|
|
Peet Koekemoer
Guest
|
| Posted: Mon Apr 05, 2004 3:09 pm Post subject: Re: Please Help with MSSQL 2000 Update Trigger |
|
|
Thanx everyone for the help. I fixed the problem. I found out there was
another update trigger on the same table and thereofore I got the nested
level error. I combined the two triggers into one and it works 100%
Peet.
"Peet Koekemoer" <peetmce (AT) telkomsa (DOT) net> wrote
| Quote: | Mike,
Thanx for the help so far, but now I get the following error when updating
one record:
Server: Msg 217, Level 16, State 1, Procedure TR_Quotes_Set_Status, Line 5
Maximum stored procedure, function, trigger, or view nesting level
exceeded
(limit 32).
This is my new trigger:
Create Trigger TR_Quotes_Set_Status
On Quotes
after Insert, update as
update Quotes set
Status =
case
when (Quotes.HoursCompl = 0) then 'N'
when (Quotes.HoursCompl > 0) then 'B'
when (Quotes.HoursCompl = Quotes.Hours) then 'C'
end
from Inserted where Inserted.RefNo = Quotes.RefNo
Peet
"Mike Walsh" <techs.msllib.com (AT) verizon (DOT) net> wrote in message
news:407159c1$1 (AT) newsgroups (DOT) borland.com...
IF ((Select HoursCompl from Quotes) = 0)
(Select HrsQuoted from Quotes))
Begin
Update Quotes Set Status = 'C'
end
end
Peet,
I believe that your problem is with understanding what the trigger is
doing.
When a trigger fires, it can look at more than just the records that
have
been changed. Your problem is that your are not really looking at the
whole
table.
IF ((Select HoursCompl from Quotes) = 0
This statement will try to return multiple records in the DB. You
will
get the aforementioned error. Take this statement and execute it in the
QA
program. Change the update statements to be print instead. You'll see
what
I
mean.
However, your trigger also has the effect that if the IF statments
worked,
they would arbitrarily change all of the records in the DB. That is
after
the recursion error was resolved. If the IF statement had worked, this
would
have modified Quotes, which would have called this trigger again, which
would have modified Quotes, which would have called the trigger ad
infinitum.
To get around these you could try something like this
update inserted set
Status =
case
when (HoursCompl = 0) then 'N'
when (HoursCompl > 0) then 'B'
when (HoursCompl = HrsQuoted) then 'C'
else '?'
This is untested, and I'm not sure that it handles all possible values.
The inserted and deleted tables are there to tell you which records are
affected by your INSERT, UPDATE or DELETE statement. Also you need to
keep
in mind that a trigger needs to be able to handle the fact that these
statements may affect more than one row.
Mike Walsh
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
