Checking uniqueness through triggers.

[rajk123]

I would like to know how the following can be done through a trigger -

When session A inserts a row in a table I would like to check for uniqueness for one of the columns through the insert trigger before commiting the row. This works well for all rows which have already been commited but suppose another session B has inserted a row and session A has not commited it (B) does not "see" the row added by A and the check for uniqueness fails if both B and A have the same value for that column.
The interesting thing is it works when you put a unique constraint on the column (even if it is not commited). For some reason I do not want to put a constraint on the table but validate it through a trigger. Basically I want to know how the constraint does it so I could implement this in triggers.

 

[sem]

I suppose you can not implement it in trigger unless you use table locking (not a good idea, isn't it? ). Oracle validating procedures are not implemented in pl/sql, so may access "hidden" entities, such as database blocks. Thus I'd recommend you to use unique constraint. Regards, Dima

 

[sent13]

i would also suggest you to go for constraints as the best way to implement business rules or constraints rather than going for triggers.

senthil.

 

[rajk123]

Thanks for your comments. I would have used constraints but it is only on some business secnarios that I want to check for the uniqueness so putting a constriant will not work. I have to do this in a pl/sql block which can be done through triggers. I am still looking for a solution using triggers.

Thanks,
Raj.