Trigger Question...

[oraka6]

I am testing this trigger at 4:20pm and it suppose to raise this error message when I do an update. However, this trigger doesn't give me an error like the following:

SQL> UPDATE EMP
2 SET SAL =6500
3 WHERE ename='MILLER';
UPDATE EMP
*
ERROR at line 1:
ORA-20503: You may only update SAL during normal hrs.
ORA-06512: at "SCOTT.SECURE_EMP", line 11
ORA-04088: error during execution of trigger 'SCOTT.SECURE_EMP'

This is the code:

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF (TO_CHAR (sysdate, 'DY')) In ('SAT', 'SUN') OR
(TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '16')
-- Inserting @6:15pm.
THEN
IF DELETING
THEN RAISE_APPLICATION_ERROR (-20502, 'You may only delete from EMP during normal hours.');
ELSIF INSERTING
THEN RAISE_APPLICATION_ERROR (-20500, 'You may only insert into EMP during normal hrs.');
ELSIF UPDATING ('SAL')
THEN RAISE_APPLICATION_ERROR (-20503, 'You may only update SAL during normal hrs.');
ELSE
RAISE_APPLICATION_ERROR (-20504, 'You may only update EMP during normal hrs.');
END IF;
END IF;
END;
/

I think the problem occurs in TO_CHAR (sysdate, 'HH24'). What is it exactly though? Thank you in advance!!

[Shestakov]

U have problem with conversion from char to number:
Format 'hh24' return value 16 when time from 16:00 to 16:59.

U have to change 'if .." statment:

IF (TO_CHAR (sysdate, 'DY')) In ('SAT', 'SUN') OR
(TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '15')
-- Inserting @6:15pm.
THEN

because :
SQL> select * from (
2 select to_char(sysdate+(rownum-1)/24,'hh24') hh from all_objects where rownum <25)
3 where hh not between '08' and '16';
HH
--
22
23
00
01
02
03
04
05
06
07
17
18
19
20
21

16 INCLIDE IN WORKING TIME!
but:
select * from (
select to_char(sysdate+(rownum-1)/24,'hh24') hh from all_objects where rownum <25)
where hh not between '08' and '15';
..
04
05
06
07
16
17
18

16 - NOT INCLIDE IN WORKING TIME.

[oraka6]

If I understand you correctly, I can insert into emp up to 16th hour in the following query so that I won't have any problems entering data into the table after 16th hour, like at 16:20, for example.

select * from (
select to_char(sysdate+(rownum-1)/24,'hh24') hh from all_objects where rownum <25)
where hh not between '08' and '15';

**
I am trying to find out what if my boss told me to write a trigger disallowing anyone to enter data after 16th hour? If anyone enters data at 16:10, or at 16:15 etc, the trigger should fire and give user an error message. How do I go about doing that by modifying the above trigger?

Also, can anyone tell me why (sysdate+(rownum-1)? What's the purpose of using ROWNUM in this case?

Thank you!!

[Shestakov]

1) About
IF (TO_CHAR (sysdate, 'DY')) In ('SAT', 'SUN') OR
(TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '15')
-- Inserting @6:15pm.
THEN
...
FIELD not between A and B =>
if NOT (FIELD => A and FIELD <= B) then
(in ur case :
if NOT (TO_CHAR (sysdate, 'HH24') >= '08' and TO_CHAR (sysdate, 'HH24') <= '16') then
function TO_CHAR(sysdate, 'HH24') return '16' if current time from 16:00 to 16:59, and all update/insert statment in this time will not generate errors.

2) About ROWNUM:
This is usual technic for create date/hour/... generators

rownum (without ORDER BY ..) = 1, 2, 3, ...

(sysdate+(rownum-1)/24 (if rownum = 1) = current date + time
(sysdate+(rownum-1)/24 (if rownum = 2) = current date + time + 1 hour
(sysdate+(rownum-1)/24 (if rownum = 3) = current date + time + 2 hours
and so on ..

I just had showed to u whitch hours was working hours in ur trigger.