Oracle Triggers

I have been working on this – I hope someone can point me in the right direction.

 My application has multiple schema’s – that can be helped – all in the same instance.  Each schema will generate buckets of info.  I want to be able to create a single audit table – and capture some information from each of the schema’s into a separate table. But I cant do it – the only way IO can see to do it is by having a remote database.

For example:

if I have two schema’s s1 and s2 and in those schema’s I have a table called table_trail – same layout.  I put a trigger on each table which should update a single audit table in audit_table in schema s3 – say.

Ok, so I have a trigger for each table:

CREATE OR REPLACE TRIGGER atrigger  after insert on table_trail  REFERENCING OLD AS old NEW AS new  FOR EACH ROW begininsert into s3.audit_table values(new.col1, new.col2);

end;

basically doesn’t work on a compile because the trigger cannot see s3.audit_table. 

Why is this ?  I though if various rights were granted then it should be OK ? But I cant get it to work.

I have been able to get the remote database link working ok – so audit_table@remote compiles fine and that works.