Trigger trouble, seeking assistance

jsturm01 · #1 (**permalink**) 11-01-11, 09:25

Hello,

First time on this forum, and very new to SQL and Oracle. I'm trying to get this trigger to compile properly; all I want it to do at this point is fire if a location being entered in a new row hasn't been entered in any previous row before. Here's the code:

Code:

CREATE OR REPLACE TRIGGER SIGHTINGS_FOREIGN_KEY
BEFORE INSERT ON SIGHTINGS
FOR EACH ROW
BEGIN
  IF :NEW.location NOT IN (SELECT location FROM SIGHTINGS) 
  THEN dbms_out.put('not working yet');
  END IF;
END;

At this point, the errors are:

Quote:

Error(2,3): PL/SQL: Statement ignored
Error(2,27): PLS-00405: subquery not allowed in this context

Any help or suggestions would be appreciated. Thanks in advance.

flyboy · #2 (**permalink**) 11-01-11, 09:42

Quote:

Originally Posted by jsturm01

I'm trying to get this trigger to compile properly; all I want it to do at this point is fire if a location being entered in a new row hasn't been entered in any previous row before.

Throw away that trigger and create unique constraint on SIGHTINGS.LOCATION column. Choose VALIDATE/NOVALIDATE and ENABLE/DISABLE state options according to your exact needs.

By the way, your pseudo code would do nothing as DBMS_OUTPUT only writes to the output buffer; it would not stop/affect executing the INSERT statement which fired it.

Internet Services

Web Development

Digital Marketing

Consumer Tech