-
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!!
-
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.
-
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!!
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|