DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: trigger w/ subquery

  1. #1
    Join Date
    Mar 2001
    Posts
    16
    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.


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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....?


  3. #3
    Join Date
    Mar 2001
    Posts
    16
    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 .




  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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;/

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    insert into track values (user, sysdate, sys_context('userenv', 'os_user'))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width