Oracle SQL (trigger)

Question

CREATE OR REPLACE TRIGGER Guest_Change_In_WorkingHour
       BEFORE INSERT OR UPDATE OR DELETE ON guest
    BEGIN
       IF  TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) < 8 -- cant do anything before 8:00am
           OR TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) >= 5
              -- changes must be made BEFORE 5:00pm
            OR TO_CHAR(SYSDATE,'dy') in ('sun','sat') THEN -- nothing on weekends
    RAISE_APPLICATION_ERROR (-20000, 'Satff changes only allowed during business hours.');
    END IF;
   END;
   /

This trigger for during working hours (8AM To 5PM) can perform insert, delete and update data. For my problem is when the time is 4.00AM still can insert, delete and update data. I found that this trigger ignore AM and PM. How to solve?

Alex Poole · Accepted Answer · 2012-07-19 16:32:23Z

2

You're using time format HH24, which is 24-hour ('military' in the US, I believe) time. You therefore need to do this:

TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) >= 17

... rather than use 5.

But as it is, it should be raising the error at any time, not just outside the 08:00-17:00 range (since very few numbers are both >= 8 and < 5). Does the trigger actually compile?

Also, you need to check the case you're using to compare the day of the week too, as I don't think that will match at the moment (correction - yes it will, with dy); but relying on NLS-sensitive values for comparison is not ideal.

edited Jul 19, 2012 at 16:32

answered Jul 19, 2012 at 16:14

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Poole Over a year ago

@LONGKIT - I've tested it, and with 17, this trigger does what you want; did you change anything else? If you try it again as it is now with just that change to 17, what happens?

LONG KIT Over a year ago

i change to 17 and then i also change my pc time to 4.00Am, but still can update record.

Alex Poole Over a year ago

Did you verify that the database also thinks SYSDATE is 04:00, in the same session that you did the update? Is the database on your PC, or on a server somewhere?

Collectives™ on Stack Overflow

Oracle SQL (trigger)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related