Home » SQL & PL/SQL » SQL & PL/SQL » error in trigger
error in trigger [message #253726] Tue, 24 July 2007 13:37 Go to next message
madhu_msinus@yahoo.co.in
Messages: 12
Registered: July 2007
Location: usa
Junior Member
can any one help me out from this error..
thanx.


SQL> create or replace trigger secure_emp
2 before insert or update or delete on employees
3 begin
4 if (to_char(sysdate,'DY') in ('SAT','SUN')) or (sysdate not between
5 (trunc(sysdate)+8/24)and(trunc(sysdate)+18/24)) then
6 if deleting then raise_application_error(-20502,'no delete');
7 elsif inserting then raise_application_error(-20503,'no insert');
8 elsif updating('salary') then raise_application_error(-20507,'u may update sal in business days
');
9 else raise_application_error(-20509,'normal hours');
10 end if;
11 end if;
12 end
13 /

Warning: Trigger created with compilation errors.

SQL> show errors;
Errors for TRIGGER SECURE_EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/3 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.
Re: error in trigger [message #253727 is a reply to message #253726] Tue, 24 July 2007 13:44 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
First of all read the stickies and format your code accordingly.

Have a look at your if - elsif - end if structure(s). If the code was properly formatted and indented it would stick out like a sore thumb.

[Updated on: Tue, 24 July 2007 13:45]

Report message to a moderator

Re: error in trigger [message #253728 is a reply to message #253727] Tue, 24 July 2007 14:06 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Simple approch edit according to your requeiments.
SQL> CREATE TABLE ORACLE.TEST
  2  AS
  3  SELECT * FROM DBA_USERS;

Table created.

SQL> create or replace trigger TEST
 before insert or update or delete on oracle.TEST
 declare
     sydate char(3);
     begin
     sydate:=to_char(sysdate,'dy','nls_date_language=american');
 if sydate in ('mon','tue','thu') then
  raise_application_error(-20005,'no insertion/updation/deletion allowed');
     end if;
   end;
/
Trigger created.

SQL> CONN ORACLE/DSORACLE
Connected.
SQL> INSERT INTO TEST
  2  SELECT * FROM TEST;
INSERT INTO TEST
            *
ERROR at line 1:
ORA-20005: no insertion/updation/deletion allowed
ORA-06512: at "ORACLE.TEST", line 6
ORA-04088: error during execution of trigger 'ORACLE.TEST'



[Updated on: Tue, 24 July 2007 14:10]

Report message to a moderator

Re: error in trigger [message #253740 is a reply to message #253726] Tue, 24 July 2007 14:34 Go to previous message
chimbs
Messages: 32
Registered: April 2005
Member
You are missing the semicolon in the line 12.
Previous Topic: date conversion
Next Topic: How to identify / prevent pl/sql function calls in infinite loop?
Goto Forum:
  


Current Time: Fri Dec 02 13:50:20 CST 2016

Total time taken to generate the page: 0.06079 seconds