Solved

firing of trigger on a particular date

Posted on 2001-07-10
8
1,035 Views
Last Modified: 2012-05-04
hi,
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.

0
Comment
Question by:shaileshpaldiwal
8 Comments
 
LVL 1

Accepted Solution

by:
bkm earned 100 total points
Comment Utility
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!!
0
 
LVL 2

Expert Comment

by:afkapur
Comment Utility
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.

Else:
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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).
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
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.
0
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.

 
LVL 3

Expert Comment

by:UsamaMunir
Comment Utility
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
begin

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);

end;
/


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

I hope it helps

regards
UsamaMunir

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 

Author Comment

by:shaileshpaldiwal
Comment Utility
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
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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).
0

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.

Join & Write a Comment

Suggested Solutions

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now