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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

809 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