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 |
|
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 |
|
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 |
|
Michel Cadot
Messages: 68665 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.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:42:14 CDT 2024
|