Solved

Create Trigger autonumber

Posted on 2008-10-13
14
825 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
  • 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:
ScottPletcher earned 125 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
 
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 125 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
ID: 22704680
Oops, sorry, yeah, it's not "NAME", it's the name column.  Grr, that complicates things considerably.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now