SQL & PL/SQL

I was curious whether using a trigger restriction was somehow faster than using the same test inside the trigger itself, perhaps because it avoids a context switch or something, but I can't seem to be able to create the trigger with a when clause...

The example from DB Concept, Figure 22-2, uses

AFTER UPDATE OF parts_on_hand ON inventory
WHEN (new.parts_on_hand < new.reorder_point)
FOR EACH ROW ...

and my attempt below looks similar, except it's not column specific. They are both "for each row" triggers.

Did I get the syntax wrong, or is this really not possible? And if so why?

Am I missing a way to implement what the trigger checks using referential integrity or indexes?

This is a non-production example to get a feel for the cost of referential integrity and triggers, yet I'd still like to go to the bottom of it. The vrtx? columns from the triangle table have FKs back to vrtx_tab(vrtx_id), the vertex table. 11g on windows.

Thanks, --DD

SQL> create or replace trigger t1
  2  before insert on trgl_tab
  3  when (
  4      NEW.vrtx1 = NEW.vrtx2 or
  5      NEW.vrtx1 = NEW.vrtx3 or
  6      NEW.vrtx2 = NEW.vrtx3
  7  ) for each row
  8  begin
  9      raise_application_error(-20001, 'degenerate triangle');
 10  end;
 11  /
when (
*
ERROR at line 3:
ORA-04077: WHEN clause cannot be used with table level triggers


SQL> create or replace trigger t1
  2  before insert on trgl_tab
  3  for each row
  4  begin
  5  if
  6      :NEW.vrtx1 = :NEW.vrtx2 or
  7      :NEW.vrtx1 = :NEW.vrtx3 or
  8      :NEW.vrtx2 = :NEW.vrtx3
  9  then
 10      raise_application_error(-20001, 'degenerate triangle');
 11  end if;
 12  end;
 13  /

Trigger created.

SQL> desc trgl_tab
 Name                                            Null?    Type
 ----------------------------------------------- -------- ------------

 TRGL_ID                                         NOT NULL NUMBER(10)
 FACE_ID                                         NOT NULL NUMBER(10)
 VRTX1                                           NOT NULL NUMBER(10)
 VRTX2                                           NOT NULL NUMBER(10)
 VRTX3                                           NOT NULL NUMBER(10)