Time Triggers

[doreen]

Hi Experts,
I'm to triggers. How to prevent update of Salary table at any time between 8pm and 10am. Only allow updates on Weekdays?

thx
doreen

 

[carp]

Somewhere in your trigger (preferably the beginning) use the following:
.
.
.
v_timestamp DATE := sysdate;
e_out_of_bounds EXCEPTION;
.
.
.
BEGIN
IF ((to_char(v_timestamp,'HH24') BETWEEN '08' and '10') OR
(to_char(v_timestamp,'DAY') IN ('SATURDAY','SUNDAY')) THEN
RAISE e_out_of_bounds;
ELSE
.
.
.
<the rest of the trigger logic>
.
.
.
EXCEPTION
WHEN e_out_of_bounds
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,'You Cannot Perform This Action At This Time');
END;

 

[pet2236]

hi carp,

CREATE OR REPLACE TRIGGER salary_update_trig
AFTER UPDATE
ON salary

BEGIN

IF TO_NUMBER (TO_CHAR (SYSDATE,'HH24')) > 20
OR TO_NUMBER (TO_CHAR (SYSDATE,'HH24')) < 10
OR TO_CHAR (SYSDATE, 'DAY') IN ('SATURDAY', 'SUNDAY')

THEN

IF UPDATING THEN
RAISE_APPLICATION_ERROR (-20100, 'NOT allowed to update a offering now!');

END IF;
END IF;
END;
/

i still can update when i set the time to 1900: 7pm, Saturday???? Pls advise n modify it!!!

Can u pls provide me yr above replied code also???

thx
doreen.

 

[carp]

There's probably a cleaner way to do this, but it's the weekend so I'm in 'quick and dirty' mode:

create or replace trigger salary_update_trig before update on test
declare e_time exception;
begin
if to_number(to_char(sysdate,'HH24')) > 18
or to_number(to_char(sysdate,'HH24')) < 10
then
raise e_time;
end if;
exception
when e_time then
raise_application_error(-20100,'blew up on update');
end;

SQL> update test set salary = 5000;
update test set salary = 5000
*
ERROR at line 1:
ORA-20100: blew up on update
ORA-06512: at &quot;SYSTEM.SALARY_UPDATE_TRIG&quot;, line 11
ORA-04088: error during execution of trigger 'SYSTEM.SALARY_UPDATE_TRIG'

 

[doreen]

Hi carp,
how about the updates only on Weekdays? pls help..


actually wat wrong with my code above? I still can update when i set the time to 1900: 7pm, Saturday???? Pls advise n modify it!!!


thx again

 

[carp]

OK, now it's not the weekend, so I'll include the AND clause for you:

CREATE OR REPLACE TRIGGER salary_update_trig BEFORE UPDATE ON test
DECLARE e_time EXCEPTION;
BEGIN
IF to_number(to_char(sysdate,'HH24')) NOT BETWEEN 10 AND 18
OR to_char(sysdate,'DAY') IN ('SATURDAY','SUNDAY') THEN
RAISE e_time;
END IF;
EXCEPTION
WHEN e_time THEN
raise_application_error(-20100,'blew up on update');
END;

I know the table name and times being tested are also a little different from what you need, but you'll just have to make those adjustments.

Now, as to why your code doesn't work - I saw the same problem you did, so I can only assume that RAISE_APPLICATION_ERROR doesn't work like a regular exception when invoked from within the executable code. Unfortunately, I'm having trouble finding this procedure in any documentation, so I haven't been able to confirm that yet. However, if you actually study the structure of the above code, you will see that I have declared and raised an explicit exception, then invoked RAISE_APPLICATION_ERROR from within the EXCEPTION section - at which time the code worked as desired.

 

[yopy]

what about if i want to going to update
the table every three hours everyday ?

regards

 

[sem]

Submit your job with dbms_job package.