Home » SQL & PL/SQL » SQL & PL/SQL » SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING
SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING [message #579911] Sun, 17 March 2013 07:33 Go to next message
slotankar
Messages: 5
Registered: March 2013
Location: MUMBAI
Junior Member
CREATE OR REPLACE TRIGGER TRIGORDER
BEFORE INSERT ON ORDERS
BEGIN
IF TO_CHAR(SYSDATE,'DAY') IN ('SATURDAY','SUNDAY')
THEN
RAISE_APPLICATION_ERROR(-20000,'WEEKDAYS NOT ALLOWED');
END IF;
END;
Re: SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING [message #579912 is a reply to message #579911] Sun, 17 March 2013 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
The trigger is checking for weekend days not week days.
Re: SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING [message #579914 is a reply to message #579912] Sun, 17 March 2013 08:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1967
Registered: January 2010
Senior Member
Oracle documentation of formats elements is a bit confusing. Datetime Format Elements just mentions that :

• Many datetime format elements are padded with blanks or leading zeroes to a specific length. Refer to the format model modifier FM for more information.

And you always have to keep this in mind. When you lookup DAY format element in Table 3-15, "Datetime Format Elements". you will find "Name of day", but in reality it is "Name of day" padded with blanks to longest "Name of day" in corresponding date language. So use FM format modifier. Also, your code in nls_dependent, so either add third parameter to TO_CHAR, specifying english as date language or better use IW format.

SY.
Re: SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING [message #579917 is a reply to message #579914] Sun, 17 March 2013 10:31 Go to previous messageGo to next message
slotankar
Messages: 5
Registered: March 2013
Location: MUMBAI
Junior Member
Smile Thanks, For your help.
Re: SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING [message #579918 is a reply to message #579917] Sun, 17 March 2013 11:28 Go to previous message
John Watson
Messages: 4417
Registered: January 2010
Location: Global Village
Senior Member
Your code will not work for everyone:
c:\users\john\home>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on So Mrz 17 16:25:00 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

orcl>
orcl> select TO_CHAR(SYSDATE,'DAY') from dual;

TO_CHAR(SYSDATE,'DAY')
------------------------------------
SONNTAG

orcl>

Previous Topic: Star printing in PL/SQL Program Problem
Next Topic: create external table..
Goto Forum:
  


Current Time: Fri Aug 01 22:17:22 CDT 2014

Total time taken to generate the page: 0.04990 seconds