CODEWITHSUNDEEP
oracle-databaseplsqltriggers
user8029928
user8029928

Reputation: 27

Trying to create a trigger based on day and time

I'm trying to create a trigger which would prevent insertions, updates and deletions if done outside office hours(i.e, other than 8-18 hours or sundays)

here is my code:

CREATE OR REPLACE TRIGGER HOLIDAY
BEFORE DELETE OR INSERT OR UPDATE ON DEPT FOR EACH ROW
BEGIN
IF ((EXTRACT(DAY FROM SYSDATE)='SUNDAY') OR (EXTRACT(HOUR FROM LOCALTIMESTAMP) NOT BETWEEN 8 AND 18)) THEN
   RAISE_APPLICATION_ERROR(-20001,'ILLEGAL OPERATION - OUT OF OFFICE HOURS OR HOLIDAY!');
 END IF ;
END; 
/

the trigger was created. Today is sunday and when i try to insert a row, the application error is not raise, instead, what i get is:

SQL> @/home/divya/A43.SQL

Trigger created.

SQL> DELETE FROM DEPT WHERE D_NO = 'D3' ;
DELETE FROM DEPT WHERE D_NO = 'D3'
            *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "DIVYA12345.HOLIDAY", line 2
ORA-04088: error during execution of trigger 'DIVYA12345.HOLIDAY'

Can someeone please tell me exactly what is the error? show errors displays - no errors as output. Any help is highly appreciated. thanks in advance.

Upvotes: 1

Views: 1385

Answers (1)

Spock
Spock

Reputation: 327

You get the error because EXTRACT(DAY FROM SYSDATE) returns the number of the day in month, not its name. To retrieve the name, you can use TO_CHAR(SYSDATE, 'DAY') instead, or to_char(sysdate,'fmDY', 'nls_date_language = ENGLISH') as suggested in the comments:

CREATE OR REPLACE TRIGGER HOLIDAY
BEFORE DELETE OR INSERT OR UPDATE ON DEPT FOR EACH ROW
BEGIN
IF (TO_CHAR(sysdate,'fmDY', 'nls_date_language = ENGLISH')='SUNDAY') OR (EXTRACT(HOUR FROM LOCALTIMESTAMP) NOT BETWEEN 8 AND 18)) THEN
   RAISE_APPLICATION_ERROR(-20001,'ILLEGAL OPERATION - OUT OF OFFICE HOURS OR HOLIDAY!');
END IF ;
END; 
/

Upvotes: 1

Related Questions