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.