locked
Dynamic default value of a table RRS feed

  • Question

  • Hi, 

    In table TableA which has primary key ( PersonNo, Serial ).
    I would like to set the default value of the column Serial as
    when the same PersonNo insert into the TableA, the Serial will be Serial +1.
    such as:
    A. ('John'), [1];  ('John'),[2];  ('Mary'),[1]
    B. ('Jojo'), [1];  ('John'),[3]; ('Mary'),[2]; ('Tom'),[1]

    How do I do that?
    Thanks for help.

    Jason

    Thursday, December 17, 2015 7:05 AM

Answers

  • Hi JasonHuang8888,

    I don't think there's a straightforward way to do that. Neither do I think there would be an elegant workaround. However you can ignore the serial when inserting and get a sequence serial when querying. Please see below sample.

    CREATE TABLE TABLEA( PersonNo VARCHAR(10), Serial INT);
    
    INSERT INTO TABLEA(PersonNo) VALUES('John'),('John'),('Many'),('Jojo'),('John'),('Mary'),('Tom');
    GO
     
    SELECT PersonNo,ROW_NUMBER() OVER(PARTITION BY PersonNo ORDER BY (SELECT 1)) Serial FROM TABLEA
    
     

    If you have any question, feel free to let me know.




    Eric Zhang
    TechNet Community Support

    • Marked as answer by JasonHuang8888 Monday, December 21, 2015 12:55 AM
    Thursday, December 17, 2015 12:09 PM
  • One way would be to use an INSTEAD OF trigger:

    CREATE TRIGGER tbl_tri INSTEAD OF INSERT AS

    INSERT tbl(PersonNo, Serial)
       SELECT PersonNo,
              CASE WHEN Serial IS NOT NULL THEN Serial                ELSE row_number() OVER (PARTITION BY PersonNo
                                           ORDER BY (SELECT 1)) +
                        (SELECT MAX(tbl.Serial)
                         FROM   tbl
                         WHERE  tbl.PersonNo = inserted.PersonNo)
              END
       FROM   inserted

    Note: the above is not tested.

    • Marked as answer by JasonHuang8888 Friday, December 18, 2015 6:23 AM
    Thursday, December 17, 2015 11:04 PM

All replies

  • Hi JasonHuang8888,

    I don't think there's a straightforward way to do that. Neither do I think there would be an elegant workaround. However you can ignore the serial when inserting and get a sequence serial when querying. Please see below sample.

    CREATE TABLE TABLEA( PersonNo VARCHAR(10), Serial INT);
    
    INSERT INTO TABLEA(PersonNo) VALUES('John'),('John'),('Many'),('Jojo'),('John'),('Mary'),('Tom');
    GO
     
    SELECT PersonNo,ROW_NUMBER() OVER(PARTITION BY PersonNo ORDER BY (SELECT 1)) Serial FROM TABLEA
    
     

    If you have any question, feel free to let me know.




    Eric Zhang
    TechNet Community Support

    • Marked as answer by JasonHuang8888 Monday, December 21, 2015 12:55 AM
    Thursday, December 17, 2015 12:09 PM
  • One way would be to use an INSTEAD OF trigger:

    CREATE TRIGGER tbl_tri INSTEAD OF INSERT AS

    INSERT tbl(PersonNo, Serial)
       SELECT PersonNo,
              CASE WHEN Serial IS NOT NULL THEN Serial                ELSE row_number() OVER (PARTITION BY PersonNo
                                           ORDER BY (SELECT 1)) +
                        (SELECT MAX(tbl.Serial)
                         FROM   tbl
                         WHERE  tbl.PersonNo = inserted.PersonNo)
              END
       FROM   inserted

    Note: the above is not tested.

    • Marked as answer by JasonHuang8888 Friday, December 18, 2015 6:23 AM
    Thursday, December 17, 2015 11:04 PM
  • Instead of update trigger should be created as well and maybe instead of delete, if the serial sequence is required.
    Friday, December 18, 2015 12:44 AM