Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


firing of trigger on a particular date

Posted on 2001-07-10
Medium Priority
Last Modified: 2012-05-04
there r two tables a and b.
when a row is inserted in table b,
table a should get updated with the values of table b
after 7 days(i.e 7 days after a row is inserted in table b).
i will have to use trigger thats for sure,
but how should i use a trigger bcoz
i want to fire a trigger on date not on any event of a table.

Question by:shaileshpaldiwal
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

bkm earned 400 total points
ID: 6270037
I am not sure whether you can do this thru a Trigger.  But, you can have a scheduled PL/SQL Procedure (using DBMS_JOB package) that runs every day checking for data inserted into table b 7 days ago.  If there are some rows, then update table a accordingly.

Hope this helps!!

Expert Comment

ID: 6270381
if table b needs to be copy of table a seven days ago, then you can make table b a snapshot of table a and set th refresh to be every 7 days.

1. you can create another table, say table c, with a rowid column and a data column.
2. Write a trigger that places rowid of table a and a date stamp into table c.
3. Now write a job that takes rowids from table c and updates table b.
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6270650
A trigger by itself cannot do this.  You can write a PL\SQL procedure that checks all of the recent records in the source table (I assume that the records have a date or timestmp column that records when they were created) and then if the create date is more than 7 days ago, checks the destination table to see if the records is already there.  If not, it can do the insert.

If your tables are very large, and this process is too slow you may be able to make it faster by using the approach afkapur suggested.  Or, add one column to the source table and populate it with a non-null value and add an index on this column.  Then just find these records, insert them into the taget table when they need to be, and update the source record, setting this indicator column to null (to keep the index small).
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 6271120
I'm not sure why you wish to move data in this fashion.  Could you tell us more about the application you are trying to create?

Chances are that you could get the results you want by having a field on table a that stores the date the row was inserted or updated.  You can populate this date with a simple table trigger.  Any application that needs to see if this row was inserted/modified 7 days ago can simply do a check for (date_row_modified + 7) > SYSDATE.  Physically moving data after 7 days seems odd, when it can be easily tracked with a date field in a single table.

Expert Comment

ID: 6272784
Try This

1) Create  a Lookup table like this

Create table LookUpDate(Lookup_d DATE,row_id ROWID);

2) Create a Trigger on After insert of table B

Create or replace trigger TEST after insert on b for each row

insert into LookUpDate
values(sysdate, :NEW.ROWID);

insert into a as
select * from b where rowid in (select rowid from LookUpDate where abs(sysdate - Lookup_dt) = 7);


This trigger will insert rows into table a from table b which are there for last 7 days.

I hope it helps


LVL 35

Expert Comment

by:Mark Geerlings
ID: 6273034
Ser6398 makes a good point.  Can you help us understand the reasons or advantages you see in copying data that is more than a week old?  There may be simpler (at least to Oracle) ways of meeting your needs.

Author Comment

ID: 6273137
hi everybody,
i will explain my application in detail.
this is an application for online examination,
there is a table called examcenter.the following r the fields of that table.

CENTERID                        NOT NULL VARCHAR2(10)
TOTALSYST                       NOT NULL NUMBER(3).

the examcenter is the center in which a student will give examination.whenever a student wants to book a slot for a particular examcenter,the exam slots for the next 7 days r displayed to him through frontend,the no of slots depends upon totalsyst,availabe in a particular examcenter,
now if a examcenter wants to increase or decrease the no of availabe systems then that should be done only after 7 days from the current date,bcoz some slots may be booked for next 7 days from the current date  for that particular examcenter.
so i cant  directly change the value in the table till the 7th day from the current day,
so i will store this value to be changed in some temporary table and update the value in the examcenter table from the temporary table after 7 days.
thats what i want
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6273298
I think you can do this in one table without triggers or copying data to another table by adding a date column, changing the primary key to include the date column, and then either:
1. rename the table and create a view with the original name of the table that selects only the appropriate columns and record(s)
2. change the queries to display only the appropriate columns and record(s).

This looks similar to the structure I use for a product price table, where the primary key includes the product number and a date.  That allows prices to be entered that will take effect at a particular date in the future, as long as the application gets the price from the record that has the greatest date that is less than the current date (ignoring future dates, if any).

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question