July 24, 2008 at 8:57 am
Hi,
I have a trigger which is used to generate a ID for each record inserted , this trigger is part of a existing Enterprise software so i cannot have identity column, the problem is that when the client table is empty and a new client record is created a client id is not being generated but if i enter a client id manually for the first client record then the client ids for next client records gets generated automatically so i think that the if clause i have written for the cursor status is being skipped , can any one tell me what is wrong with the code ,so kindly help me with this.
The if condition is used to check if the table is empty or not or more precisely wether a client id exists for an existing record.
July 24, 2008 at 9:32 am
First, it is possible to modify an existing column to make it an Identity. That might solve your problem easily.
Second, to help you with the trigger, we would need you to at least post the code of the trigger. Usually, the table structure matters too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 11:56 pm
CREATE TRIGGER [dbo].[clientActiveID] ON [dbo].[Client]
For Insert As
Begin
Declare @clientrefid nchar(20)
Declare @idno int
Declare curclientRefID SCROLL cursor
For Select client_activeid from client Order By client_activeid
Open curclientRefId
Fetch Last from curclientRefID into @clientrefid
Print @clientrefid
print 'ok'
If(@@fetch_status=0)
Begin
Print 'ok1'
Declare @refid int
Set @refid=Cast(Substring(@clientrefid,4,7) as int)
print @refid
Set @refid=@refid+1
Declare @reftext varchar(20)
Set @reftext = Cast(@refid as varchar(20))
print @reftext
Set @reftext =Replicate('0',7-Len(@reftext))+@reftext
print @reftext
print @clientrefid
Set @clientrefid=Stuff(@clientrefid,4,7, @reftext)
print @clientrefid
Update client
Set client_activeid=@clientrefid where client_activeid is null
End
Else
--if(@@fetch_status=-2)
Begin
print @@fetch_status
Declare @slno int
Set @slno=1
Declare @sltext varchar(20)
Set @sltext =Cast(@slno as varchar (20))
Set @sltext =Replicate('0',7-len(@sltext))+@sltext
Set @clientrefid ='PW'+'-'+@sltext
Update client
Set client_activeid=@clientrefid where client_activeid is null
End
Close curclientRefID
Deallocate curclientRefID
END
July 25, 2008 at 7:33 am
Here is a script but it is not ideal -
GSquared is right when he said you should try an identity col first.
This should work on SQL2000 although I did not test it. 2005 would offer a more elegant solution by getting rid of the looping completely.
Also, one trigger fires even if you update multiple rows, so you must make sure your trigger can cope with multiple row inserts. You should also think of creating an index on the id field if it is a big table.
Remember - this is not your ideal solution.
CREATE TRIGGER [dbo].[clientActiveID] ON [dbo].[Client]
For Insert As
Begin
DECLARE @clientrefid nchar(20)
DECLARE @idno int
DECLARE @rowsinserted INT--THE COUNT OF ROWS WE NEED TO UPDATE
DECLARE @counter INT--LOOPING COUNTER TO MOVE THROUGH THE ROWS
DECLARE @refid int
DECLARE @prefix nvarchar(4)--THE PREFIX TO USE WHEN COMPOSING THE NUMBER
--GET THE AMOUNT OF ROWS THAT WERE INSERTED
SELECT @rowsinserted = COUNT(*) FROM INSERTED
SELECT @counter = 1
Select @clientrefid = MAX(client_activeid) from client
--GET THE PERFIX AND THE BASE NUMBER TO INCREMENT
IF @clientrefid IS NOT NULL
BEGIN --THE TABLE HAS ALREADY A NUMBER...
Print 'ok1'
SET @refid=Cast(Substring(@clientrefid,4,7) as int)
SET @prefix = Substring(@clientrefid,0,4)
END
ELSE
BEGIN
SET @refid= 0
SET @prefix = 'PW-'
END
SET ROWCOUNT 1 --WE ONLY UPDATE ONE ROW AT A TIME - OTHERWISE WE COUNL GET DUPLICATE ID'S
--LOOP THROUGH THE NEW ROWS AND UPDATE THEM ONE AT A TIMEWITH A NEW ID
WHILE @counter <= @rowsinserted
BEGIN
Update client
Set client_activeid = @prefix + Replicate('0',7-Len(@refid + @counter)) + CAST( @refid + @counter AS NVARCHAR)
where client_activeid is null
SELECT @counter = @counter + 1
END
SET ROWCOUNT 0 --RESET ROWCOUNT
END
July 25, 2008 at 8:43 am
This is in addition to what GSquared and tertiusdp said. Although since the id field is character you can't convert it to an Identity column without changing the data type as well.
A trigger is probably not the best place to be creating an ID for your rows. What if 2 processes create a new row at the same time? They will both get the same ID. Normally when you manually increment a field you would have row in a table that you lock when selecting and update when you are done. This way you will always get the real "Next" number. Secondly, if you really do not want nulls in the field it should be set to not null and any process doing an insert should create the value. You could create a scalar-valued UDF that you can include in the insert.
All that being said here is trigger code (along with testing code using AdventureWorks) that does what you want the trigger to do:
[font="Courier New"]USE AdventureWorks
GO
CREATE TABLE dbo.client
(
client_active_id NCHAR(20) NULL,
client_name NVARCHAR(20) NOT NULL
)
GO
CREATE TRIGGER [dbo].[clientActiveID] ON [dbo].client
FOR INSERT AS
BEGIN
DECLARE @NextId INT,
@client_name VARCHAR(20)
SELECT
@NextID = ISNULL(MAX(CONVERT(
INT, SUBSTRING(
client_active_id, 4, 7)) + 1), 1)
FROM
dbo.client
PRINT 'ID is ' + CONVERT(VARCHAR(7), @NextID)
-- handle multiple rows
IF (SELECT COUNT(*) FROM inserted) > 1
BEGIN
PRINT 'Multi update'
DECLARE c_clients CURSOR FOR
SELECT
client_name
FROM
inserted
OPEN c_clients
FETCH Next FROM c_clients INTO
@client_name
WHILE @@Fetch_Status = 0
BEGIN
UPDATE dbo.client
SET client_active_id = REPLICATE('0',
7-LEN(CONVERT(VARCHAR(7), @NextId))) +
CONVERT(VARCHAR(7), @NextId)
WHERE
client_name = @client_name
SET @NextId = @NextId + 1
PRINT 'ID is ' + CONVERT(VARCHAR(7), @NextID)
FETCH Next FROM c_clients INTO
@client_name
END
CLOSE c_clients
DEALLOCATE c_clients
END
ELSE
-- single row
BEGIN
PRINT 'single row'
PRINT 'ID is ' + CONVERT(VARCHAR(7), @NextID)
UPDATE dbo.client
SET client_active_id = REPLICATE('0',
7-LEN(CONVERT(VARCHAR(7), @NextId))) +
CONVERT(VARCHAR(7), @NextId)
WHERE
client_name IN (SELECT client_name FROM inserted)
END
END
GO
BEGIN TRANSACTION
INSERT INTO dbo.client
(
client_name
)
SELECT
'Single Test'
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT INTO dbo.client
(
client_name
)
SELECT
LEFT(name, 20)
FROM
HumanResources.Department
COMMIT TRANSACTION
SELECT * FROM dbo.client
TRUNCATE TABLE dbo.client
-- use this when you are done
-- Drop Table dbo.client
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 25, 2008 at 9:53 am
Sorry my mistake , the table already has a id field which the software creates by itself so Client ID this is an addition field which the software user can see and use it in different scenarios. The ID created by the software is used by the application internally.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply