April 2, 2003 at 8:41 am
I have a table
Company (
company_id varchar(10) not null,
company_name varchar(50),
ROWID identity
)
On insert of the company, I initially have a value of '1' and then I update it based on ROWID
appending 'C'+ROWID value to come up with unique 10 character Value eg 'C000000001','C000000002'
here are the steps
1) Insert into company(company_id ,company_name )
Values ('1','ABC inc')
2) Update company
set company_id = 'C' +Rowid
Where ROWID=@@identity
I do this in a store proc. On the same table I want to have a trigger to populate the journal table.
Company_jn (
business_date datetime default getdate(),
company_id varchar(10) not null,
company_name varchar(50),
ROWID identity
)
Here is where the problem starts.
if I do not have the trigger on the table the SP runs fine and generates values like 'C000000001','C000000002'etc.
but if I have a trigger on the table. The insert takes place and trigger gets fired to insert into the journal table
the update doesn't take place at all.
What I am doing wrong here? appreciate your help. thanks in advance.
April 2, 2003 at 11:08 am
The @@IDENTITY returns the most recent value used to insert a row. Your problem is that your journal table has an IDENTITY column so the value returned is that secondary insert (not your original one).
You could explore SCOPE_IDENTITY() function, but I suggest that you remove that column from the journal - no need to have a PK on such, but I suggest you add the extra columns to Company_jn for capturing who/where/what/when (repost if you need help)
Dick
London, UK
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply