Automatic sequence number (1,2, …) to be filled in a specific field

Question

I have a view table as below and I want to make the custom field named “number” to have as automatic sequence number (1,2,….) to be filled in the column CUSTOMFIELDVALUE according to REQUESTID

enter image description here

I need a trigger code on the WOCUSTOMFIELD table that do what I want

Thanks in Advance Lubna

Answer 1

The trigger grabs the max value from t and stores it in a table variable. The cte works out the row number of each insert to t and in the update phase adds it to the value stored int the table variable.

 use sandbox
    go
    --drop table t
    --create table t(workid int identity, requestid int,customfieldvalue int)
    --go
    IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tGenCustomid]'))
    drop trigger tGenCustomid
    go
    CREATE TRIGGER tGenCustomid
    ON t
    after insert
    AS
    BEGIN
      DECLARE @max TABLE (
        workid int,
        requestid int,
        customfieldvalue int
      );

      INSERT INTO @max (requestid,customfieldvalue)
        select requestid,
         coalesce(max(customfieldvalue),0)
        from     t
        group by requestid


      ;with cte as
      (select i.workid,i.requestid, row_number() over (partition by i.requestid order by i.workid) rn,
            m.customfieldvalue
      from inserted i
      join @max m on m.requestid = i.requestid
      )
      update t 
        set customfieldvalue = cte.rn + cte.customfieldvalue
        from t
        join cte on cte.workid = t.workid and cte.requestid = t.requestid

    END;

    go

    SET NOCOUNT ON
    truncate table debug_table
    truncate table t
    print 'First bunch of inserts'
    insert into t(requestid, customfieldvalue) 
    values
    (1,0),(1,0),
    (2,0),(2,0),(2,0),
    (3,0)

    select * from t

    print 'Second bunch of inserts'
    insert into t(requestid, customfieldvalue) 
    values
    (1,0),(1,0)
    select * from t

    print 'Third bunch of inserts'
    insert into t(requestid, customfieldvalue) 
    values
    (1,0),(4,0),(3,0)
    select * from t

    First bunch of inserts
    workid      requestid   customfieldvalue
    ----------- ----------- ----------------
    1           1           1
    2           1           2
    3           2           1
    4           2           2
    5           2           3
    6           3           1

    Second bunch of inserts
    workid      requestid   customfieldvalue
    ----------- ----------- ----------------
    1           1           1
    2           1           2
    3           2           1
    4           2           2
    5           2           3
    6           3           1
    7           1           3
    8           1           4

    Third bunch of inserts
    workid      requestid   customfieldvalue
    ----------- ----------- ----------------
    1           1           1
    2           1           2
    3           2           1
    4           2           2
    5           2           3
    6           3           1
    7           1           3
    8           1           4
    9           1           5
    10          4           1
    11          3           2