firing of trigger on a particular date

Posted on 2001-07-10
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

Accepted Solution

bkm earned 100 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).
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

777 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