Trigger not seeing uncommitted values [message #1275] |
Tue, 16 April 2002 09:50 |
Jack Haster
Messages: 4 Registered: April 2002
|
Junior Member |
|
|
I am trying to write a trigger that stops a value from being inserted more than once into a table. While I could do this other ways I am aiming to do it with a trigger.
Basically, the trigger works like this: when inserting a value into columnX of the table, the trigger searchs columnX to see if that value exists already. If it does, it raises an error, otherwise the value is inserted.
I used an autonomous transaction in the trigger to avoid the mutating table problem. It works fine. The problem I encounter is due to uncommitted data. If I try to insert the value 10 twice, the trigger won't see the second insert because the first insert is not yet committed. If I commit after each insert, the trigger works properly.
So my question is, is there a way to have the trigger function properly without committing after each insert? Say I want to insert 1000 records as a single transaction, is there a way to make the trigger work?
Regards,
Jack
|
|
|
|