INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Run Trigger at certain time only

Run Trigger at certain time only

(OP)
I have this Trigger in the table:
BEFORE UPDATE
:
NEW.RECORD_LAST_UPDATED := SYSDATE;

which updates the field when the record is updated. Great. But at night all records in this table get updated (like clearing all locks on all records) so all records get the date/time of last update at midnight.

How do I limit of when this Trigger gets to run if I want to exclude any time between - let's say - 10:00 PM and 6:00 AM next day?

I was thinking to employ somehow:
SELECT TO_CHAR(SYSDATE, 'HH') AS MYTIME FROM DUAL
but there may be a better way to do it....

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Run Trigger at certain time only

You could store the "Exclude start and end times" in a table. There are some advantages to this approach as you'll probably see.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Run Trigger at certain time only

CREATE OR REPLACE TRIGGER MY_TABLE_T1
BEFORE UPDATE
ON MY_TABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (
TO_NUMBER(to_char(sysdate,'HH24')) BETWEEN 6 AND 22
)
BEGIN
:NEW.RECORD_LAST_UPDATED := SYSDATE;
END whb_test;
/

Bill
Lead Application Developer
New York State, USA

RE: Run Trigger at certain time only

(OP)
Thank you Bill

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Run Trigger at certain time only

Welcome. Glad to help

Bill
Lead Application Developer
New York State, USA

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close