Simple Trigger Error?! PL/SQL: ORA-00933:

ora-00933 sql command not properly ended insert
ora-00933 sql command not properly ended select
ora-00933: sql command not properly ended c#
ora-00933 sql command not properly ended create table
ora-00933: sql command not properly ended java
ora-00933: sql command not properly ended limit
ora-00933 sql command not properly ended execute immediate
ora-00933 sql command not properly ended stack overflow

I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The trigger will insert new values that are added into a consultant table into this audit table.

I have re-jigged the trigger a lot but cannot seem to bypass the compilation error! I assume it's something small?

DROP TABLE   MyAuditTable;
CREATE TABLE MyAuditTable (
    audit_id INTEGER NOT NULL,
    new_name VARCHAR2 (30),
    new_postcode VARCHAR2 (20),
    status     VARCHAR2 (15), 
    CONSTRAINT pk_MyAuditTable  PRIMARY KEY ( audit_id )
); 


DROP sequence MySeq;
Create sequence MySeq MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1;

drop trigger MyTrigger;
create trigger MyTrigger
after insert on my_consultant_table
for each row
begin
    insert into MyAuditTable values (
        MySeq.nextval, :new.con_name, 
        :new.con_postcode, 
        'Pending'
    )
    from my_consultant_table;
end;
/

ERROR: PL/SQL: ORA-00933:

So the audit table should now have the newly inputted data from the consultant table, which contains name and postcode attributes. Another trigger will fire so that when the status is changed, these changes are permitted.

Thank You!

The line

from my_consultant_table

Is unnecessary.

Should be:

insert into MyAuditTable values (MySeq.nextval, :new.con_name, :new.con_postcode, 'Pending');

Update inside pl/sql Trigger - plsql, Simple Trigger Error?! PL/SQL: ORA-00933: I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The solution to the ORA-00933 error is to update your query to remove the clause that’s causing the issue. This would depend on the type of query being run. Let’s take a look at some example solutions.

This ORA-00933 denotes a syntax error in your trigger declaration :

begin
    insert into MyAuditTable values (
        MySeq.nextval, :new.con_name, 
        :new.con_postcode, 
        'Pending'
    )
    from my_consultant_table;
end;

The trailing from my_consultant_table does not make sense, just remove it and you should be fine :

begin
    insert into MyAuditTable values (
        MySeq.nextval, :new.con_name, 
        :new.con_postcode, 
        'Pending'
    );
end;

Oracle / PLSQL: ORA-00933 Error Message, Learn the cause and how to resolve the ORA-00933 error message in Oracle. You tried to execute a SQL statement with an inappropriate clause. PL/SQL Check if SYSDATE is between two DATETIMES “HH24:mi” sql,oracle,plsql. Using TRUNC on a date sets it as 00:00 on that day. I assume what you're after here is "Check if the, right now, is between X and Y".

Try this:

begin
insert into MyAuditTable 
   select
    MySeq.nextval,
    :new.con_name, 
    :new.con_postcode, 
    'Pending'
   from dual;
end;

One Tip: When u create a sequence and always use from 1 to 9999999.... anc increment one by one. Just use

create sequence owner.table nocache;

PL/SQL Triggers, Constraints are easier to write and less error-prone than triggers that enforce the same rules. A simple DML trigger fires at exactly one of these timing points:. You can avoid ORA-00933 errors while still executing the programming goals that you wish to obtain. Some clauses, such as the ORDER BY clause, can be recreated by inserting the clause in a separate query. For example, if you are trying to order your view, doing so in the CREATE VIEW statement will trigger an ORA-00933.

Oracle SQL, A DML trigger is either simple or compound. A simple DML trigger fires at exactly one of these timing points: Before the triggering statement runs. (The trigger is  Exactly right - which is not the same as "Oracle does not allow joining tables in an UPDATE statement." I know that, and clearly you know that too; now search through SO and see how many times people make that statement, even in (many) cases when updating through a join would work perfectly fine, if only people used the proper syntax and they had the proper unique constraint in the source table.

Oracle Trigger, Triggers are a special PL/SQL construct similar to procedures. Oracle also provides simple error message strings that have a format similar to the following:. ERROR: PL/SQL: ORA-00933: So the audit table should now have the newly inputted data from the consultant table, which contains name and postcode attributes. Another trigger will fire so that when the status is changed, these changes are permitted. Thank You!

ORA-00933:SQL command not properly ended, A trigger is a named PL/SQL block stored in the Oracle Database and executed you will receive an error message indicating that the name of your trigger is  error: 11/2 PL/SQL: SQL Statement ignored 24/8 PL/SQL: ORA-00933: SQL command not properly ended please help me in this I have the same question Show 0 Likes (0) 101 Views

Comments
  • Just remove from my_consultant_table
  • Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
  • @JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
  • I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)