Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not seeing uncommitted values
Trigger not seeing uncommitted values [message #1275] Tue, 16 April 2002 09:50 Go to next message
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
Re: Trigger not seeing uncommitted values [message #1277 is a reply to message #1275] Tue, 16 April 2002 10:00 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
You could use the unique constraint to be sure that a value is only inserted once.
Previous Topic: Help me.
Next Topic: Re: Trigger not seeing uncommitted values
Goto Forum:
  


Current Time: Thu Mar 28 08:49:41 CDT 2024