Foreign key between two databases

Question

I went through the previous answers to create a pseudo-foreign key to reference tables between two databases in Netbeans 8.1. This is the code I came up with,

DELIMITER //

CREATE OR REPLACE TRIGGER conf_track_FK
AFTER INSERT OR UPDATE on S26994437.track@FIT5148B
FOR EACH ROW
BEGIN
    IF EXISTS(select * from inserted I where not exists (select * from
    S1234567.conference@FIT5148A A where I.conf_id=A.conf_id))
    RAISE_APPLICATION_ERROR(-20001,'Violation of pseudo-foreign key.');
    ROLLBACK;
    END IF;
END;

/

However, I encounter the following errors:

PLS-00103: Encountered the symbol ";" when expecting one of the following:
   ) with and or group having intersect minus start union where
   connect

PLS-00103: Encountered the symbol "ROLLBACK" when expecting one of the following:
   := . ( % ;
The symbol ":=" was substituted for "ROLLBACK" to continue.

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map

Your error message indicates Oracle and the `@FIT5148B` a DBLink. You can't create a trigger through a db link. You need to connect to the remote database and create the trigger there. Also: the `delimiter //` is invalid for any Oracle SQL tool I know. Are you sure your tool supports that? Furthermore there is no such thing as `inserted` in Oracle - especially not in a row level trigger. — , Apr 01 '16 at 05:55
Also `if-statement` structure is plain wrong. Please help yourself and read some basic tutorials first. They are not that hard to [find](http://stackoverflow.com/tags/plsql/info). — user272735, Apr 01 '16 at 06:47

score 0 · Answer 1 · answered Apr 01 '16 at 05:05

0

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update As Begin

If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN -- Handle the Referential Error Here END

END

answered Apr 01 '16 at 05:05

Dhaval Vansajaliya

26
2

Hi, Thanks fr your quick response, that is the basic structure that i tried to follow. However, the error handling seems to be the part which is inducing the errors. I am unable to fix the same. – Suraj Sachin Apr 01 '16 at 05:32

Avrajit Roy · Answer 2 · 2016-04-01T07:26:42.027

Try the below illustrated snippet. Hope it helps. And also any TRANSACTIONS like COMMIT / ROLLBACK cant be placed INSIDE Trigger unless its an Autonomous transaction but that too not a good idea as parent transaction is hidden to the trigger transaction. So even the Parent transaction fails the Automnomous transaction will be done.

CREATE OR REPLACE TRIGGER conf_track_FK AFTER
  INSERT OR
  UPDATE ON S26994437.track@FIT5148B --Remove DB link as it cant be used in Trigger
  FOR EACH ROW 
  DECLARE 
  lv_cnt PLS_INTEGER;
  BEGIN
    SELECT COUNT(1)
    INTO lv_cnt
    FROM inserted I
    WHERE NOT EXISTS
      (SELECT 1 FROM S1234567.conference@FIT5148A A WHERE I.conf_id=A.conf_id
      );
    IF lv_cnt > 0 THEN
      RAISE_APPLICATION_ERROR(-20001,'Violation of pseudo-foreign key.');
    END IF;
  END;
  /

Foreign key between two databases

2 Answers2