?
Solved

Create Trigger autonumber

Posted on 2008-10-13
14
Medium Priority
?
861 Views
Last Modified: 2010-05-18
Hi,
I've this table TAB1:

COD_ID............NAME........DESCRIP

I'd like to create a trigger that when I insert a new record into table TAB1 I get the value of column COD_ID = NAME_0000000001......NAME_0000000002.............NAME_0000000003............NAME_0000000010................(NAME underscore 10byte autonumber)

for example:

INSERT INTO TAB1 (NAME, DESCRIP)
VALUES ('AAA', 'THIS IS EXAMPLE');

select *
from tab1;

COD_ID....................NAME............DESCRIP
AAA_0000000001.........AAA...........THIS IS EXAMPLE

INSERT INTO TAB1 (NAME, DESCRIP)
VALUES ('BBB', 'THIS IS EXAMPLE2');

select *
from tab1;

COD_ID....................NAME............DESCRIP
AAA_0000000001.........AAA...........THIS IS EXAMPLE
BBB_0000000002.........BBB...........THIS IS EXAMPLE2

INSERT INTO TAB1 (NAME, DESCRIP)
VALUES ('XXX', 'THIS IS EXAMPLE3');

select *
from tab1;

COD_ID....................NAME............DESCRIP
AAA_0000000001........AAA...........THIS IS EXAMPLE
BBB_0000000002.........BBB...........THIS IS EXAMPLE2
XXX_0000000003........XXX...........THIS IS EXAMPLE3

INSERT INTO TAB1 (NAME, DESCRIP)
VALUES ('CCC', 'THIS IS EXAMPLE4');

select *
from tab1;

COD_ID....................NAME............DESCRIP
AAA_0000000001........AAA...........THIS IS EXAMPLE
BBB_0000000002.........BBB...........THIS IS EXAMPLE2
XXX_0000000003........XXX...........THIS IS EXAMPLE3
CCC_0000000004........CCC...........THIS IS EXAMPLE4
.......................................................................................
.......................................................................................
.......................................................................................
.......................................................................................
.......................................................................................
.......................................................................................

How can I write this trigger to get my output??

Thanks in advance!
0
Comment
Question by:chirpl_chirpl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
14 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 22703472
Please use a standard identity column for the autonumber.  

The other column can be derived as a computed column or in a view:

COD_ID VARCHAR(30) AS NAME + '_' + RIGHT(REPEAT('0',9)+CAST(AUTO_ID AS VARCHAR(10)),10)


0
 

Author Comment

by:chirpl_chirpl
ID: 22703497
dqmq,
thanks but I need a trigger.

How can I write your code into a trigger?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 22703772
Please try code below. The tricky part is if multiple rows are inserted at one time, which I think the code below handles, and I've tested for smaller numbers of rows, but can't 100% guarantee since the SQL engine sometimes gets "goofy" in this situation.

For SQL 2005, be sure to include the hint to also *prevent* this query from use parallelization; that almost surely won't work for that UPDATE statement.


CREATE TRIGGER tab1_trg_ins
ON tab1
AFTER INSERT
AS
IF @@ROWCOUNT = 0
RETURN
DECLARE @cod_id_number BIGINT
SELECT @cod_id_number = COALESCE(RIGHT(MAX(cod_id), 10), 0)
FROM tab1 WITH (UPDLOCK)
WHERE cod_id IS NOT NULL

UPDATE tab1
SET @cod_id_number = @cod_id_number + 1,
cod_id = 'NAME_' + RIGHT(REPLICATE('0', 10) + CAST(@cod_id_number AS VARCHAR(10)), 10)
FROM tab1
INNER JOIN inserted i ON i.name = tab1.name AND
ISNULL(i.descrip, '') = ISNULL(tab1.descrip, '')

GO
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dqmq
ID: 22703801
You will need to invent your own autonumber mechanism.  Unless you can create another table to manage it, a trigger will have very bad performance.  Are you sure?  
0
 

Author Comment

by:chirpl_chirpl
ID: 22703812
ScottPletcher:
thanks for your answer,
but I've 2 database, one on sqlserver 2005 and one on sqlserver2000

How can I write your trigger also for database sqlserver 2000?

Thanks in advance!
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 500 total points
ID: 22704034
Scott,
Very clever.  Some feedback/questions:

1. this line:

cod_id = 'NAME_' + RIGHT(REPLICATE('0', 10) + CAST(@cod_id_number AS VARCHAR(10)), 10)

needs to be:
cod_id = i.name +  '_' + RIGHT(REPLICATE('0', 10) + CAST(@cod_id_number AS VARCHAR(10)), 10)


2.  What is purpose of: IF @@ROWCOUNT = 0?  Does insert trigger EVER get invoked when @@ROWCOUNT = 0?

3.  The goal (I think) is to generate a unique key in part because Name + Description is not sufficiently unique.  In that case, solution will not work.

4.  How are you preventing infinite recursion?  I suggest adding this condition to update statement:
     WHERE tab1.cod_id IS NULL

5. What pecisely do you think (UPDTLOCK) is locking?
 

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22704050
That trigger will work for both.  In fact, SQL 2000 actually does support the hint I was talking about, so the same code can be put in both triggers:

UPDATE tab1
SET @cod_id_number = @cod_id_number + 1,
cod_id = 'NAME_' + RIGHT(REPLICATE('0', 10) + CAST(@cod_id_number AS VARCHAR(10)), 10)
FROM tab1
INNER JOIN inserted i ON i.name = tab1.name AND
ISNULL(i.descrip, '') = ISNULL(tab1.descrip, '')
OPTION (MAXDOP 1)  --<<-- add this line to the UPDATE statement in the trigger

You want to make sure SQL does not try to use parallelization for that UPDATE (most unlikely anyway, but it's best to be absolutely sure, since para~ would likely produce incorrect results).
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22704298
Other issues I see:

1.  Select max(cod_id)  does not work.   You need to do:
   Select max(right(cod_id,10))
and pray that you never get text in those last 10 bytes.

2.  What is the Primary Key to this table?  If you are expecting cod_id to be the primary key, then you cannot permit null.  I don't think the Insert will make it to the trigger if a PK column is missing.   Perhaps you could sneak by with an Instead of trigger--not sure, and don't have time to test.

3. There are still more problems with this.  It's bad technique, plain-and-simple. Someday, your data will almost certainly look something like this:

COD_ID....................NAME............DESCRIP
AAA_0000000001........BBB...........THIS IS BAD EXAMPLE 1
AAA_00000000OZ........AAA...........THIS IS BAD EXAMPLE 2

Then what?  Do you need an update trigger, as well?



 




0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22704498
>> 1.  Select max(cod_id)  does not work. <<

Why not?  The prefix is *always* "NAME_", and is *always* followed by a *10*-digit number, left filled with zeros.  Why wouldn't MAX() of that *always* give me the largest value?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22704514
You really should add a unique value to use in JOINs to assign the id.  You could use an IDENTITY column (exclusively) for that purpose, and use the generated id everywhere else.  Otherwise a duplicate name and description would cause two rows to get the same id value.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22704643
Scott,
>The prefix is *always* "NAME_"

Look at the examples farther down in the question.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22704680
Oops, sorry, yeah, it's not "NAME", it's the name column.  Grr, that complicates things considerably.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question