Trigger

Question

Trigger

newbieuser

SSCrazy Eights

Points: 9423
More actions
December 1, 2008 at 1:11 pm

#97005

Hi all,
I would like to get your opinion on creating a trigger or if there is any other possible way that you could think of for the below criteria.
Below is the table numwrk we need to create the trigger for. The table has pr_reprn,reprn_no and pr_no as primary keys. Initially, the data will be inserted for all the fields except the ‘itm’ field in this table. The value for the ‘itm’ field will be assigned through an interface. The ‘itm’ field could be blank or null. But when the value is being assigned, the insert should fail if the values for itm and reprn_no already exists (the value for itm field should be unique for every combination of itm and reprn_no).
create table numwrk (
pr_reprn varchar2(10) default ‘ ‘ not null,
reprn_no number(22) default 0not null,
pr_no number(22) default 0 not null,
itm varchar2(10) default ‘ ‘ ,
impid number(22) default 0,
stat varchar2(1) default ‘N’
);
Select pr_reprn, reprn_no, itm from numwrk;
Pr_reprnreprn_noitm
200 0ALDO
202 0ALDO-1
203 1ALDO
204 0AMLC
209 1AMLC
210 0
224 0CDEL
226 1
In the above select statement, the trigger must check for the existing combination of reprn_no and itm values and the insert or update must fail if a user attempts to insert or update a duplicate value for the reprn_no and itm combination.
For example,
for pr_reprn= 226, the user can insert or update itm= CDEL since reprn_no is 1.(reprn_no = 1 and itm = CDEL combination does not already exist).
for pr_reprn=210, the user should not be able to insert or update itm=CDEL since reprn_no is 0. ( reprn_no = 0 and itm = CDEL combination already exists). The trigger should be fired in this condition.
I’m trying to write a trigger for this condition. I would appreciate any of your help. Thanks a lot.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Instead of a trigger, would a unique index give you what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

GSquared (12/1/2008)
Instead of a trigger, would a unique index give you what you need?

That will depend if they want to allow multiple rows with NULL for itm for the same value of reprn_no. If they only want to allow one NULL itm record per reprn_no then a unique index would be fine, otherwise it won't work.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

Thanks. I just tried using the unique index:

CREATE UNIQUE INDEX numwrk_itm_reprn_no
ON numwrk
( itm,
CASE
WHEN itm IS NOT NULL
THEN reprn_no
ELSE NULL -- This is the default, so you don't really have to say it
END
)
select pr_reprn,reprn_no,itm from numwrk;
PR_REPRNREPRN_NOITM
200 0 aldo
202 0 aldo-1
203 1 aldo
204 0 amlc
209 1 amlc
237 0
224 0 cdel
226 1 cdel-1
insert into numwrk (pr_reprn,reprn_no) values ('238',0);

ORA-00001: unique constraint (PUBS.NUMWRK_ITM_REPRN_NO) violated

I think the index is supposed to allow duplicates for the same reprn_no with null itm column, but I'm still getting this error.

Please help. Thank you very much.