trigger to increment data number

[deejayAr]

I have scenario
I want to save the data into a Name field of give table
First time it save the name
if second I provide same name I want to check if new name is already exist then triger should save new name as name 1 and for next save if name is name 1 it should save as name 2 egc....
my table is
CREATE TABLE [dbo].[tbl_EventTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Severity] [int] NULL,
[EventTypeTemplateId] [int] NULL,
[Name] [nvarchar](max) NULL,
CONSTRAINT [PK_tbl_EventType_pId] PRIMARY KEY CLUSTERED

or there is any other way, I need your suggession
thanks

 

[rasETL]

You could simply count(*) the number of rows that have the Name as the value, then add 1 to the count.
If Name does not currently exist, count = 0 + 1 = 1.
If Name exists once, then 1+1 = 2.
This could be a sub-select or pre-select using a Temp Table.

 

[bborissov]

And what if the user deletes some records with the same name?
BTW you could always get value with ROW_NUMBER() w/o any triggers.
But that will require to change all your queries.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Qik3Coder]

This code WILL run. This code WILL update EVERY row in the database.
This code is NOT OPTIMIZED to be minimally invasive.

You would use this as a base for identifying how to restrict the updated rows to only the ones with the same name type as the ones in the UPDATED table, which is only present inside the trigger.

create table tbl_EventTypes(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Severity] [int] NULL,    
[EventTypeTemplateId] [int] NULL,    
[Name] [nvarchar](max) NULL)

INSERT INTO tbl_EventTypes (Severity, EventTypeTemplateID, Name)
SELECT 1, 0, 'Aaaa A 1' UNION ALL
SELECT 1, 0, 'Aaaa A' UNION ALL
SELECT 1, 0, 'Bee Bee' UNION ALL
SELECT 2, 3, 'Aaaa A' UNION ALL
SELECT 2, 3, 'Bee Bee' UNION ALL
SELECT 3, 1, 'Ce Ce Ce' UNION ALL
SELECT 3, 2, 'Ce Ce Ce' UNION ALL
SELECT 3, 2, 'Ce Ce Ce'

SELECT * from tbl_EventTypes

UPDATE tet
SET tet.Name = SUBSTRING(dt.Name, 1, LEN(dt.Name) - CHARINDEX(' ', REVERSE(dt.NAME))) + ' ' + rtrim(dt.RowNum)
FROM
( 
SELECT *, ROW_NUMBER() OVER (
	PARTITION BY Severity, EventTypeTemplateID, SUBSTRING(Name, 1, LEN(Name) - CHARINDEX(' ', REVERSE(NAME)))
	ORDER BY Severity, EventTypeTemplateID, SUBSTRING(Name, 1, LEN(Name) - CHARINDEX(' ', REVERSE(NAME)))) RowNum
FROM (
	SELECT ID, Severity, EventTypeTemplateID, 
		CASE WHEN Len(IsNull(Name, '')) > 0 THEN
			CASE WHEN IsNumeric(substring(Name, LEN(Name),1)) = 1  THEN Name ELSE Name+' 0' END 
		ELSE '' END Name
	FROM tbl_EventTypes) as a 
) as dt
JOIN tbl_EventTypes tet on 
	dt.ID=tet.ID

SELECT * from tbl_EventTypes

HTH,
Lodlaiden

I'll answer your question, not solve your problem