Thanks for the question, SABAT.

Asked: April 04, 2016 - 4:18 pm UTC

Last updated: December 18, 2018 - 1:54 am UTC

Version: 11G

Viewed 100K+ times! This question is

You Asked

I HAVE 1 table

Create table TB_COBA1 (ID NUMBER , NIS NUMBER , NILAI_A NUMBER , semester number);

Create table TB_COBA2 (ID NUMBER , NIS NUMBER , NILAI_b NUMBER , semester number);

SQL> Insert into TB_COBA1 values (1,1,1,1);
SQL> Insert into TB_COBA1 values (1,2,2,2);

when i insert on tb_coba1 there will insert automatic on tb_coba2 , but it will be not insert automatic on tb_coba2 when new.nis and new.semester is exists

and my trigger

create or replace trigger t_cb
after insert on tb_coba1
for each row
begin
IF NOT not exists (select * from tb_coba2 where nis = :new.nis and semester = :new.semester)
THEN
insert into tb_cb2 (nis , nilai_b , semester)
values (:new.nis , :new.nilai_a , new.semester) ;
END IF;
end;

please help me , Thanks so much for your help.

and Connor said...

OK, first check this solution

SQL> create table tb_coba1 (id number , nis number , nilai_a number , semester number);

Table created.

SQL> create table tb_coba2 (id number , nis number , nilai_b number , semester number);

Table created.

SQL> insert into tb_coba1 values (1,1,1,1);

1 row created.

SQL> insert into tb_coba1 values (1,2,2,2);

1 row created.

SQL>
SQL> create or replace trigger t_cb
  2  after insert on tb_coba1
  3  for each row
  4  begin
  5    insert into TB_COBA2 (nis , nilai_b , semester)
  6    select :new.nis , :new.nilai_a , :new.semester
  7    from dual
  8    where not exists (
  9      select 1 from tb_coba2
 10      where nis = :new.nis and semester = :new.semester
 11      );
 12  end;
 13  /

Trigger created.

SQL> sho err
No errors.
SQL>
SQL> insert into TB_COBA1 values (3,3,3,3);

1 row created.

SQL> select * from tb_coba2;

        ID        NIS    NILAI_B   SEMESTER
---------- ---------- ---------- ----------
                    3          3          3

SQL> insert into TB_COBA1 values (4,3,3,3);

1 row created.

SQL> select * from tb_coba2;

        ID        NIS    NILAI_B   SEMESTER
---------- ---------- ---------- ----------
                    3          3          3

That *looks* like it will work...but its actually wrong. Because what if TWO people do that insert at the same time. Each will NOT see the others insert (because we have not committed yet), so you will STILL end up with 2 occurrence of "3,3" in the table.

How do we fix that ?

Option 1 - make the nis/semester unique on TB_COBA2 using a constraint. Then our trigger has to take care of the potential error that might then come back

SQL> alter table tb_coba2 add constraint tb_coba2_uq unique ( nis, semester );

Table altered.

SQL> create or replace trigger t_cb
  2  after insert on tb_coba1
  3  for each row
  4  begin
  5    insert into TB_COBA2 (nis , nilai_b , semester)
  6    select :new.nis , :new.nilai_a , :new.semester
  7    from dual
  8    where not exists (
  9      select 1 from tb_coba2
 10      where nis = :new.nis and semester = :new.semester
 11      );
 12  exception
 13    when dup_val_on_index then null;
 14  end;
 15  /

Trigger created.

Now if two people try to insert the same data, one will wait until the other is finished (committed).

If you dont want a constraint, then you can be more brutal and lock the whole table when you insert, ie,

SQL> create or replace trigger t_cb
  2  after insert on tb_coba1
  3  for each row
  4  begin
  5    lock table TB_COBA2 in exclusive mode;
  6
  7    insert into TB_COBA2 (nis , nilai_b , semester)
  8    select :new.nis , :new.nilai_a , :new.semester
  9    from dual
 10    where not exists (
 11      select 1 from tb_coba2
 12      where nis = :new.nis and semester = :new.semester
 13      );
 14  end;
 15  /

Trigger created.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

something i can't understand in the query

ayah mamdouh, December 17, 2018 - 11:48 am UTC

please
what do you mean by
select 1 ???

in where not exists (select 1 from tb_coab2 ) ?

December 18, 2018 - 1:54 am UTC

When you are doing an existence check, then we only care if the query returns a row - we don't care what is *in* the row, just whether one comes back.

select some_columns
from   my_table
where exists ( 
   select 1
   from   some_other_table
)

is simply saying "I want data from MY_TABLE but only if a row EXISTS in SOME_OTHER_TABLE". I don't care *what* rows are SOME_OTHER_TABLE, or what they contain, just that there are (or are not) some rows.

So I could use:

where exists ( select 1 ... )

where exists ( select 'X' ... )

where exists ( select null ... )

It doesn't matter, because I'll never *use* that value.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

something i can't understand in the query

More to Explore

PL/SQL demos

PL/SQL docs

INSERT IF NOT EXISTS

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

something i can't understand in the query

More to Explore

PL/SQL demos

PL/SQL docs