I would appreciate any help as I am new to PL/SQL and have run out of ideas.
I am trying to write a trigger that when a user inserts a row into a table that records an ID, a clinic, and user initials, a second table is updated with that ID in the next available "slot" for that clinic.
I wrote a procedure that does execute successfully with ID, user initials, and clinic hardcoded. I cannot create a trigger using the fields from the 1st table instead of the hardcoded values that compiles without errors.
The tables in question are in another schema, but I have all priviledges on that schema.
I have tried referencing the fields from table 1 in every way I can think of:
1) :new.<field from table 1> and got:
ORA-04082: NEW or OLD references not allowed in table level triggers
2) :<field from table 1> and got
PLS-00049: bad bind variable '<field name>'
3) <field from table 1> , <table>.<field from table 1>, <schema>.<table>.<field from table> and got
5/38 PL/SQL: ORA-00904: "N106CLINIC": invalid identifier
14/44 PL/SQL: ORA-00904: "N106CLINIC": invalid identifier
and
5/38 PL/SQL: ORA-00904: "N106"."N106CLINIC": invalid identifier
14/44 PL/SQL: ORA-00904: "N106"."N106CLINIC": invalid identifier
and
5/38 PL/SQL: ORA-00904: "CCRN"."N106"."N106CLINIC": invalid identifier
14/44 PL/SQL: ORA-00904: "CCRN"."N106"."N106CLINIC": invalid identifier
in addition to "SQL statement ignored".
It does complain only about n106clinic, even though other fields referenced from the first table are n106id and n106init; n106clinic IS a valid field name in the n106 table...maybe this is just a compiler peculiarity.
Here is the code for the trigger. N106 is the table where the user inserts a row and RAMBCYL is the table to be updated. CCRN is the schema that owns the two tables.
create or replace trigger ccrn.aocylrand
after insert
on ccrn.n106
declare l_accno number;
begin
select rambcyl.accno into l_accno
from ccrn.rambcyl rambcyl
where rambcyl.clinic=n106clinic and
rambcyl.accno = (select min(r.accno)
from ccrn.rambcyl r
where r.id is null
and r.clinic = n106clinic);
update ccrn.rambcyl set id =n106id,
randdt = sysdate,
staff=n106init
where ccrn.rambcyl.accno = l_accno and
ccrn.rambcyl.clinic = n106clinic;
end aocylrand;
Thanks, Helen