| SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING [message #579911] |
Sun, 17 March 2013 07:33  |
 |
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 #579914 is a reply to message #579912] |
Sun, 17 March 2013 08:52  |
Solomon Yakobson
Messages: 1398
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 #579918 is a reply to message #579917] |
Sun, 17 March 2013 11:28 |
John Watson
Messages: 3102
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>
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
Thanks, For your help.