Trigger Question...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Trigger Question...

  1. #1
    Join Date
    Mar 2001
    Posts
    77
    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!!


  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  3. #3
    Join Date
    Mar 2001
    Posts
    77
    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!!

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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
  •  


Click Here to Expand Forum to Full Width