3

So I have trigger as following

create or replace TRIGGER TWELVE_ONE 
BEFORE INSERT OR UPDATE OR DELETE
ON ORDERS
FOR EACH ROW 
DECLARE 
V_DAY VARCHAR2 (10); 
MyException exception; 

BEGIN 

SELECT TO_CHAR (SYSDATE, 'DAY') INTO V_DAY FROM DUAL; 
DBMS_OUTPUT.PUT_LINE(V_DAY); 

IF (V_DAY='SUNDAY') 
THEN 
raise_application_error(-20001, 'Today is Sunday. Order table cannot be altered'); 
end if;
END;

Which supposed to prevent UPDATE,INSERT and DELETE if the system day is SUNDAY. To me logically it seems correct but unfortunately its still allows insertion. What am I doing wrong here? I'm using SQL Developer Please help Many Thanks

envyM6
  • 1,099
  • 3
  • 14
  • 35
  • 1
    Another optimisation is to remove the query altogether - you can refer to SYSDATE directly in your IF statement, e.g. `IF TO_CHAR(SYSDATE,'fmDY')='SUN' THEN ...` – Jeffrey Kemp Dec 18 '15 at 00:44

1 Answers1

5

to_char(sysdate, 'day') produces a string that is blank padded to the length of the longest day (if your language is English, that would be 9 characters to handle the string "Wednesday"). This is because in the olden days, you'd have fixed width text reports and you wanted every value to have the same width.

You could trim the value before comparing. But the more elegant approach is to use the fm modifier in your to_char. This stops Oracle from defaulting to blank padding. You can use this with basically any format mask that blank pads the result.

v_day := to_char( sysdate, 'fmDAY' )
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks! I had no idea about it! No wonder why my condition wasnt validating! – envyM6 Dec 17 '15 at 23:11
  • 3
    In order to be independent from user session NLS-Settings you should prefer `IF to_char( sysdate, 'fmDAY', 'NLS_DATE_LANGUAGE = american') = 'SUNDAY' THEN` – Wernfried Domscheit Dec 18 '15 at 06:34
  • @WernfriedDomscheit opens an important point: localization. What time zone? 12:15 am Monday in New York is Sunday 10:15 pm in Seattle. And are we requesting day names in English? Be ready to address these. – Andrew Wolfe Dec 19 '15 at 18:54