Oracle trigger invalid

Question

I'm new to SQL and I'm trying to create a trigger that would insert into an audit table.

create or replace trigger late_ship_insert
  after insert on suborder
  for each row
declare
  employee int;
begin
  select emp_id 
    into employee
    from handles
   where order_no = :new.order_no;
  if :new.actual_ship_date > :new.req_ship_date then
    insert into ship_audit
      values (employee, :new.order_no, :new.suborder_no, :new.req_ship_date, :new.actual_ship_date);
end;

Error:

Warning: execution completed with warning
trigger late_ship_insert Compiled.

But once I try an insert statement it tell me the trigger is not working it to drop it.

Error starting at line 1 in command:
insert into suborder 
    values  ( 8, 3, '10-jun-2012', '12-jul-2012', 'CVS', 3) 
Error at Command Line:1 Column:12
Error report:
SQL Error: ORA-04098: trigger 'COMPANY.LATE_SHIP_INSERT' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

Any idea what is causing this, any help would be greatly appreciated. Thanks!

Do a SHOW ERRORS after you create the trigger and show us the error message. — user330315
– user330315, Commented Jul 19, 2012 at 20:51
I would be surprised to find int type in Oracle... Try number ? — Raphaël Althaus
– Raphaël Althaus, Commented Jul 19, 2012 at 20:52
I think that you're missing an END IF; plus, I think that all the declared variables should be placed on the beginning. — Nir Alfasi
– Nir Alfasi, Commented Jul 19, 2012 at 20:55
Once I create the trigger the :Warning: execution completed with warning trigger late_ship_insert Compiled. I changed int to number but still the same. The problem is when I try an insert statement on suborder it gives me the second error on the post — JProg
– JProg, Commented Jul 19, 2012 at 20:58
@user1354275 - Assuming you are using SQL*Plus, when you create the trigger and get the warning, type show errors. As @a_horse_with_no_name states, that will show you the actual errors and warnings you got when you tried to create the trigger. — Justin Cave
– Justin Cave, Commented Jul 19, 2012 at 20:59

Justin Cave · Accepted Answer · 2012-07-19 20:58:11Z

The error that becomes apparent when you format your code is that your IF statement is missing the END IF

create or replace trigger late_ship_insert
  after insert on suborder
  for each row
declare
  employee int;
begin
  select emp_id 
    into employee
    from handles
   where order_no = :new.order_no;
  if :new.actual_ship_date > :new.req_ship_date then
    insert into ship_audit
      values (employee, :new.order_no, :new.suborder_no, :new.req_ship_date, :new.actual_ship_date);
  end if;
end;

As a general matter, you should always list the columns of the destination table in your INSERT statement rather than relying on the fact that your INSERT statement specifies a value for every column and specifies them in the proper order. That will make your code much more robust since it won't become invalid when someone adds additional columns to the table for example. That would look something like this (I'm guessing at the names of the columns in the ship_audit table)

create or replace trigger late_ship_insert
  after insert on suborder
  for each row
declare
  employee int;
begin
  select emp_id 
    into employee
    from handles
   where order_no = :new.order_no;
  if :new.actual_ship_date > :new.req_ship_date then
    insert into ship_audit( emp_id, order_no, suborder_no, req_ship_date, actual_ship_date )
      values (employee, :new.order_no, :new.suborder_no, :new.req_ship_date, :new.actual_ship_date);
  end if;
end;

Collectives™ on Stack Overflow

Oracle trigger invalid

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related