Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Trigger problem with federated database

    I have a federated system in Windows 2000 (DB2 EE 7.2 fixpack 5) and remote system in Windows 2000 (DB2 EE 7.2 fixpack 5).

    I would like to create trigger witch will check referencial integrity with the table in federated system witch points to table in remote system - somehow simulate an foreign key witch can not be defained between federated system and remote database.

    I created two test tables. Customer is inside federated system, State is inside remote database.

    Custumer (customerID integer, stateID integer)
    State (stateID integer, stateDes char(20))

    I tried to write trigger to simulate foreign key for update on Custumer table:

    CREATE TRIGGER REF_INTEGRITY AFTER UPDATE OF stateID ON Custumer REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.StateID NOT IN (SELECT StateID FROM State)) SIGNAL SQLSTATE '75000' ('Referencial integrity Error after update')

    This command returns the error:

    SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "".) SQLSTATE=58004

    Is this a sign that I can not make a call to remote table from trigger?

    Grofaty

  2. #2
    Join Date
    Jan 2004
    Location
    Mumbai, India
    Posts
    16

    Re: Trigger problem with federated database

    Originally posted by grofaty
    I have a federated system in Windows 2000 (DB2 EE 7.2 fixpack 5) and remote system in Windows 2000 (DB2 EE 7.2 fixpack 5).

    I would like to create trigger witch will check referencial integrity with the table in federated system witch points to table in remote system - somehow simulate an foreign key witch can not be defained between federated system and remote database.

    I created two test tables. Customer is inside federated system, State is inside remote database.

    Custumer (customerID integer, stateID integer)
    State (stateID integer, stateDes char(20))

    I tried to write trigger to simulate foreign key for update on Custumer table:

    CREATE TRIGGER REF_INTEGRITY AFTER UPDATE OF stateID ON Custumer REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.StateID NOT IN (SELECT StateID FROM State)) SIGNAL SQLSTATE '75000' ('Referencial integrity Error after update')

    This command returns the error:

    SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "".) SQLSTATE=58004

    Is this a sign that I can not make a call to remote table from trigger?

    Grofaty

    Hi
    i am also struggled like u to do the same
    but now its woking
    i will give the triger which i created, that is working

    CREATE TRIGGER Test1 no cascade before insert ON emp1 REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (deptno NOT IN (SELECT deptno FROM dept)) SIGNAL SQLSTATE '75000' ('Referencial integrity Error before insert')

    Sakthi
    G.Sakthi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •