Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run Trigger at certain time only 1

Status
Not open for further replies.

Andrzejek

Programmer
Jan 10, 2006
8,509
US
I have this Trigger in the table:
[tt][blue]BEFORE UPDATE
:[/blue]NEW.RECORD_LAST_UPDATED[blue] := SYSDATE;[/blue][/tt]
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:
[tt]SELECT TO_CHAR(SYSDATE, 'HH') AS MYTIME FROM DUAL[/tt]
but there may be a better way to do it....

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
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


 
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
 
Thank you Bill

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
Welcome. Glad to help

Bill
Lead Application Developer
New York State, USA
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top