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.
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);
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.