Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My table structure is below:

CREATE TABLE [ACC].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [SalesCompanyFinancialPeriodID] [int] NOT NULL,
    [DocumentTypeID] [int] NULL,
    [Number] [int] NULL,
    [Title] [nvarchar](200) NULL,
    [ConfirmStatusEnumID] [int] NULL,
    [SignedPrice] [money] NOT NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED 
(
    [DocumentID] ASC
)

I want to set Number column value automaticaly increment number started from 1 for each SalesCompanyFinancialPeriodID column. In other word if new record inserted in this table, value of Number column calculated in instead of trigger, such as below:

 ISNULL( (Select Max(A.Number) 
          From Acc.Document A
          Where A.SalesCompanyFinancialPeriodID = Inserted.SalesCompanyFinancialPeriodID),1)

What is the best practices for this problem.

share|improve this question
1  
Why instead of trigger? –  dezso Jun 7 '12 at 6:22
2  
@dezso Why does everyone think instead of triggers are evil? Are you suggesting an after trigger is better? Do you think it's better to insert a row and then update the value, rather than just insert the intact row once? Do you think it's better to perform business logic and validation after you've inserted the row, so if validation fails you have to do the work twice (insert and then rollback) instead of zero times? –  Aaron Bertrand Jun 7 '12 at 6:24
    
@AaronBertrand - No, no and no. I use AFTER triggers only for modifyling other tables based on the row in question. The problem is apparently in my box so to say - INSTEAD OF triggers can be defined only on views in Oracle an PostgreSQL, and there are BEFORE triggers for doing things like the OP wants to accomplish. So +1 for your comment, because I was unaware of this difference between RDBMSes. –  dezso Jun 7 '12 at 7:31
1  
@dezso there are no BEFORE triggers in SQL Server. We've been asking for them for a long time (especially for DDL) but sadly they're not buying in. –  Aaron Bertrand Jun 7 '12 at 12:43
add comment

1 Answer

Why do you need to store this value in the Number column? Much better to derive this information at query time than to bother storing redundant information and constantly have to update it as rows are added, changed or deleted.

SELECT DocumentID, SalesCompanyFinancialPeriodID, 
  Number = ROW_NUMBER() OVER (PARTITION BY SalesCompanyFinancialPeriodID
    ORDER BY DocumentID)
FROM ACC.Document;
share|improve this answer
    
Sometimes, a number put on a document is a reference number that users refer to, it is printed on the hardcopy, for instance a should never change –  Alireza Jun 7 '12 at 7:15
    
@Alireza but a number that users refer to that is only valid in combination with SalesCompanyFinancialPeriodID (since 1 will be re-used for every distinct SalesCompanyFinancialPeriodID)? Seems DocumentID would make more sense for the purpose you describe... that's unique, at least, and by definition will not change. –  Aaron Bertrand Jun 7 '12 at 12:43
1  
Consider a software being used by Accountants of multiple companies, in multiple financial years, they expect their Number field to increase sequentially without interrupt in their own domain, not to jump from 1 to 17. Also, it is usually advised not to use the primary key in any interaction with the user, and while I can't explain why, my experiences in software development/architecture strongly verify this. –  Alireza Jun 9 '12 at 6:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.