Auto-Increment Insert Trigger on Numerice Converted fFeld not Working

[rmtiptoes]

Hi All,

I am trying to create a trigger that auto-increments a PK field from another sequence table. Since I cannot change the structure of the existing table, I need to pull and increment the field and update the record for insert as a char type field. When I insert the record in the application, the auto-increment field remains blank. My table struture for the sequence file is as follows:

seq_id numeric 9(18,0)
table_name char 15
column_name char 15
last_num numeric 9(18,0)


CREATE TRIGGER SHOP_PERSON_SEQUENCE
ON [root].[ae_h_emp_e]
FOR INSERT
AS
DECLARE @SHOPPERSON VARCHAR(15)
SET @SHOPPERSON = CAST(@SHOPPERSON AS VARCHAR(15))

BEGIN
IF (SELECT SHOP_PERSON FROM INSERTED) IS NULL
SET @SHOPPERSON=(SELECT LAST_NUM FROM SEQ_NUMBER WHERE COLUMN_NAME = 'shop_person')
UPDATE SEQ_NUMBER
SET SEQ_NUMBER.LAST_NUM = SEQ_NUMBER.LAST_NUM + 1 WHERE COLUMN_NAME = 'shop_person'

UPDATE [root].[ae_h_emp_e]
SET SHOP_PERSON = @SHOPPERSON
WHERE SHOP_PERSON IS NULL
END



Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[SQLDenis]

you got major issues with this trigger
1) you did not account for a multirow statement

2)SET @SHOPPERSON=(SELECT LAST_NUM FROM SEQ_NUMBER WHERE COLUMN_NAME = 'shop_person')
this will probabaly always get the same row
3) what happens when 2 users hit this at the same time? potentially they might both get this: LAST_NUM + 1

Ans what is this gem supposed to do?
DECLARE @SHOPPERSON VARCHAR(15)
SET @SHOPPERSON = CAST(@SHOPPERSON AS VARCHAR(15))

it is already a varchar(15) ????

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[rmtiptoes]

So glad you responded!

Good question. Not sure how to handle this will have to research more. Any suggestions?
"2)SET @SHOPPERSON=(SELECT LAST_NUM FROM SEQ_NUMBER WHERE COLUMN_NAME = 'shop_person')
this will probabaly always get the same row
3) what happens when 2 users hit this at the same time? potentially they might both get this: LAST_NUM + 1"

Couldn't get it to work so I thought I needed to change it again. It did not work when this statement was not a part of the trigger so I was attempting to troubleshoot. If I remove, still does not work.
"Ans what is this gem supposed to do?
DECLARE @SHOPPERSON VARCHAR(15)
SET @SHOPPERSON = CAST(@SHOPPERSON AS VARCHAR(15))
it is already a varchar(15) ????"

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[SQLSister]

whyh not use an identity field instead?

Questions about posting. See faq183-874

 

[rmtiptoes]

Tried that but this db has an application sitting on top of which does not allow the structure to be revised. So I have to maintain the integrity of the fields and tables

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.