How to write trigger to generate sequence number for repeated value

Question

I need to generate sequence number for repeated value in a SQL Server table.

Here's my table

CREATE TABLE [dbo].[tbl_all_purple_flag_level](
[Sno] [int] IDENTITY(1,1) NOT NULL,
[Id] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Score] [varchar](50) NULL,
[Disability_Level] [varchar](50) NULL,
[visited_count] [varchar](50) NULL,
[Date] [varchar](50) NULL
)

If id has repeated numbers, I need visited_count to be a sequence number (1, 2, 3...)

I tried this code

SELECT 
   id, 
   RIGHT('0'+ CAST((ROW_NUMBER() OVER (Partition By id Order By id)) as varchar(2)), 2) as duplicate_id
FROM 
   tbl_all_purple_flag_level

It works fine but I don't know how to do in trigger. Can anyone help me? Thanks!

You probably want Order By Sno and not Order By id in your last SQL statement. — Andrew Savinykh
– Andrew Savinykh, Commented Jan 7, 2014 at 19:29
I think you want visited_count to increase if the id is the same? So the first record with id='a' has visited_count = 1 and the second record with id='a' has visited_count = 2. And you want to accomplish this with a trigger, am I right? — Paul
– Paul, Commented Jan 7, 2014 at 19:46

M.Ali · Accepted Answer · 2014-01-07 20:11:18Z

1

ALTER TRIGGER tr_After_Update_Inser
ON [dbo].[tbl_all_purple_flag_level]
FOR INSERT, UPDATE
AS
BEGIN
 SET NOCOUNT ON;
WITH Updateable
AS
 (
  SELECT [Sno], id,  [visited_count],
        RIGHT('0'+ CAST((ROW_NUMBER() OVER (Partition By id Order By id, [Date] DESC)) as varchar(2)), 2) as duplicate_id
  FROM tbl_all_purple_flag_level 
 )
 UPDATE Updateable
  SET [visited_count] =  duplicate_id
END

One thing I would like to point out, SQL Server has Data types to store almost all kinds of data, I can see you are saving Dates in Varchar data type and IDs and Score all in varchar data type, Sql Server has Date data type to store date values. why would you use varchar for all the columns dont know but I think you should consider using appropriate data types for your column instead of using VARCHAR for everything.

edited Jan 7, 2014 at 20:11

answered Jan 7, 2014 at 20:03

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HLGEM Over a year ago

I shudder at the though of someone using varchar to store dates! A dat integrity problem just wating to happen.

M.Ali Over a year ago

@HLGEM tell me about it. any string can end up in that column, knowing some poeple , most probably you will end up having data like Today or even 2day :)

Collectives™ on Stack Overflow

How to write trigger to generate sequence number for repeated value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related