is it possible to create a trigger with a subquery ?
i have a table "track" with fields (action,action_date,user,os_user)
CREATE or replace tirgger score_track
AFTER insert or update or delete on score
if inserting then
insert into track values
('insert',sysdate,user,(select osuser from v$session where schemaname in (select user from dual)));
i keep getting a PLS-00103.
select osuser from v$session where schemaname in (select user from dual
what if that returns more than one row? how are you going to insert it....?
this query: select osuser from v$session where schemaname in (select user from dual) returns only the current user ....as far as i know. if you can suggest a better way to get the current user...please do .
select username from v$session where username = user;
but what if there are more than one user with same username login in? You will get two, three or even more rows
something would be like
create or replace trigger logon
insert into logon values(user, sysdate);
insert into track values (user, sysdate, sys_context('userenv', 'os_user'))