Results 1 to 3 of 3

Thread: PL/SQL Problem

  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Question Unanswered: PL/SQL Problem

    hi,
    i am leaning pl/sql from about one week and i have problem that
    how can i apply a trigger on the table(say emp) as i want the restriction of timing.restriction is that users can only access the emp table during office timing which is 9:00am to 5:00pm. no user can access the table before 9:00am or after 5:00pm.
    i am writing the incomplete code here


    create or replace trigger secure_emp
    before
    insert or update or delete
    on emp
    begin
    if()then
    raise_application_error(-20500,'You may only insert,update or delete during office timings');
    end if;
    end;


    i dont know what to write in the IF statement, so help me out
    regards
    Ismat
    Last edited by Ismat; 08-30-04 at 17:25.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Ismat
    create or replace trigger secure_emp
    before
    insert or update or delete
    on emp
    begin
    if()then
    raise_application_error(-20500,'You may only insert,update or delete during office timings');
    end if;
    end;
    try

    create or replace trigger secure_emp
    before
    insert or update or delete
    on emp
    begin
    if to_char(sysdate,'HH24MI') not between '0800' and '1700' then
    raise_application_error(-20500,'You may only insert,update or delete during office timings');
    end if;
    end;

    However remember one thing, this will stop all modification of emp, but will not stop viewing of emp.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Allthough this is a possible solution for your problem, still it wont prevent users from selecting data from the table, hence the trigger is for DML operations only.

    What I would do in your case is to schedule a job (using dbms_job for an instance) to revoke and grant the proper priviledges to the users (or roles) you would like to prevent access to that table.

Posting Permissions

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