SQL Statement Ignored and Missing Expression

Question

I am trying to debug a trigger in Oracle SQL Developer, but I don't really understand the compiler's errors. I trying to follow the templates that I have seen, but it doesn't seem to be working out no matter what I try.

CREATE OR REPLACE TRIGGER audit_movie_insert_trig
REFERENCING NEW AS newRow
AFTER INSERT ON audit_movie 
FOR EACH ROW
BEGIN
INSERT INTO audit_movie VALUES(audit_seq.nextval,  newRow.title,  newRow.m_year,  newRow.m_length, newRow.genre, newRow.studio_name, newRow.producer, SYSDATE, 'Insert');
END;

The errors that I am getting are:

Error(2,7): PL/SQL: SQL Statement ignored
Error(2,83): PL/SQL: ORA-00936: missing expression

Any help would be great.

How may columns the `audit_movie` table has ? Please explicitely list columns in the inset statement: `INSERT INTO tablename (column1, col2,... colN) VALUES ( val1, val2, .... valN)`. Using of `INSERT INTO tablename VALUES(....` is not recommended, because it's very sensitive to errors - when you change the table structure (add or remove some column), then all your code become invalid. — krokodilko, Dec 14 '13 at 21:12

score 1 · Answer 1 · answered Dec 14 '13 at 20:54

1

.m_length,

Please watch out.. it starts with a "." .. u miss the alias name!

answered Dec 14 '13 at 20:54

Maheswaran Ravisankar

17,652
6
47
69

also.. just `SYSDATE` in the `VALUES` will do, you need not use a `SELECT` inside for it. – Maheswaran Ravisankar Dec 14 '13 at 20:56
Right, thanks. I changed the post to better reflect what I am using now; the errors are unchanged. – Nathaniel Grisham Dec 14 '13 at 21:03
You attempt insert trigger, trigerring it.. again.. I just nw read it. Probably you may need to look into [link]http://stackoverflow.com/questions/8167200/insert-trigger-for-inserting-record-in-same-table – Maheswaran Ravisankar Dec 14 '13 at 21:27
That is not a reason for your compilation error. Can you paste the exact one you see in the script ? – Maheswaran Ravisankar Dec 14 '13 at 21:29

score 1 · Accepted Answer · answered Dec 14 '13 at 21:41

You have clauses the wrong way round, as the syntax diagram shows. You should referencing clause after the after insert clause.

You also need to have a colon before newRow references. And you seem to be inserting in the same table the trigger is against, but it looks like your main table is probably just called movie?

CREATE OR REPLACE TRIGGER audit_movie_insert_trig
AFTER INSERT ON movie 
REFERENCING NEW AS newRow
FOR EACH ROW
BEGIN
  INSERT INTO audit_movie (id, title, m_year, m_length, genre, studio_name,
    producer, when, what)
  VALUES(audit_seq.nextval, :newRow.title, :newRow.m_year, :newRow.m_length,
    :newRow.genre, :newRow.studio_name, :newRow.producer, SYSDATE, 'Insert');
END;

I've guessed the column names. As mentioned in a comment, you should specify the column names to avoid issues when the table definition is modified, and it makes it more obvious if you try to insert the wrong values into the wrong columns.

SQL Statement Ignored and Missing Expression

2 Answers2