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 Go to next message
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 #362301 is a reply to message #362299] Mon, 01 December 2008 14:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
It sounds like you have two separate issues that should be treated separately.

To prevent duplicates, use a unique constraint.

To only allow updates of a column when that column is null, use a before insert row trigger.
Re: Is it possible with a Trigger? [message #362304 is a reply to message #362299] Mon, 01 December 2008 14:20 Go to previous messageGo to next message
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 #362308 is a reply to message #362304] Mon, 01 December 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But to create unique contraint, the field has to be not null right??

Wrong.

Regards
Michel
Re: Is it possible with a Trigger? [message #362309 is a reply to message #362304] Mon, 01 December 2008 14:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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 Go to previous messageGo to next message
joy_division
Messages: 4642
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.


Re: Is it possible with a Trigger? [message #362503 is a reply to message #362299] Tue, 02 December 2008 08:39 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try removing the default value from the itm column.
Re: Is it possible with a Trigger? [message #362514 is a reply to message #362299] Tue, 02 December 2008 09:31 Go to previous message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thanks a lot. I dropped the default and it works now.
Previous Topic: Help on stored procedure
Next Topic: Word count
Goto Forum:
  


Current Time: Thu Dec 08 06:16:43 CST 2016

Total time taken to generate the page: 0.06459 seconds