What is the syntax to use a Select statement inside a PL/SQL Trigger?

Question

This is what I currently have:

CREATE OR REPLACE TRIGGER MYTRIGGER
AFTER INSERT ON SOMETABLE
FOR EACH ROW    

DECLARE
 v_emplid varchar2(10);    

BEGIN
 SELECT
  personnum into v_emplid
 FROM PERSON
 WHERE PERSONID = :new.EMPLOYEEID;

dbms_output.put(v_emplid);

/* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values from the trigger table*/

END MYTRIGGER;

DBA_ERRORS has this error: PL/SQL: ORA-00923: FROM keyword not found where expected

Answer 1

I would not use a select statment in a trigger ever. Insert into the table rather than a select into. Once the table already exists select into does not work in most databases.

Answer 2

1) There must be something else to your example because that sure seems to work for me

SQL> create table someTable( employeeid number );

Table created.

SQL> create table person( personid number, personnum varchar2(10) );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER MYTRIGGER
  2    AFTER INSERT ON SOMETABLE
  3    FOR EACH ROW
  4  DECLARE
  5   v_emplid varchar2(10);
  6  BEGIN
  7   SELECT personnum
  8     into v_emplid
  9     FROM PERSON
 10    WHERE PERSONID = :new.EMPLOYEEID;
 11    dbms_output.put(v_emplid);
 12    /* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values
 from the trigger table*/
 13* END MYTRIGGER;
 14  /

Trigger created.

SQL> insert into person values( 1, '123' );

1 row created.

SQL> insert into sometable values( 1 );

1 row created.

2) You probably want to declare V_EMPLID as being of type Person.PersonNum%TYPE so that you can be certain that the data type is correct and so that if the data type of the table changes you won't need to change your code.

3) I assume that you know that your trigger cannot query or update the table on which the trigger is defined (so no queries or inserts into someTable).

Answer 3

You are playing with Lava (not just fire) in your trigger. DBMS_OUTPUT in a trigger is really, really bad. You can blow-out on a buffer overflow in your trigger and the whole transaction is shot. Good luck tracking that down. If you must do output-to-console like behavior, invoke an AUTONOMOUS TRANSACTION procedure that writes to a table.

Triggers are pretty evil. I used to like them, but they are too hard to remember about. They affect data often times leading to MUTATING data (scary and not just because Halloween is close).

We use triggers to change the value of columns like .new:LAST_MODIFIED := sysdate and .new:LAST_MODIFIED_BY := user. That's it.

Don't ever allow a TRIGGER to prevent a transaction from completing. Find another option.