Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible with a Trigger? (10.2.0.1, Windows 2003)
Is it possible with a Trigger? [message #362299] |
Mon, 01 December 2008 14:08  |
sant_new
Messages: 165 Registered: June 2008
|
Senior Member |
|
|
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 0 not 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_reprn reprn_no itm
200 0 ALDO
202 0 ALDO-1
203 1 ALDO
204 0 AMLC
209 1 AMLC
210 0
224 0 CDEL
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.
|
|
|
|
Re: Is it possible with a Trigger? [message #362304 is a reply to message #362299] |
Mon, 01 December 2008 14:20   |
sant_new
Messages: 165 Registered: June 2008
|
Senior Member |
|
|
Thanks Barbara. But to create unique contraint, the field has to be not null right?? THe problem is the itm field can have null values, so we are not able to create unique constraint for the field.
Actually on both conditions , when a user attempts to insert or update the itm field, the trigger has to check if that combination of itm and reprn_no values already exists in the table. If it exists, the trigger should fire and the insert or update should fail.
Thanks for your help.
|
|
|
|
Re: Is it possible with a Trigger? [message #362309 is a reply to message #362304] |
Mon, 01 December 2008 14:37   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Quote: | But to create unique contraint, the field has to be not null right??
|
Wrong. The field does not have to be not null. Try it, test it, and see for yourself.
Quote: |
Actually on both conditions , when a user attempts to insert or update the itm field, the trigger has to check if that combination of itm and reprn_no values already exists in the table. If it exists, the trigger should fire and the insert or update should fail.
|
Triggers do not fire based on combinations of column values. Triggers fire when inserts, updates, or deletes or other such events occur, then you check for other items using :new and/or :old values within the trigger code. Based on the evaluation of these values, the trigger code can either do nothing which allows the insert or update or delete to happen or raise an application error.
|
|
|
Re: Is it possible with a Trigger? [message #362322 is a reply to message #362304] |
Mon, 01 December 2008 15:15   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
sant_new wrote on Mon, 01 December 2008 15:20 | But to create unique contraint, the field has to be not null right
|
You may be thinking of a Primary Key, which the column must be (will be) NOT NULL, but this is not the same as a unique constraint.
|
|
|
Re: Is it possible with a Trigger? [message #362338 is a reply to message #362299] |
Mon, 01 December 2008 22:03   |
lakshmis
Messages: 102 Registered: November 2008 Location: India
|
Senior Member |
|
|
sant_new wrote on Mon, 01 December 2008 14:08 | Hi
the insert or update must fail if a user attempts to insert or update a duplicate value for the reprn_no and itm combination.
|
Hi,
create unique index <index_name> on <table_name>(reprn_no,itm);
I think this is sufficient. Why do you need a trigger?
|
|
|
Re: Is it possible with a Trigger? [message #362501 is a reply to message #362299] |
Tue, 02 December 2008 08:33   |
sant_new
Messages: 165 Registered: June 2008
|
Senior Member |
|
|
Thank you all for your replies.
I tried with the unique index, not sure if I'm doing something wrong.
I just created a new table in my test database and inserted fresh records and created the unique index.
SQL> create table numwrk (
2 pr_reprn varchar2(10) default ' ' not null,
3 reprn_no number(22) default 0 not null,
4 itm varchar2(10) default ' '
5 );
Table created.
SQL> CREATE UNIQUE INDEX NUMWRK_PK ON NUMWRK
2 (PR_REPRN, REPRN_NO);
Index created.
SQL> ALTER TABLE NUMWRK ADD (
2 CONSTRAINT NUMWRK_PK
3 PRIMARY KEY
4 (PR_REPRN, REPRN_NO));
Table altered.
SQL> insert into numwrk (pr_reprn, reprn_no, itm) values ('200',0,'aldo');
1 row created.
SQL> insert into numwrk (pr_reprn, reprn_no, itm) values ('202',0,'aldo-1');
1 row created.
SQL> insert into numwrk (pr_reprn, reprn_no, itm) values ('203',1,'aldo');
1 row created.
SQL> insert into numwrk (pr_reprn, reprn_no) values ('210',0);
1 row created.
SQL> insert into numwrk (pr_reprn, reprn_no, itm) values ('224',0,'cdel');
1 row created.
SQL> insert into numwrk (pr_reprn, reprn_no) values ('226',1);
1 row created.
SQL> commit;
Commit complete.
SQL> Select pr_reprn, reprn_no, itm from numwrk;
PR_REPRN REPRN_NO ITM
---------- ---------- ----------
200 0 aldo
202 0 aldo-1
203 1 aldo
210 0
224 0 cdel
226 1
SQL> CREATE UNIQUE INDEX numwrk_idx
2 ON numwrk
3 ( itm,
4 CASE
5 WHEN itm IS NOT NULL
6 THEN reprn_no
7 ELSE NULL -- This is the default, so you don't really have to say it
8 END
9 )
10 /
Index created.
Scenario 1: Success.
SQL> insert into numwrk (pr_reprn, reprn_no,itm) values ('228',1,'cdel');
1 row created.
SQL> insert into numwrk (pr_reprn, reprn_no,itm) values ('229',1,'cdel');
insert into numwrk (pr_reprn, reprn_no,itm) values ('229',1,'cdel')
*
ERROR at line 1:
ORA-00001: unique constraint (PUBS.NUMWRK_IDX) violated
So far it looks good.
But the Scenario 2, since 'itm' field can have null values, we should be able to insert the below record. But it fails.
Scenario 2:
SQL> insert into numwrk (pr_reprn, reprn_no) values ('227',1);
insert into numwrk (pr_reprn, reprn_no) values ('227',1)
*
ERROR at line 1:
ORA-00001: unique constraint (PUBS.NUMWRK_IDX) violated
Please give me your suggestions. Thank you.
Please give me your suggestions. Thank you.
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 11:12:30 CST 2025
|