-
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
-
could you put a unique constraint on fld2?
-
No, I can not, because it may contain NULLs. I need to only evaluate on valid data.
- Gary
-
Hey
u can't do this..
if ld. is null then
raise
else
end if;
Exception when then
Vijays
Vijay.s
-
unique constraint allows nulls - no problem. Only primary key and not null prevent nulls.
-
That did it! Apparently, Oracle handles NULLs differently than SQL Server.
Thanks guys,
- Gary
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|