October 11, 2010 at 10:45 am
My sub select is giving me errors stating that it is returning more than 1 row. I am not sure how.
Someone help please....
The trigger is on my performanceobjectives table as follows
CREATE TABLE [dbo].[PERFORMANCEOBJECTIVES](
[PERFORMANCEOBJECTIVES_ID] [uniqueidentifier] NOT NULL,
[PERFORMANCEREVIEW_ID] [uniqueidentifier] NOT NULL,
[COMPLETED] [nvarchar](50) NULL,
[TOPIC] [nvarchar](50) NULL,
[PERFORMANCEOBJECTIVE] [nvarchar](1000) NULL,
[EVIDENCEHISTORY] [nvarchar](1000) NULL,
[PROGRESSOREVIDENCE] [nvarchar](1000) NULL,
[OBJECTIVENUMBER] [int] NULL
I am using the following code in my trigger to assign an OBJECTIVENUMBER based on the top 1 OBJECTIVENUMBER for the foreign key [PERFORMANCEREVIEW_ID]
if (
SELECT a.objectiveNumber
FROM PerformanceObjectives a
inner join inserted b on
a.performancereview_id = b.performancereview_id) is not null
Begin
update a
set a.objectiveNumber =
(select top (1) (c.objectiveNumber + 1)
from PerformanceObjectives c
inner join inserted d on
c.Performancereview_ID = d.Performancereview_ID
order by c.objectiveNumber desc)
from PerformanceObjectives a
inner join inserted b
on a.Performanceobjectives_ID = b.Performanceobjectives_ID
END
else
Begin
update a
set a.objectiveNumber = 1
from PerformanceObjectives a
inner join inserted b
on a.Performanceobjectives_ID = b.Performanceobjectives_ID
END
Cheers...Steve
October 11, 2010 at 10:51 am
The subquery in the If statement is probably the one that's blowing up.
It looks like you're trying to increment a number in a column. Why not use an Identity field and let SQL Server do the work for you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2010 at 10:53 am
On review, it also looks like your trigger will probably produce incorrect results if more than one row is inserted at a time. Can't be sure about that, since I don't know your business rules, but I would definitely check that out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2010 at 11:07 am
Ok thanks I will check that out. I did not know that the identity column could be based on a group of records with the same foreign keys. I thought an identity column would not be able to differentiate.
Take your point on the second post. Hopefully the identity column will negate my need for working out that problem.
Cheers...Steve
October 15, 2010 at 1:31 pm
I missed that you need to partition the number.
Is there a reason that the incrementing number needs to be stored in the database? Can you store a datetime or something of that sort, and use row_number() in queries to make it look like there's a number?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 1:48 am
I'm all ears
October 18, 2010 at 2:10 am
GSquared (10/15/2010)
I missed that you need to partition the number.Is there a reason that the incrementing number needs to be stored in the database? Can you store a datetime or something of that sort, and use row_number() in queries to make it look like there's a number?
sorry missed this bit.
the actual requirement has gone away now as the number is filled in manually, but if you are still intrested as I am, then I will carry on.
outline: Each year each of our employees has a review. As part of the review they will have several objectives. Each of these objectives needed to be automatically numbered. This has to be a sequential number 1 to however many objectives they had. The number simply takes the last objective with the highest number and adds 1 to it to get the next number.This is where I was trying to be clever.
Cheers...Steve
October 19, 2010 at 2:38 am
CELKO (10/18/2010)
CREATE TABLE PerformanceEvaluations(emp_id CHAR(10) NOT NULL
REFERENCES Personnel(emp_id),
objective_nbr INTEGER NOT NULL,
PRIMARY KEY (emp_id, objective_nbr),
objective_description VARCHAR(1000) NOT NULL,
objective_status CHAR(5) NOT NULL
CHECK (objective_status IN (..)),
vague_topic VARCHAR(50) NOT NULL,
progress_history VARCHAR(1000) NOT NULL);
Where is guy being evaluated? You had objectives which are completed, but by whom? Can I assume that objectives are individual and not drawn from a global list?
Why was "completed" fifty characters long? Isn't that one of many status codes, like: {"completed", 'failed", "working", etc.} What is a topic? The name is vague and the column is very wide. Why have several columns to hold what I guess are narrative descriptions.
Sequentially numbering rows or throwing meaningless GUIDs on them is not a good design. It tells you about a physical event in the physical storage and nothing about what you are modeling. Those are ways to mimic pointer chains and not write RDBMS.
Firstly many thanks for the long replys. I think I will try and give you a little outline first as some of this design is out of my control. This is all part of an HR piece of software that was designed to be used by HR staff to design the odd workflow, screen and report. For this reason the application has been locked down with regards to what you can and cannot do.
So to get what they want from the data we are having to try and manipulate the best we can. All the tables have to be developed through their console. So I have no control over the GUIDS and other aspects of the table structure.
Everything is linked back to the people table
People
------
People_ID uniqueidentifier
each person has a perfomancereview record for each year
PerformanceReview
------------------
PerformanceReview_ID uniqueidentifier
People_ID (FK)
Reviewee_ID (FK)
each Performance review has a number of objectives
PerformanceObjectives
----------------------
PerformanceObjectives_ID uniqueidentifier
PerformanceReview_ID (FK)
I will try and answer your questions.
Where is guy being evaluated? -In table PerformanceReview
You had objectives which are completed, but by whom? - the objectives are being reviewed by the reviewer in table Performance Review
Can I assume that objectives are individual and not drawn from a global list? - yes objectives are individual.
Why was "completed" fifty characters long? - this takes a value from what the system calls a 'Pick List' and cannot not be changed.
Sequentially numbering rows or throwing meaningless GUIDs on them is not a good design? - I guess this is an IT versus business. They felt that they needed a sequential number displayed so that they could identify each objective by number. God knows why they cannot identify the objective by the title......maybe I need to push a little harder.
I will check your second post and try and digest.
Cheers...Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply