oracle11g - Trigger Compilation Error, Oracle 11g -

banging head against 1 while. i'm constructing database on oracle 11g, , attempting insert record "registry" table whenever record created on "data product" table. registry table needs auto-increment product_id, , product_id used foreign key on data product table. here trigger code:

create or replace trigger "tr_camera_dp_dpr_create" before insert on "dd1"."camera_dp" each row begin   :new.product_id := id_seq.nextval;    insert dd1.dp_registry          (    product_id,               fs_location,               parent_group_id,               product_name,               shortdes,               createdate,               revision )          values          (    :new.product_id,               'placeholder',               0,               '_image',               'description placeholder',               sysdate,               0           );  end; 

so, ideally, insert dd1.camera_dp without providing product_id first insert record dd1.dp_registry, , use incremented product_id key field dd1.camera_dp.

the insert statement works when run hard-coded value :new.product_id, , id_seq.nextval working properly. feeling i'm missing obvious.

thanks!

your code works me. if you're getting error, there code running code posted.

sql> create table camera_dp(   2    product_id number,   3    name varchar2(10)   4  );  table created.  sql> create sequence id_seq;  sequence created.  sql> ed wrote file afiedt.buf    1  create table dp_registry   2           (    product_id number,   3                fs_location varchar2(100),   4                parent_group_id number,   5                product_name varchar2(100),   6                shortdes varchar2(100),   7                createdate date,   8*               revision number) sql> /  table created.  sql> ed wrote file afiedt.buf    1  create or replace trigger "tr_camera_dp_dpr_create"   2  before insert on "camera_dp"   3  each row   4  begin   5    :new.product_id := id_seq.nextval;   6    insert dp_registry   7           (    product_id,   8                fs_location,   9                parent_group_id,  10                product_name,  11                shortdes,  12                createdate,  13                revision )  14           values  15           (    :new.product_id,  16                'placeholder',  17                0,  18                '_image',  19                'description placeholder',  20                sysdate,  21                0  22            );  23* end;  24  /  trigger created.  sql> insert camera_dp( name ) values( 'foo' );  1 row created.  sql> ed wrote file afiedt.buf    1* select product_id dp_registry sql> /  product_id ----------          1 

if you're getting error table doesn't exist, common culprits be

• you have typo in name of table
• you don't have permission insert table. note if in actual code, not in same schema, guess user owns trigger has privileges insert dp_registry table via role rather via direct grant. since priileges granted through role not available in definer's rights stored procedure block, explain why can @ command line not in pl/sql.