SQL & PL/SQL

I have a PROCEDURE that INSERTs a record, and returns the TRIGGER/SEQUENCE assigned id. Now i need to modify it, to INSERT...WHERE...NOT EXISTS.

The problem is, the usual way i would do this is to change the VALUES() clause into SELECT..FROM Dual..WHERE NOT EXISTS, but this invalidates the use of the RETURNING clause.

How do i check if a record already EXISTS and find out what the popped SEQUENCE value was?


SQL> CREATE TABLE A (A INT);

Table created.

SQL> CREATE SEQUENCE B;

Sequence created.

SQL> CREATE OR REPLACE TRIGGER C BEFORE INSERT ON A
2 FOR EACH ROW BEGIN SELECT B.NEXTVAL INTO :NEW.A FROM Dual; END;
3 /

Trigger created.

SQL>
SQL> VAR A NUMBER
SQL> INSERT INTO A(A) VALUES(1) RETURNING A INTO :A;

1 row created.

SQL> INSERT INTO A(A) SELECT 1 FROM Dual RETURNING A INTO :A;
INSERT INTO A(A) SELECT 1 FROM Dual RETURNING A INTO :A
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL>
SQL> DROP SEQUENCE B;

Sequence dropped.

SQL> DROP TABLE A;

Table dropped.