Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: Trigger trouble, seeking assistance

    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:
    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.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by jsturm01 View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •