Forum Stats

  • 3,724,521 Users
  • 2,244,775 Discussions
  • 7,851,068 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

How to force uniqueness across 2 tables ?

SalimWer
SalimWer Member Posts: 224 Bronze Badge
edited June 2015 in SQL & PL/SQL

Hi

I have a column email1 in two tables A and B.

and I don't want to allow to insert the email twice in both tables, the email should either exists in table A or in table B not both.

Is there an easy way to implement this feature ?

Best Regards

Salim

ManikCelal ÖzdemirsgalaxyinvalidsearchWilliam RobertsonKarthick2003Stew Ashton2779499SalimWerChris Saxon-Oracle

Best Answer

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited June 2015 Accepted Answer

    Something like this I guess

    SQL> create table a (id integer, email varchar2(100));
    
    Table created.
    
    SQL> alter table a add constraint a_uk unique(email);
    
    Table altered.
    
    SQL> create table b (id integer, email varchar2(100));
    
    Table created.
    
    SQL> alter table b add constraint b_uk unique(email);
    
    Table altered.
    
    SQL> create materialized view log on a with rowid including new values;
    
    Materialized view log created.
    
    SQL> create materialized view log on b with rowid including new values;
    
    Materialized view log created.
    
    SQL> alter materialized view log on b add (email);
    
    Materialized view log altered.
    
    SQL> alter materialized view log on a add (email);
    
    Materialized view log altered.
    
    SQL> create materialized view mv build deferred refresh fast on commit
      2  as
      3  select count(*) cnt
      4    from a join b
      5      on a.email = b.email; Materialized view created. SQL> alter materialized view mv add constraint mv_chk check (cnt = 0); Materialized view altered. SQL> exec dbms_mview.refresh('MV','C') PL/SQL procedure successfully completed. SQL> insert into a values (1, '[email protected]'); 1 row created. SQL> insert into b values (1, '[email protected]'); 1 row created. SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-12008: error in materialized view refresh path
    ORA-02290: check constraint (V195588.MV_CHK) violated SQL> rollback; Rollback complete. SQL> insert into a values (1, '[email protected]'); 1 row created. SQL> insert into b values (1, '[email protected]'); 1 row created. SQL> commit; Commit complete. SQL> select * from a;         ID
    ----------
    EMAIL
    ------------------------------------------------------
             1
    [email protected]
    SQL> select * from b;         ID
    ----------
    EMAIL
    ------------------------------------------------------
             1
    [email protected]
    SQL> select * from mv;        CNT
    ----------
             0 SQL>
    Celal Özdemir
«1

Answers

  • Lothar Flatz
    Lothar Flatz Member Posts: 681 Silver Badge
    edited June 2015

    Hi Salim,

    you should review your database design. Given the normalization rules of Dr. Codd it is unlikely that such situation should exist. Database normalization - Wikipedia, the free encyclopedia

    Apart from that the answer could be a trigger, but that would be curing the symptom and not the disease.

    invalidsearch2779499
  • SalimWer
    SalimWer Member Posts: 224 Bronze Badge
    edited June 2015

    Hi Lother

    Thanks for you reply, I Totally agree with you that in normalized database this case should not happen

    But sometimes in legacy systems you don't have much control over your database design.

    a trigger is one of the solutions to implement this feature but I always use triggers as a last solution,

    another method that I am researching now is using materialized views.

    Thanks a lot

    Salim

  • Niranjana Hulikeremath -Oracle
    edited June 2015

    Hello Salim,

    How about creating a view on table B and not to expose email column in that. But this means, users may not insert or update data into table B at all.

    I am not sure if that is something you are expecting. Otherwise, I think trigger is what can do this job for you.

    Thanks

    Niru

  • Celal Özdemir
    Celal Özdemir Member Posts: 173
    edited June 2015

    apart  from lothar flatz says ,

    this can be done through trigger ,

    it can be done through Table C .

      - Table C will be  union All table A's email  and Table B's email .

      - email is Primary Key of Table C 

      - at trigger on Table A and Table B   ,this emails insert to Table C .

    Table C's PK (email) will be provide uniqueness.

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited June 2015

    the data model is indeed questionable - but you could use a materialized view to ensure the uniqueness. The MView would be defined as a UNION ALL over both tables and you would create a unique index on the email solumn.

    ManikWilliam Robertson
  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy
    edited June 2015
    Martin Preiss wrote:
    
    ...you could use a materialized view to ensure the uniqueness. The MView would be defined as a UNION ALL over both tables and you would create a unique index on the email solumn.
    
    
    

    Oracle says: "I'm sorry, Martin, I'm afraid I can't do that." The "classic" way is to create a join-based MV that should be empty, then raise an error when it is not.

    Note to OP: the error must be raised on commit, because that is when the MV is refreshed.

    > create table t(email varchar2(64) primary key)

    /

    table T created.

    > create materialized view log on t with rowid

    /

    materialized view LOG created.

    > create table u(email varchar2(64) primary key)

    /

    table U created.

    > create materialized view log on u with rowid

    /

    materialized view LOG created.

    > create materialized view tu refresh fast on commit as

    select rowid rid, email from t

    union all

    select rowid rid, email from u

    /

    Error starting at line : 1 in command -

    create materialized view tu refresh fast on commit as

    select rowid rid, email from t

    union all

    select rowid rid, email from u

    Error at Command Line : 15 Column : 30

    Error report -

    SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"

    *Cause:    The materialized view did not satisfy conditions for refresh at

              commit time.

    *Action:  Specify only valid options.

    > create materialized view tu refresh fast on commit as

    select t.rowid trid, u.rowid urid

    from t join u using(email)

    /

    materialized view TU created.

    > alter materialized view tu add constraint same_email_in_t_and_u_not check(1=0) DEFERRABLE

    /

    materialized view TU altered.

    > insert into t values('[email protected]')

    /

    1 rows inserted.

    > insert into u values('[email protected]')

    /

    1 rows inserted.

    > commit

    /

    Error starting at line : 24 in command -

    commit

    Error report -

    SQL Error: ORA-12008: error in materialized view refresh path

    ORA-02290: check constraint (STEW.SAME_EMAIL_IN_T_AND_U_NOT) violated

    12008. 00000 -  "error in materialized view refresh path"

    *Cause:    Table SNAP$_<mview_name> reads rows from the view

              MVIEW$_<mview_name>, which is a view on the master table

              (the master may be at a remote site).  Any

              error in this path will cause this error at refresh time.

              For fast refreshes, the table <master_owner>.MLOG$_<master>

              is also referenced.

    *Action:  Examine the other messages on the stack to find the problem.

              See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,

              <mowner>.<master>@&lt;dblink>, <mowner>.MLOG$_<master>@&lt;dblink>

              still exist.

    Celal ÖzdemirsgalaxySalimWerChris Saxon-Oracle
  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy
    edited June 2015
    Celal Özdemir wrote:
    
    this can be done through trigger ,
    
    it can be done through Table C .
      - email is Primary Key of Table C  
      - at trigger on Table A and Table B   ,this emails insert to Table C .
    
    Table C's PK (email) will be provide uniqueness.
    

    Triggers are almost always very hard to do correctly.

    At first glance, this one seems OK. Table C just needs the email, nothing else. It can also be an Index Organized Table (IOT), so there is just the index segment to manage.

    One advantage to this solution is that the constraint can be either immediate or deferrable, depending on what the OP wants. The MV solution can only be deferrable.

    Celal Özdemir
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited June 2015 Accepted Answer

    Something like this I guess

    SQL> create table a (id integer, email varchar2(100));
    
    Table created.
    
    SQL> alter table a add constraint a_uk unique(email);
    
    Table altered.
    
    SQL> create table b (id integer, email varchar2(100));
    
    Table created.
    
    SQL> alter table b add constraint b_uk unique(email);
    
    Table altered.
    
    SQL> create materialized view log on a with rowid including new values;
    
    Materialized view log created.
    
    SQL> create materialized view log on b with rowid including new values;
    
    Materialized view log created.
    
    SQL> alter materialized view log on b add (email);
    
    Materialized view log altered.
    
    SQL> alter materialized view log on a add (email);
    
    Materialized view log altered.
    
    SQL> create materialized view mv build deferred refresh fast on commit
      2  as
      3  select count(*) cnt
      4    from a join b
      5      on a.email = b.email; Materialized view created. SQL> alter materialized view mv add constraint mv_chk check (cnt = 0); Materialized view altered. SQL> exec dbms_mview.refresh('MV','C') PL/SQL procedure successfully completed. SQL> insert into a values (1, '[email protected]'); 1 row created. SQL> insert into b values (1, '[email protected]'); 1 row created. SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-12008: error in materialized view refresh path
    ORA-02290: check constraint (V195588.MV_CHK) violated SQL> rollback; Rollback complete. SQL> insert into a values (1, '[email protected]'); 1 row created. SQL> insert into b values (1, '[email protected]'); 1 row created. SQL> commit; Commit complete. SQL> select * from a;         ID
    ----------
    EMAIL
    ------------------------------------------------------
             1
    [email protected]
    SQL> select * from b;         ID
    ----------
    EMAIL
    ------------------------------------------------------
             1
    [email protected]
    SQL> select * from mv;        CNT
    ----------
             0 SQL>
    Celal Özdemir
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited June 2015

    Stew,

    yes, that sounds and looks convincing...

    Maybe I could have checked it before suggesting: but I was sure that the solution contained MViews...

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited June 2015

    Hi

    I have a column email1 in two tables A and B.
    and I don't want to allow to insert the email twice in both tables, the email should either exists in table A or in table B not both.
    
    Is there an easy way to implement this feature ?
    

    no there is not. Why do you care if your data model allows that? Change data model or stop caring .

  • Lothar Flatz
    Lothar Flatz Member Posts: 681 Silver Badge
    edited June 2015

    Hi Salim,

    I think a MVIEW would be overkill. You need all the mview logs and the synchronizing just for one check.

    I think Codd would suggest a regular view because that was his solution for logical transformations. Well, still that does not give me a solution.

    Would it be possible do define one tabla as the only source of the email column? Let's say table A form the sake of the argument. Than the column in table B could be ignored.

    SalimWer
  • SalimWer
    SalimWer Member Posts: 224 Bronze Badge
    edited June 2015

    Hi Lothar

    Yes I think I should consider one of the tables as source table , or allow some duplication in this stage and handle it in later stage.

    Regards

    Salim

  • Lothar Flatz
    Lothar Flatz Member Posts: 681 Silver Badge
    edited June 2015

    If you have something like a staging area that could be a valid approach.

  • Chris Saxon-Oracle
    Chris Saxon-Oracle Member Posts: 21 Employee
    edited June 2015

    Building on Celal's solution, it is possible to do this declaratively without needing a materialized view. The basic approach is:

    - Create a third table as a master list of email addresses. Also have a discriminator/type column on this table.

    - The primary key of this table is email_address. Also create a unique key over (email_address, type).

    - Add type columns to tables A and B.

    - Add check constraints to validate that these columns can only be type A or B respectively

    - Add foreign keys on A & B back to the master on (email_address, type).

    - Done!

    - You can now only insert rows into A or B that correspond to the type given in the master table.

    create table email_master (
      email_address varchar2(320) not null,
      email_type varchar2(10) not null 
        check (email_type in ('TABLEA', 'TABLEB')),
      constraint email_master_pk primary key (email_address),
      constraint email_master_u unique (email_address, email_type)
    );
    
    create table tablea (
      email_address varchar2(320) not null primary key
        references email_master (email_address),
      email_type varchar2(10) not null 
        check (email_type = 'TABLEA'),
      stuff varchar2(100),
      constraint taba_email_fk foreign key (
        email_address, email_type
      ) references email_master (email_address, email_type)
    );
    
    create table tableb (
      email_address varchar2(320) not null primary key
        references email_master (email_address),
      email_type varchar2(10) not null 
        check (email_type = 'TABLEB'),
      stuff varchar2(100),
      constraint tabb_email_fk foreign key (
        email_address, email_type
      ) references email_master (email_address, email_type)
    );
    
    insert into email_master values ('[email protected]', 'TABLEA');
    insert into email_master values ('[email protected]', 'TABLEB');
    insert into email_master values ('[email protected]', 'TABLEA');
    
    insert into tablea values ('[email protected]', 'TABLEA', 'stuff');
    insert into tableb values ('[email protected]', 'TABLEB', 'stuff');
    insert into tablea values ('[email protected]', 'TABLEA', 'stuff');
    
    insert into tableb values ('[email protected]', 'TABLEA', 'stuff'); ORA-02290: check constraint (USER_4_368F6.SYS_C00817948) violated insert into tableb values ('[email protected]', 'TABLEB', 'stuff'); ORA-02291: integrity constraint (USER_4_39CAFF.TABB_EMAIL_FK) violated - parent key not found

    If you're not able to modify the application code (quickly), you may need to add insert/update triggers on A & B to populate the master table.

    I'm not sure which normal form distributed uniqueness violates - it's compatible up to 5NF as far as I can see. Hugh Darwen proposes something similar to handle missing information (i.e. get rid of nulls): http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf

    William Robertson
  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy
    edited June 2015

    Chris,

    Nice! To implement this transparently, I thought of using a few new database features. These are new ideas to me, so feedback appreciated.

    First, instead of two indexes use one non-unique index to police both constraints:

    create table email_master (
      email_address varchar2(320),
      email_type varchar2(10) check (email_type in ('TABLEA', 'TABLEB')),
      constraint email_master_pk primary key (email_address, email_type)
        using index (
          create index email_master_pk on email_master (email_address, email_type)
        ),
      constraint email_master_address_u unique (email_address) using index email_master_pk
    );
    
    

    Second, make the "email-type" columns in TABLEA and TABLEB invisible and virtual.

    create table tablea (
      email_address varchar2(320) primary key references email_master (email_address),
      email_type varchar2(10) invisible generated always as ('TABLEA'),
      stuff varchar2(100),
      constraint taba_email_fk foreign key (email_address, email_type) references email_master
    );
    
    create table tableb (
      email_address varchar2(320) primary key references email_master (email_address),
      email_type varchar2(10) invisible generated always as ('TABLEB'),
      stuff varchar2(100),
      constraint tabb_email_fk foreign key (email_address, email_type) references email_master
    );
    
    

    Now we need triggers to manage the master table.

    CREATE OR REPLACE TRIGGER tablea_email
      BEFORE INSERT OR DELETE OR UPDATE OF email_address
      ON tablea
      for each row
    DECLARE
    BEGIN
      if UPDATING or DELETING then
        delete from email_master where (email_address, email_type) = ((:old.email_address, 'TABLEA'));
      end if;
      if UPDATING or INSERTING then
          insert into email_master (email_address, email_type) values(:new.email_address, 'TABLEA');
      end if;
    END;
    /
    
    CREATE OR REPLACE TRIGGER tableb_email
      BEFORE INSERT OR DELETE OR UPDATE OF email_address
      ON tableb
      for each row
    DECLARE
    BEGIN
      if UPDATING or DELETING then
        delete from email_master where (email_address, email_type) = ((:old.email_address, 'TABLEB'));
      end if;
      if UPDATING or INSERTING then
          insert into email_master (email_address, email_type) values(:new.email_address, 'TABLEB');
      end if;
    END;
    /
    

    Now we can do DML on TABLEA and TABLEB without knowing that the email_type column exists.

    > insert into tablea values('[email protected]', 'stuff')
    /
    1 rows inserted.
    
    > insert into tablea values('[email protected]', 'stuff')
    /
    ...
    SQL Error: ORA-00001: unique constraint (STEW.EMAIL_MASTER_PK) violated
    ORA-06512: at "STEW.TABLEA_EMAIL", line 7
    ORA-04088: error during execution of trigger 'STEW.TABLEA_EMAIL'
    
    > insert into tableb values('[email protected]', 'stuff')
    /
    ...
    SQL Error: ORA-00001: unique constraint (STEW.EMAIL_MASTER_PK) violated
    ORA-06512: at "STEW.TABLEB_EMAIL", line 7
    ORA-04088: error during execution of trigger 'STEW.TABLEB_EMAIL'
    
    > update tablea set email_address = '[email protected]'
    /
    1 rows updated.
    
    > insert into tableb values('[email protected]', 'stuff')
    /
    1 rows inserted.
    
    > delete from tableb where email_address = '[email protected]'
    /
    1 rows deleted.
    
    > insert into tablea values('[email protected]', 'stuff')
    /
    1 rows inserted.
    
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited June 2015

    Any specific reason to have email_type?

    SQL> create table email_master
      2  (
      3  email_address varchar2(320) primary key
      4  ); Table created. SQL> create table tablea
      2  (
      3  email_address varchar2(320) primary key references email_master (email_address)
      4  ); Table created. SQL> create table tableb (
      2  email_address varchar2(320) primary key references email_master (email_address)
      3  ); Table created. SQL> create or replace trigger tablea_email
      2    before insert or delete or update of email_address
      3    on tablea
      4    for each row
      5  declare
      6  begin
      7    if updating or deleting then
      8      delete from email_master where email_address = :old.email_address;
      9    end if;
    10    if updating or inserting then
    11        insert into email_master (email_address) values(:new.email_address);
    12    end if;
    13  end;
    14  / Trigger created. SQL> create or replace trigger tableb_email
      2    before insert or delete or update of email_address
      3    on tableb
      4    for each row
      5  declare
      6  begin
      7    if updating or deleting then
      8      delete from email_master where email_address = :old.email_address;
      9    end if;
    10    if updating or inserting then
    11        insert into email_master (email_address) values(:new.email_address);
    12    end if;
    13  end;
    14  / Trigger created. SQL> insert into tablea values ('[email protected]'); 1 row created. SQL> insert into tablea values ('[email protected]');
    insert into tablea values ('[email protected]')
                *
    ERROR at line 1:
    ORA-00001: unique constraint (V195588.SYS_C00674492) violated
    ORA-06512: at "karthick.TABLEA_EMAIL", line 7
    ORA-04088: error during execution of trigger 'karthick.TABLEA_EMAIL'
    SQL> select * from email_master; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> select * from tablea; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> insert into tableb values ('[email protected]');
    insert into tableb values ('[email protected]')
                *
    ERROR at line 1:
    ORA-00001: unique constraint (V195588.SYS_C00674492) violated
    ORA-06512: at "karthick.TABLEB_EMAIL", line 7
    ORA-04088: error during execution of trigger 'karthick.TABLEB_EMAIL'
    SQL> insert into tableb values ('[email protected]'); 1 row created. SQL> select * from email_master; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected]
    [email protected] SQL> select * from tablea; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> select * from tableb; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> update tablea set email_address = '[email protected]';
    update tablea set email_address = '[email protected]'
           *
    ERROR at line 1:
    ORA-00001: unique constraint (V195588.SYS_C00674492) violated
    ORA-06512: at "karthick.TABLEA_EMAIL", line 7
    ORA-04088: error during execution of trigger 'karthick.TABLEA_EMAIL'
    SQL> update tablea set email_address = '[email protected]'; 1 row updated. SQL> select * from email_master; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected]
    [email protected] SQL> select * from tablea; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> select * from tableb; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> delete from tableb; 1 row deleted. SQL> select * from email_master; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> select * from tablea; EMAIL_ADDRESS
    --------------------------------------------------------------------------------
    [email protected] SQL> select * from tableb; no rows selected SQL>

    Stew Ashton
  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy
    edited June 2015

    Karthick,

    Honestly, I logged back in to ask that very question! I was so eager to apply a few new features to Chris' solution that I forgot to ask "why".

    Karthick2003
  • ReemaPuri
    ReemaPuri Member Posts: 1,358 Gold Badge
    edited June 2015

    Just tried the trigger method if you want to have i think little late  but okay

    create table first

    (

    email_id1 varchar2(100)

    );

    create table second

    (

    email_id2 varchar2(100)

    )

    CREATE or replace TRIGGER unique_first BEFORE INSERT ON first

    FOR EACH ROW

      DECLARE

    c number;

    d number;

       user_xcep EXCEPTION;

       PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

    begin

    SELECT COUNT(email_id2) INTO C FROM second

    WHERE email_id2=:NEW.email_id1;

    SELECT COUNT(email_id1) INTO d FROM first

    WHERE email_id1=:NEW.email_id1;

    --abort inserting

    IF (C>0 or d>0) THEN

    raise user_xcep;

    END IF;

    END;

    CREATE or replace TRIGGER unique_second BEFORE INSERT ON second

    FOR EACH ROW

      DECLARE

    c number;

    d number;

       user_xcep EXCEPTION;

       PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

    begin

    SELECT COUNT(email_id1) INTO C FROM first

    WHERE email_id1=:NEW.email_id2;

    SELECT COUNT(email_id2) INTO d FROM second

    WHERE email_id2=:NEW.email_id2;

    --abort inserting

    IF (C>0 or d>0 ) THEN

    raise user_xcep;

    END IF;

    END;

  • SalimWer
    SalimWer Member Posts: 224 Bronze Badge
    edited June 2015

    Hi

    For the email case, your solution will work fine as long no updates are allowed to the emails.

    if ON updates is add to the triggers then , we will face the mutating problem,

    So I think we can add unique index on email columns in each table , and change the triggers to only check if the email exists in the other table.

    this way we will not have the mutating problem

    Regards

    Salim

  • ReemaPuri
    ReemaPuri Member Posts: 1,358 Gold Badge
    edited June 2015

    yes forgot to write we need to write trigger for update too

  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy
    edited June 2015

    How are you going to check if the email exists in the other table? Someone could have inserted that email and not committed yet. (*)

    The mutating table thing is not a problem to work around: it is a reminder we are not the only ones using the database.

    We need some third thing, either an ON COMMIT materialized view or a table, with all the email addresses and a unique constraint.

    (*) There might be a way: insert the email in the other table, then delete it. As long as you don't commit or rollback no one else can insert that email address into the other table. Just don't try that in your triggers, because they will call each other and cause a "recursive SQL" error.

    OOPS! My idea of inserting into the other table, then deleting can cause deadlocks! Got to have that third table/MV.

  • SalimWer
    SalimWer Member Posts: 224 Bronze Badge
    edited June 2015

    The triggers should not be removed , but replace the select count() that check if the value exists in same table with unique index and keep the select count() that check the if the value exists in the other table

    if you make the trigger to fire ON UPDATE and select from the same table of the trigger you will receive the following error

    One error saving changes to table "LAB"."SECOND":

    Row 3: ORA-04091: table LAB.SECOND is mutating, trigger/function may not see it

    ORA-06512: at "LAB.UNIQUE_SECOND", line 7

    ORA-04088: error during execution of trigger 'LAB.UNIQUE_SECOND'

    I updated the solution provided by REEMA to be as follows

    DROP TABLE FIRST;

    CREATE TABLE FIRST

      ( email_id1 VARCHAR2(100) unique

      );

    DROP TABLE second;

    CREATE TABLE second

      ( email_id2 VARCHAR2(100) unique

      ) ;

    CREATE OR REPLACE TRIGGER unique_first BEFORE

      INSERT OR UPDATE ON FIRST FOR EACH ROW

    DECLARE

      c         NUMBER;

      d         NUMBER;

      user_xcep EXCEPTION;

      PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

      BEGIN

        SELECT COUNT(email_id2) INTO C FROM second WHERE email_id2=:NEW.email_id1;

        --abort inserting

        IF (C>0) THEN

          raise user_xcep;

        END IF;

      END;

      /

    CREATE OR REPLACE TRIGGER unique_second BEFORE

      INSERT OR UPDATE ON second FOR EACH ROW

    DECLARE

      c         NUMBER;

      d         NUMBER;

      user_xcep EXCEPTION;

      PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

      BEGIN

        SELECT COUNT(email_id1) INTO C FROM FIRST WHERE email_id1=:NEW.email_id2;

        --abort inserting

        IF (C>0) THEN

          raise user_xcep;

        END IF;

      END;

      /

  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy
    edited June 2015

    Session A:

    > insert into first values('@1');

    Session B:

    > insert into second values('@1');

    Session A:

    > commit;

    Session B:

    > commit;

    Session A:

    >

    select * from first

    union all

    select * from second;

    EMAIL_ID1
    @1
    @1
    SalimWer
  • SalimWer
    SalimWer Member Posts: 224 Bronze Badge
    edited June 2015

    yes got it, solution will not work

  • ReemaPuri
    ReemaPuri Member Posts: 1,358 Gold Badge
    edited June 2015

    i was saying to create separate update trigger it will work

    CREATE TABLE first

      ( email_id1 VARCHAR2(100)

      ) ;

    CREATE TABLE second

      ( email_id2 VARCHAR2(100)

      ) ;

    CREATE OR REPLACE TRIGGER unique_first BEFORE

      INSERT  ON FIRST FOR EACH ROW

    DECLARE

      c         NUMBER;

      d         NUMBER;

      user_xcep EXCEPTION;

      PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

      BEGIN

        SELECT COUNT(email_id2) INTO C FROM second WHERE email_id2=:NEW.email_id1;

        --abort inserting

        IF (C>0) THEN

          raise user_xcep;

        END IF;

      END;

      /

    CREATE OR REPLACE TRIGGER unique_first1 BEFORE

      update  ON FIRST FOR EACH ROW

    DECLARE

      c         NUMBER;

      d         NUMBER;

      user_xcep EXCEPTION;

      PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

      BEGIN

        SELECT COUNT(email_id2) INTO C FROM second WHERE email_id2=:NEW.email_id1;

        --abort inserting

        IF (C>0) THEN

          raise user_xcep;

        END IF;

      END;

      /

    CREATE OR REPLACE TRIGGER unique_second BEFORE

      INSERT ON second FOR EACH ROW

    DECLARE

      c         NUMBER;

      d         NUMBER;

      user_xcep EXCEPTION;

      PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

      BEGIN

        SELECT COUNT(email_id1) INTO C FROM FIRST WHERE email_id1=:NEW.email_id2;

        --abort inserting

        IF (C>0) THEN

          raise user_xcep;

        END IF;

      END;

      /

    CREATE OR REPLACE TRIGGER unique_second1 BEFORE

      update ON second FOR EACH ROW

    DECLARE

      c         NUMBER;

      d         NUMBER;

      user_xcep EXCEPTION;

      PRAGMA EXCEPTION_INIT( user_xcep, -20001 );

      BEGIN

        SELECT COUNT(email_id1) INTO C FROM FIRST WHERE email_id1=:NEW.email_id2;

        --abort inserting

        IF (C>0) THEN

          raise user_xcep;

        END IF;

      END;

      /

This discussion has been closed.