Re: What is wrong with this trigger definition?

Benjamin Hell wrote:
> Hello!
>
> I cannot find out what I'm doing wrong with the definition of the
> trigger below. I would like to create a MAPPING table which contains
> aliases/translations of primary key values in other tables (with
> different names of the primary key column). With the trigger I want
> to make sure that the alias target really exists before entering a
> tuple into the MAPPING table. It does two things (well, not quite
> yet): (1) find out which column is primary key for the table; (2)
> check that column for the target value. But the trigger only
> compiles with errors:
>
> LINE/COL ERROR
> -------- -------------------------------------------
> 9/9 PL/SQL: SQL Statement ignored
> 11/13 PL/SQL: ORA-00903: invalid table name
>
> I think it is some problem with using the result (key_col) of the
> first SELECT query in the second one. But I'm kind of stuck here.
>
> I hope the example below is clear enough that you can maybe point me
> towards some solution. I'm running 10g on Windows 2003.
>
>
> Thanks a lot in advance,
>
> Benjamin
>
>
>
> -- Some testing tables:
> CREATE TABLE COUNTRIES (
> ID VARCHAR2(2) PRIMARY KEY NOT NULL,
> Name VARCHAR2(64)
> );
>
> CREATE TABLE SPORTS (
> Name VARCHAR2(64) PRIMARY KEY NOT NULL,
> Description VARCHAR2(64)
> );
>
> -- The translation table:
> CREATE TABLE MAPPING (
> Alias VARCHAR2(64),
> TableName VARCHAR2(30),
> Target VARCHAR2(64)
> );
>
> ALTER TABLE "MAPPING"
> ADD CONSTRAINT pk_mapping
> PRIMARY KEY (Alias, TableName)
> ;
>
> -- And some data:
> INSERT INTO COUNTRIES VALUES ('US', 'United States');
> INSERT INTO COUNTRIES VALUES ('DE', 'Germany');
>
> INSERT INTO SPORTS VALUES ('Soccer', 'Nice game');
> INSERT INTO SPORTS VALUES ('Tennis', 'Very boring');
>
> -- That's data which should be allowed by the trigger:
> INSERT INTO MAPPING VALUES ('U.S.A.', 'COUNTRIES', 'US');
> INSERT INTO MAPPING VALUES ('Deutschland', 'COUNTRIES', 'DE');
> INSERT INTO MAPPING VALUES ('Fussball', 'SPORTS', 'Soccer');
>
> -- Check that "Target" is in primary key column of "TableName":
> CREATE OR REPLACE TRIGGER trig_check_mapping_target
> BEFORE INSERT OR UPDATE ON MAPPING
> FOR EACH ROW
> DECLARE
> key_col VARCHAR2(4000);
> target_value VARCHAR2(16);
> BEGIN
> SELECT UNIQUE USER_CONS_COLUMNS.COLUMN_NAME INTO key_col
> FROM USER_CONS_COLUMNS, USER_CONSTRAINTS
> WHERE USER_CONS_COLUMNS.TABLE_NAME = :new.TableName
> AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P';
> SELECT key_col INTO target_value
> FROM :new.TableName
> WHERE key_col = :new.Target;
> IF target_value = NULL THEN
> RAISE_APPLICATION_ERROR (
> num => -20002,
> msg => :new.Target || ' not in key of ' || :new.TableName
> );
> ELSE
> NULL;
> END IF;
> END;
> /

In addition to the other advice you received ...

ELSE NULL does nothing ... eliminate it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org