Home » SQL & PL/SQL » SQL & PL/SQL » Preventing duplicate column value using Trigger (11g)
Preventing duplicate column value using Trigger [message #446370] Mon, 08 March 2010 00:31 Go to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
I want to avoid duplication on old_nic_no and new_nic_no number in a Table, for this i am using a following trigger

create or replace TRIGGER chk_ip_duplications
BEFORE UPDATE
ON cb_insured_person_bak
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
nic VARCHAR2 (13);
cnic VARCHAR2 (15);
BEGIN
IF UPDATING AND :NEW.old_nic_no IS NOT NULL
THEN
SELECT a.old_nic_no
INTO nic
FROM cb_insured_person_bak a
WHERE a.old_nic_no = :NEW.old_nic_no;

IF SQL%FOUND
THEN
raise_application_error (-20009,
'This NIC '
|| :NEW.old_nic_no
|| ' Already Exist'
);
END IF;
ELSIF UPDATING AND :NEW.new_nic_no IS NOT NULL
THEN
SELECT a.new_nic_no
INTO cnic
FROM cb_insured_person_bak a
WHERE a.new_nic_no = :NEW.new_nic_no;

IF SQL%FOUND
THEN
raise_application_error (-20009,
'This CNIC '
|| :NEW.new_nic_no
|| ' Already Exist'
);
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;


when i use following command

update cb_insured_person_bak set new_nic_no = '34601-0774284-9' where eobi_no = '4300G043746'

34601-0774284-9 already exist with another eobi_no , i expect error described in rise_application_error clause but i give following error

ORA-04091: Table cb_insured_person_bak is mutating..........

How i can resolve this problem.....
Re: Preventing duplicate column value using Trigger [message #446371 is a reply to message #446370] Mon, 08 March 2010 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use a trigger for this, use unique contraints.

Regards
Michel
Re: Preventing duplicate column value using Trigger [message #446372 is a reply to message #446370] Mon, 08 March 2010 00:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
BOLD does not improve readability

CREATE OR REPLACE TRIGGER chk_ip_duplications
  BEFORE UPDATE ON cb_insured_person_bak
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
  nic   VARCHAR2(13);
  cnic  VARCHAR2(15);
BEGIN
  IF updating
     AND :NEW.old_nic_no IS NOT NULL THEN
    SELECT a.old_nic_no
    INTO   nic
    FROM   cb_insured_person_bak a
    WHERE  a.old_nic_no = :NEW.old_nic_no;
    
    IF SQL%FOUND THEN
      Raise_application_error(-20009,'This NIC '
                                     ||:NEW.old_nic_no
                                     ||' Already Exist');
    END IF;
  ELSIF updating
        AND :NEW.new_nic_no IS NOT NULL THEN
    SELECT a.new_nic_no
    INTO   cnic
    FROM   cb_insured_person_bak a
    WHERE  a.new_nic_no = :NEW.new_nic_no;
    
    IF SQL%FOUND THEN
      Raise_application_error(-20009,'This CNIC '
                                     ||:NEW.new_nic_no
                                     ||' Already Exist');
    END IF;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    NULL;
END; 
Re: Preventing duplicate column value using Trigger [message #446379 is a reply to message #446371] Mon, 08 March 2010 01:00 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
it is not possible as duplication already exists, we want to avoid further duplications, this table contains about 3.5 million records
Re: Preventing duplicate column value using Trigger [message #446387 is a reply to message #446379] Mon, 08 March 2010 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is possible:
SQL> create table t (val integer);

Table created.

SQL> insert into t values(0);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> create index ti on t(val);

Index created.

SQL> alter table t add constraint t_unq unique (val) using index ti enable novalidate;

Table altered.

SQL> insert into t values(0);
insert into t values(0)
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.T_UNQ) violated


SQL> insert into t values (1);

1 row created.

SQL> /
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.T_UNQ) violated

Regards
Michel

[Updated on: Mon, 08 March 2010 01:30]

Report message to a moderator

Re: Preventing duplicate column value using Trigger [message #446396 is a reply to message #446387] Mon, 08 March 2010 02:01 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Thank U U U U Very Much....
Re: Preventing duplicate column value using Trigger [message #446411 is a reply to message #446396] Mon, 08 March 2010 03:49 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Help me little more we have date_of_birth column which is null in some records, can we add a constraint to make it not null for further insertions.....
Re: Preventing duplicate column value using Trigger [message #446413 is a reply to message #446411] Mon, 08 March 2010 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same way:
SQL> insert into t values(null);

1 row created.

SQL> alter table t add constraint t_nn check (val is not null) enable novalidate;

Table altered.

SQL> insert into t values(null);
insert into t values(null)
*
ERROR at line 1:
ORA-02290: check constraint (MICHEL.T_NN) violated

Regards
Michel
Re: Preventing duplicate column value using Trigger [message #446414 is a reply to message #446413] Mon, 08 March 2010 04:03 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Really thank full to you, GOD BLESS YOU.....
Re: Preventing duplicate column value using Trigger [message #446419 is a reply to message #446414] Mon, 08 March 2010 04:32 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Can we manage a scenario through constraint,
"we have two columns new_nic_no & old_nic_no if one is null other should be filled and vice versa"
Re: Preventing duplicate column value using Trigger [message #446420 is a reply to message #446419] Mon, 08 March 2010 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
check ( new_nic_no is not null or old_nic_no is not null )

Regards
Michel
Re: Preventing duplicate column value using Trigger [message #446437 is a reply to message #446420] Mon, 08 March 2010 06:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Now, malhi, show us what you learned and adjust Michel's code in such a way that only one of the two can be entered..
Re: Preventing duplicate column value using Trigger [message #446504 is a reply to message #446437] Mon, 08 March 2010 23:15 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
I shall do like this....

alter table t add constraint nic_cnic_chk check (old_nic_no is not null or new_nic_no is not null) enable novalidate;
Re: Preventing duplicate column value using Trigger [message #446508 is a reply to message #446504] Mon, 08 March 2010 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "novalidate" ONLY if your current data do not fit the constraint AND you don't want to fix them.

Regards
Michel

[Updated on: Mon, 08 March 2010 23:38]

Report message to a moderator

Re: Preventing duplicate column value using Trigger [message #446511 is a reply to message #446508] Mon, 08 March 2010 23:38 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
OK... Thanks
Re: Preventing duplicate column value using Trigger [message #446518 is a reply to message #446504] Tue, 09 March 2010 00:39 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
malhi wrote on Tue, 09 March 2010 06:15
I shall do like this....

alter table t add constraint nic_cnic_chk check (old_nic_no is not null or new_nic_no is not null) enable novalidate;

But this will allow people to enter a value for both columns. Is that what you want?
Previous Topic: not able to get single mvlog
Next Topic: How to get rows N times in a result set?
Goto Forum:
  


Current Time: Sun Dec 04 10:32:50 CST 2016

Total time taken to generate the page: 0.05528 seconds