fire trigger after insert in oracle

Question

I'm very new for trigger, now this what i was trying. I've two tables INSERTED and ORDER_INFO, both have the same column name ORDER_ID, ORDER_DATE. I've scenario, where client will be placing his/her order then, order information will be stored into INSERTED table, then by using this trigger, it'll insert into another table ORDER_INFO after satisfying the condition, which has been written.

    create trigger tri_check
AFTER INSERT ON inserted FOR EACH ROW
DECLARE
 v_date DATE;
BEGIN
    SELECT order_date INTO v_date FROM inserted;
if (v_date)< (sysdate + 2) then
 raiserror('You cannot take an order to be delivered less than 2 days from now',16, 1);
else
INSERT INTO orders_info
     ( order_id,order_date)
    VALUES
     (:new.order_id,v_date);
end if;
end;

But, when i'm executing the above trigger, then i'm getting this error.

ERROR at line 8: PL/SQL: SQL Statement ignored
6.     SELECT order_date INTO v_date FROM inserted;
7. if (v_date)< (sysdate + 2) then
8.  raiserror('You cannot take an order to be delivered less than 2 days from now',16, 1);
9. else
10. INSERT INTO orders_info

EDIT

Now, i made the same structure table into SYSTEM user, and got the same error. Table or View does not exist

Need help !! Thanks in advance !!

Answer 1

The message seems to indicate a problem with the 'raiserror' procedure. I'm not familiar with such a procedure in standard PL/SQL - did you mean RAISE_APPLICATION_ERROR? However, and perhaps more to the point, when using a trigger there's no need to do a SELECT from the table. All the data being inserted is available to the trigger. I suggest changing your trigger to be something like the following:

create trigger tri_check
  AFTER INSERT ON inserted
  REFERENCING NEW AS NEW
  FOR EACH ROW
BEGIN
  if :new.ORDER_DATE < sysdate + INTERVAL '2' DAY then
    RAISE_APPLICATION_ERROR(-20000, 'You cannot take an order to be delivered less than 2 days from now');
  else
    INSERT INTO orders_info
      (order_id, order_date)
    VALUES
      (:new.order_id, :new.ORDER_DATE);
  end if;
end TRI_CHECK; 

Share and enjoy.