Path: news.easynews.com!easynews!feedwest.news.agis.net!aleron.net!sfo2-feed1.news.digex.net!intermedia!logbridge.uoregon.edu!pln-w!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: Insert trigger to enforce uniqueness Date: 3 Mar 2002 18:10:05 -0800 Organization: Oracle Lines: 26 Message-ID: References: <26ee1067.0203031743.c3da536@posting.google.com> NNTP-Posting-Host: p-489.newsdawg.com X-Newsreader: Direct Read News 2.91 Xref: easynews comp.databases.oracle.server:137332 X-Received-Date: Sun, 03 Mar 2002 19:34:04 MST (news.easynews.com) In article <26ee1067.0203031743.c3da536@posting.google.com>, tram_e@hotmail.com says... > >Hi, > >Our third party application doesn't allow to create Unique indexes on >a column. >I want to write an insert trigger which allow nulls but no duplicate >values on a column (peopleID column). Waht is the best way to write >BEFORE INSERT TRIGGER for thsi kind of requirement? Please give sample just create a unique constraint on it and be done with it. No worse for you to create a unique index then to add triggers to third party products (no better either) It will allow NULLS and enforce uniqueness. Doing this in a trigger is fruitless unless you serialize all inserts into this table (multi-versioning, if two people insert to same value at the same time -- your trigger will never see it). -- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp