What is wrong with this trigger definition?

From: Benjamin Hell <bhell_at_spamfence.net>
Date: Thu, 22 Jun 2006 13:57:14 +0200
Message-ID: <4fvet4Fi06fjU1@individual.net>

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; /

Received on Thu Jun 22 2006 - 06:57:14 CDT