Create trigger in Oracle 11g

Question

I want to create a trigger in Oracle 11g. The problem is that I want a trigger which runs every time when there is a SELECT statement. Is this possible or is there other way to achieve the same result. This is the PL/SQL block:

CREATE TRIGGER time_check
   BEFORE INSERT OR UPDATE OF users, passwd, last_login ON table
   FOR EACH ROW
   BEGIN
      delete from table where last_login < sysdate - 30/1440;
   END;

I'm trying to implement a table where I can store user data. I want to "flush" the rows which are old than one hour. Are there other alternatives to how I could implement this?

p.s Can you tell me is this PL/SQL block is correct. Are there any mistakes?

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"ADMIN"."USERSESSIONFLUSH"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
   -- Insert PL/SQL code here
   delete from UserSessions where last_login < sysdate - 30/1440;
end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Asia/Nicosia',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Flushes expired user sessions',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"ADMIN"."USERSESSIONFLUSH"', attribute => 'job_priority', value => 5);
sys.dbms_scheduler.set_attribute( name => '"ADMIN"."USERSESSIONFLUSH"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
sys.dbms_scheduler.enable( '"ADMIN"."USERSESSIONFLUSH"' );
END;

I'm trying to implement a table where I can store user data. I want to "flush" the rows which are old than one hour. Can you propose other alternatives how I can implement this? — Peter Penzov
– Peter Penzov, Commented Dec 17, 2011 at 15:42
I edited your question to add what you posted in your comment, since it is relevant. (You can do that yourself next time - make sure your question contains all the relevant information.) — Mat
– Mat, Commented Dec 17, 2011 at 15:51

Mat · Accepted Answer · 2011-12-17 15:56:48Z

3

I'm not aware of a way of having a trigger on select. From the documentation, the only statements you can trigger on are insert/delete/update (and some DDL).

For what you want to do, I would suggest a simpler solution: use the DBMS_SCHEDULER package to schedule a cleanup job every so often. It won't add overhead to your select queries, so it should have less performance impact globally.

You'll find lots of examples in: Examples of Using the Scheduler

answered Dec 17, 2011 at 15:56

Mat

208k41 gold badges407 silver badges423 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Peter Penzov Over a year ago

OK, I agree. I can use SQL statements every time before the pages are loaded or DBMS_SCHEDULER. Which one of then will use less resources?

Mat Over a year ago

It depends on how frequently you need the data purged. If it's only "about an hour", and you don't care if there's a bit more in there, a DBMS_SCHEDULER job (maybe every 15min or so) is going to be relatively cheap. If you need the data purged strictly, then you need to pay the per-page query cost or something similar.

Peter Penzov Over a year ago

I need to run the DBMS_SCHEDULER every minute. Is this heavy operation?

Mat Over a year ago

The part that is potentially expensive is the code you run every minute, the scheduler itself is pretty cheap as long as you don't scheduled too frequently. There are no hard and fast rules to this, you need to try and measure impact on your specific use case.

Peter Penzov Over a year ago

Ok, so I create the PL/SQL block and add it to DBMS_SCHEDULER to execute it every minute.

|

Collectives™ on Stack Overflow

Create trigger in Oracle 11g

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related