Trigger Help!

[gshaf]

Hi guys,

I need to create a trigger that throws an error for duplicate items:

For example, I have a table called table1 that has two colums:
fld1, fld2... where fld2 is a NULL colum. I need to write a trigger that determines before an insert or update it has to check column fld2 and throw an error if trying to insert or update a duplicate value:

CREATE OR REPLACE TRIGGER SomeJob_TRIG
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW

BEGIN

SELECT fld2
INTO :new.v_fld2
FROM table1
WHERE fld2 IS NOT NULL;

.
.
.
.

END SomeJob_TRIG;
/


Any help would greate be appreciated.

Thanks,
- Gary

[kmesser]

could you put a unique constraint on fld2?

[gshaf]

No, I can not, because it may contain NULLs. I need to only evaluate on valid data.

- Gary

[vbaskar]

Hey

u can't do this..

if ld. is null then
raise
else

end if;

Exception when then


Vijays

[kmesser]

unique constraint allows nulls - no problem. Only primary key and not null prevent nulls.

[gshaf]

That did it! Apparently, Oracle handles NULLs differently than SQL Server.

Thanks guys,
- Gary