trigger w/ subquery

[slothfur]

please help:

is it possible to create a trigger with a subquery ?

i have a table "track" with fields (action,action_date,user,os_user)

my trigger:

CREATE or replace tirgger score_track
AFTER insert or update or delete on score

BEGIN
if inserting then
insert into track values
('insert',sysdate,user,(select osuser from v$session where schemaname in (select user from dual)));
elsif ....

i keep getting a PLS-00103.

[pando]

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....?

[slothfur]

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 .

[pando]

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
begin
insert into logon values(user, sysdate);
commit;
end;/

[pando]

insert into track values (user, sysdate, sys_context('userenv', 'os_user'))