Oracle trigger before insert

Question

here is my sample trigger

create or replace TRIGGER INVOICES_INSERT
BEFORE INSERT ON INVOICES 
FOR EACH ROW
BEGIN
    if :new.INVOICE_TYPE = 'Purchase'
    
    insert into Purchases (ID, Name, Type) Values (:new.ID, :new.Name, :new.Type)
END;

In my sample trigger, I get records both in Invoices and Purchases tables. But if the condition met in IF statement, I want to record data only into Purchases table.

Is it possible to do it in trigger.

Answer 1

You can't do what you wanted, not that way. Why? Because trigger fires before insert on invoices which means that row will be inserted into invoices table regardless of what you put into that trigger. Yes, you can - optionally - insert a row into the purchase table as well, but you can't avoid inserting into invoices.

Saying that you can't do it that way, I meant to say that there is, after all, another way to do it. How? Using a view and an instead of trigger. Here's an example.

Sample tables:

SQL> create table invoices
  2   (id       number,
  3    name     varchar2(10),
  4    type     varchar2(10));

Table created.

SQL> create table purchase
  2   (id       number,
  3    name     varchar2(10),
  4    type     varchar2(10));

Table created.

View, a simple union:

SQL> create or replace view v_invpur as
  2    select id, name, type from invoices
  3    union all
  4    select id, name, type from purchase;

View created.

Instead of trigger: depending on the type, it inserts a row into appropriate table:

SQL> create or replace trigger trg_ioi_vinvpur
  2    instead of insert on v_invpur
  3    for each row
  4  begin
  5    if :new.type = 'invoice' then
  6       insert into invoices (id, name, type)
  7         values (:new.id, :new.name, :new.type);
  8    elsif :new.type = 'purchase' then
  9       insert into purchase (id, name, type)
 10         values (:new.id, :new.name, :new.type);
 11    end if;
 12  end;
 13  /

Trigger created.

Testing:

SQL> insert into v_invpur (id, name, type)
  2    values (1, 'Little', 'purchase');

1 row created.

SQL> select * from invoices;

no rows selected

SQL> select * from purchase;

        ID NAME       TYPE
---------- ---------- ----------
         1 Little     purchase

SQL>

See? There's nothing in the invoices table, but a row is inserted into the purchase table.

Answer 2

There are lots of ways to do this . . . such as if. But a simple way is:

insert into Purchases (ID, Name, Type) 
    select :new.ID, :new.Name, :new.Type
    from dual
    where :new.INVOICE_TYPE = 'Purchase';

That is, the "conditional" logic can just be part of the insert.