CODEWITHSUNDEEP
mysqlsqloracle-databasetriggers
Rocky Pulley
Rocky Pulley

Reputation: 23301

Can you modify a foreign key on insert with a trigger?

I have an issue where a query is inserting into table A, which references table B, but it's using the wrong foreign key for table B. Is it possible to create a trigger in oracle where if the input foreign key is 'ASDF', we modify it to 'FDSA' before the insert so that we can fix this issue?

Upvotes: 1

Views: 146

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In either MySQL or Oracle, you can do this using a before insert trigger.

I don't recommend using a trigger for this purpose. Fixing the input data or adding the new value to the reference table seem to me to be more sensible approaches.

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Following up on @GordonLinoff's post - I agree with (what appears to be) his point that a trigger is inappropriate here. However, recognizing that sometimes you have to work with what you're given, if you were to use a trigger you'd use a BEFORE INSERT trigger, as in:

CREATE TRIGGER TABLE_A_BI
  BEFORE INSERT ON TABLE_A
  FOR EACH ROW
BEGIN
  IF :NEW.FK_FIELD = 'ASDF' THEN 
    :NEW.FK_FIELD = 'FDSA';
  END IF;
END YOUR_TABLE_BI;

Best of luck.

Upvotes: 0

Related Questions