?
Solved

firing of trigger on a particular date

Posted on 2001-07-10
8
Medium Priority
?
1,081 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
[X]
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
8 Comments
 
LVL 1

Accepted Solution

by:
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!!
0
 
LVL 2

Expert Comment

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

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 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).
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Expert Comment

by:ser6398
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.
0
 
LVL 3

Expert Comment

by:UsamaMunir
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
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 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.
0
 

Author Comment

by:shaileshpaldiwal
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
0
 
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).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

762 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