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,
    [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.

  • 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? 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. Jun 7 '12 at 12:43

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;
  • 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. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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