The issue is for Sybase 15 ASE Db.
We have a table TB1 that has trigger TR1 on it for insert, update, delete. The trigger inserts a row for each invocation in table TB2 ( for audit purpose ). Table TB2 has an index on one of the columns( insert time ) for fast lookup for later analysis.
We noticed is that upon inserts in TB1 by a daily job, a unique key constraint violation is being thrown. We verified that the data being inserted is unique and there isn't any pre-existing data in TB1 that can cause the issue.
The interesting aspect is that, upon dropping the trigger the inserts go though and no unique key constraint violation is thrown.
Another issue we noticed is that, the database driver returns update/insert row count that doesn't matches expected row count. However, upon dropping the trigger the row count matches.
TB1 and TB2 are independent tables. Trigger code inserts into TB2 and has no bearing on data being inserted in TB1.
Appreciate any pointers to help debug.
We have been trying to emulate the issue by simulating the process in non-prod env by mimicking the insert/update pattern. But so far without success.
Trigger only inserts data in TB2 which is separate from TB1 the primary table.
TB2:
{
col1 : double
insert_time : datetime ( non-unique non-clustered index column )
source_table : string
}
create index idx_0 on TB2(insert_time)
create trigger TR1 on TB1
for insert, update, delete as
begin
insert into TB2 values(123, getDate(), 'abc')
end
Error throws is unique/primary key constraint violation on TB1