| trigger ? Forms 6i [message #80749] |
Fri, 15 November 2002 09:13  |
sun77
Messages: 2
Registered: November 2002
|
Junior Member |
|
|
Hello, I am using Forms 6i.
What type of trigger and what syntax should I write if I need to automatically update foreign key field (loc_no for example) in the TEST table (for example) when the use make changes to the primary key (loc_no) in the Locations table
Thanks a lot!
|
|
|
|
| Re: trigger ? Forms 6i [message #80751 is a reply to message #80749] |
Fri, 15 November 2002 14:24  |
fokker
Messages: 18
Registered: March 2002
|
Junior Member |
|
|
If you want to update the foreign key of a Table when the Primary key of another table is changed, then you can just create a foreign key constraint with a cascading update.
ALTER TABLE table1 ADD CONSTRAINT table1_fk FOREIGN KEY (field_name) REFERENCES table2 (field_name) ON UPDATE CASCADE;
If you want to go the other way, then you have to write a trigger on the foreign key table. Might go something like this:
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT OR UPDATE ON
table1
FOR EACH ROW
BEGIN
IF :new.field_name <> :old.field_name THEN
UPDATE table2
SET field_name = :new.field_name
WHERE field_name = :old.field_name;
END IF;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
