Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to fill foreign key from another table (11.0.2.10)
Trigger to fill foreign key from another table [message #663615] Sun, 11 June 2017 03:03 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

In the below example table (test_data) did not have the foreign key field (fk_field_pk) and it was added as a design enhancement. Currently the application will not be able to add it while inserting new records in test_data. I wanted to create a trigger to do this job based on fk_field1 and fk_field2 values who will be deleted in the future.

Please advice me where is the mistake in my code as my trigger does not work (fk_field_pk values do not get updated after inserting in test_data)

The example:
CREATE TABLE TEST_PK
  (
    FIELD_PK NUMBER PRIMARY KEY,
    FIELD1 NUMBER,
    FIELD2 NUMBER,
    FIELD3 VARCHAR2(10)
   );


CREATE TABLE TEST_DATA
  (
    ID NUMBER,
    FK_FIELD1 NUMBER,
    FK_FIELD2 NUMBER,
    TEXT VARCHAR(10),
    FK_FIELD_PK REFERENCES TEST_PK
  );

INSERT ALL 
  INTO TEST_PK VALUES (11,1,10,'A')
  INTO TEST_PK VALUES (22,2,20,'B')
  INTO TEST_PK VALUES (33,3,30,'C')
  INTO TEST_PK VALUES (44,4,40,'D')
SELECT * FROM DUAL;



CREATE OR REPLACE TRIGGER TEST_TRG
    AFTER INSERT
    ON TEST_DATA
    FOR EACH ROW
BEGIN
  
   UPDATE TEST_DATA SET FK_FIELD_PK = 
    (
      SELECT FIELD_PK FROM TEST_PK
      WHERE :NEW.FK_FIELD1 = TEST_PK.FIELD1
        AND :NEW.FK_FIELD2 = TEST_PK.FIELD2
    ); 
      

END;

INSERT ALL 
  INTO TEST_DATA VALUES (1,1,10,'AA',NULL)
  INTO TEST_DATA VALUES (2,2,20,'BB',NULL)
  INTO TEST_DATA VALUES (3,3,30,'CC',NULL)
  INTO TEST_DATA VALUES (4,4,40,'DD',NULL)
SELECT * FROM DUAL;


Many thanks,
Ferro

[Updated on: Sun, 11 June 2017 03:05]

Report message to a moderator

Re: Trigger to fill foreign key from another table [message #663617 is a reply to message #663615] Sun, 11 June 2017 03:34 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Sorry for wasting your time, I got it now. I cant update the same record I am inserting. I have to update the value using a before insert trigger:
CREATE OR REPLACE TRIGGER TEST_TRG
    before INSERT
    ON TEST_DATA
    FOR EACH ROW
DECLARE
  i_fk_field_pk INTEGER;
BEGIN
  
      SELECT FIELD_PK into i_fk_field_pk FROM TEST_PK
      WHERE :NEW.FK_FIELD1 = TEST_PK.FIELD1
        AND :NEW.FK_FIELD2 = TEST_PK.FIELD2;

  :new.FK_FIELD_PK := i_fk_field_pk;
      

END;

Many thanks,
Ferro

[Updated on: Sun, 11 June 2017 03:34]

Report message to a moderator

Re: Trigger to fill foreign key from another table [message #663618 is a reply to message #663615] Sun, 11 June 2017 03:40 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You can't select/update the table (row) you are currently modifying in a "for each row" trigger
2/ It cannot be an "after" trigger, after the row is inserted you can't modify the row in a "for each row" trigger

SQL> CREATE TABLE TEST_PK
  2    (
  3      FIELD_PK NUMBER PRIMARY KEY,
  4      FIELD1 NUMBER,
  5      FIELD2 NUMBER,
  6      FIELD3 VARCHAR2(10)
  7     );

Table created.

SQL> CREATE TABLE TEST_DATA
  2    (
  3      ID NUMBER,
  4      FK_FIELD1 NUMBER,
  5      FK_FIELD2 NUMBER,
  6      TEXT VARCHAR(10),
  7      FK_FIELD_PK REFERENCES TEST_PK
  8    );

Table created.

SQL> INSERT ALL
  2    INTO TEST_PK VALUES (11,1,10,'A')
  3    INTO TEST_PK VALUES (22,2,20,'B')
  4    INTO TEST_PK VALUES (33,3,30,'C')
  5    INTO TEST_PK VALUES (44,4,40,'D')
  6  SELECT * FROM DUAL;

4 rows created.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE TRIGGER TEST_TRG
  2      before INSERT
  3      ON TEST_DATA
  4      FOR EACH ROW
  5  BEGIN
  6     SELECT FIELD_PK into :new.FK_FIELD_PK
  7     FROM TEST_PK
  8     WHERE :NEW.FK_FIELD1 = TEST_PK.FIELD1
  9       AND :NEW.FK_FIELD2 = TEST_PK.FIELD2;
 10  END;
 11  /

Trigger created.

SQL> INSERT ALL
  2    INTO TEST_DATA VALUES (1,1,10,'AA',NULL)
  3    INTO TEST_DATA VALUES (2,2,20,'BB',NULL)
  4    INTO TEST_DATA VALUES (3,3,30,'CC',NULL)
  5    INTO TEST_DATA VALUES (4,4,40,'DD',NULL)
  6  SELECT * FROM DUAL;

4 rows created.

SQL> select * from TEST_DATA;
        ID  FK_FIELD1  FK_FIELD2 TEXT       FK_FIELD_PK
---------- ---------- ---------- ---------- -----------
         1          1         10 AA                  11
         2          2         20 BB                  22
         3          3         30 CC                  33
         4          4         40 DD                  44

4 rows selected.
Previous Topic: Update values "in groups"
Next Topic: ORA-00922: missing or invalid option - PROCEDURE with SPOOL
Goto Forum:
  


Current Time: Fri Mar 29 04:08:35 CDT 2024