Dear Tom,

Thank you so much for your prompt response.

But we have to handle replication and timezone change(to GMT)

For example
A)
IF (DBMS_REPUTIL.FROM_REMOTE = FALSE)
....
...

also timezone should be stored in GMT format.
B)
:NEW.CREATEDATE := NEW_TIME(SYSDATE,GETZONE,'GMT');


So we cannot simply go with DEFAULT values.

In this scenario what would you prefer? I mean (1)Single Trigger with IF condition OR (2)Multiple Trigger

Thanks again
-Sami

Maybe I am overlooking something, but your solution is not exactly the same as the original poster's:
You provide a default (= overrulable) value for creationdate.

If I insert ...(creationdate,...) values (sysdate - 1, ...), your solution will provide different results then the original

So you end up needing an insert trigger I guess.

Tom:

I have created an "before update trigger" on table "books" that sends an email of all books that get updated when their status changes from "W" to "S".

However, the email is getting sent for each book. I am trying to change that to a statement level trigger but then I seem to lost the access to "WHEN" clause and old and new variables.

Which one should I do that allows me to send 1 email if I update 10 books from a table with 100 books.

Thanks,

Tom:

Would there be a problem of doing this?

a. Add a column (job_id) to table A.
b. Update A.job_id (with l_job) in the original update statment that updates the table instead of doing it using a trigger. I can get job id by:

SELECT sys.jobseq.nextval into l_job from dual;

c. Create a statement level after-update trigger on Table A that would schedule the job.

1. However How can I get the job id here since I can't select the table.record I have the trigger defined on.

2. WHen I ran the above SELECT statment in a trigger I kept getting an error that "invalid column name".

Tom:

You refered me to a link before but I do not think it applies here:

Here is the problem:

I have table Books(book_no,process_date,status,job_id);

When user select several books for processing they update the book_date(sysdate) and status. They do that 20 books at a time (using powerbuilder client/oracle database).

What I need to do is schedule an reminder email seven days later that will get sent to shipping if processed books do not get shipped after 7 days.

TO get process_date (sysdate usuallly) I have to use row level trigger. Problem here I need to schedule one email job and not 20 jobs that will be sent if status did not change. I am trying to update job_id for each record with the same job_id and then schedule one email job for that job id. When shipping department creates a shipment for these books I will remove the job and no email will be sent.

I appreciate your help.

Tom:

It is 20 different update statements. The problem is that I need to update 20 records with job id and send the email once for the 20 books.

I was thinking of doing it this way:

Create a job that rescedules itself every 24 hours. The job will run a procedure that checks table BOOKS for anything flagged to be processed. If any records exist, send email else rescedule job 24 hours later. I need to include in the procedure code to check if that status of those 20 books changed, so I can delete the job in case it exists since books were shipped before 7 days.

How would you do it? What do you mean by "I havr to save them up". Do you mean insert those 20 in a spearate table?