Select into statement in a trigger

Question

I am using oracle SQL and have two tables, lets just call them TriggerTest1 and TriggerTest2 , with the fields ID, NAME, FROM, UNTIL, where FROM and UNTIL are dates.

I have written this trigger:

CREATE OR REPLACE TRIGGER a 
BEFORE INSERT ON TRIGGERTEST1
DECLARE
    oldentry TRIGGERTEST1%rowtype;
BEGIN 
    IF EXISTS(SELECT * INTO oldentry FROM TRIGGERTEST1 a WHERE a.ID = New.ID) THEN 
        INSERT INTO TRIGGERTEST2 
        VALUES(oldEntry.NAME, oldEntry.FROM, New.FROM, oldEntry.ID);
        DELETE FROM TRIGGERTEST1 b WHERE b.ID = new.ID;
    END IF;
END;

Basically this should just move an entry in Triggertest1 to Triggertest2 when I try to insert an already existing ID into Triggertest1.

I get this error:

Error(3,21): PLS-00103: Encountered the symbol "INTO" when expecting one of the following:     from

According to this my SELECT INTO statement should be correct, does anyone know what the problem is?

`into` works, but it can't be in the `exists` clause. Make select first, then use result in `if`. But there other problems here. For instance you shouldn't select from the same table on which trigger is defined. It causes `ORA-04091` [mutating table](https://www.techonthenet.com/oracle/errors/ora04091.php) error. I would use unique index on `id` and the procedure. — Ponder Stibbons, Aug 09 '19 at 12:41

score 0 · Answer 1 · answered Aug 09 '19 at 12:27

Rewrite it, slightly. For example:

CREATE OR REPLACE TRIGGER a
   BEFORE INSERT
   ON TRIGGERTEST1
DECLARE
   oldentry  TRIGGERTEST1%ROWTYPE;
BEGIN
   SELECT *
     INTO oldentry
     FROM TRIGGERTEST1 a
    WHERE a.ID = :New.ID;

   IF oldentry.id IS NOT NULL
   THEN
      INSERT INTO TRIGGERTEST2
           VALUES (oldEntry.NAME,
                   oldEntry.cFROM,
                   :New.cFROM,
                   oldEntry.ID);

      DELETE FROM TRIGGERTEST1 b
            WHERE b.ID = :new.ID;
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
END;

Note that you can't name a column FROM; it is reserved. (OK, you can, if you enclose its name into double quotes when creating a table, but you shoulnd't.)

score 0 · Answer 2 · answered Aug 09 '19 at 12:28

The issue is in IF EXISTS:

SQL> begin
  2    if exists(select 1 from dual) then
  3      null;
  4    end if;
  5  end;
  6  /
  if exists(select 1 from dual) then
     *
ERROR at line 2:
ORA-06550: line 2, column 6:
PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL
statement only
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored


SQL> declare
  2    n number;
  3  begin
  4    select count(*) into n from dual;
  5    if n > 0 then
  6      null;
  7    end if;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

Select into statement in a trigger

2 Answers2