Re: Firing Triggers from Oracle --> Sybase

From: Dave Dargo <ddargo_at_us.oracle.com>
Date: 1995/09/09
Message-ID: <42sgmf$3tn_at_inet-nntp-gw-1.us.oracle.com>#1/1

diamonds2_at_aol.com (Diamonds2) writes:

>Hi,
>
>We are running Oracle 7.1 and we need to fire triggers to our SYBASE 10
>database. Unfortunately no one knows what we need to do to make this
>happen.
>We are new to both Oracle and Sybase, classes are coming up but will
>be too late for our deadline.
>
>Can some fill me in? What needs to be set in Oracle and Sybase for this
>to
>happen? Can the triggers fire directly into Sybase or will it put into a
>ascii txt
>file for SYBASE to pickup with bcp?

You can use the Oracle Transparent Gateway for Sybase which will integrate your Oracle database server with your Sybase server. This includes access from Oracle stored procedures and database triggers. You simply set up a database link in Oracle that points to the Sybase server. After that you can address the Sybase objects as in the following:

CREATE DATABASE LINK SYBS USING 'SYBS'; -- One time definition

SELECT * FROM EMP_at_SYBS;
UPDATE EMP_at_SYBS SET SAL=1000 WHERE EMPNO=2345;

You can also set up a synonym to make the coding even easier:

CREATE SYNONYM EMP FOR EMP_at_SYBS; -- One time definition

SELECT * FROM EMP;
UPDATE EMP_at_SYBS SET SAL=1000 WHERE EMPNO=2345;

By using a synonym you will be able to move the object from Sybase to Oracle and not change your code.

Because Oracle offers fully recoverable two-phase commit between any number of Oracle servers and up to one non-Oracle server you are able to update your Sybase objects with full confidence in transaction coordination.

Your Oracle database triggers simply refer to the Sybase objects. Using the above example this trigger will keep an Oracle and Sybase table in sync:

CREATE TRIGGER EMP_SYNC
AFTER INSERT OR UPDATE OR DELETE
ON ORACLE_EMP
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO SYBS_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

                VALUES (:NEW.EMPNO,:NEW.ENAME,:NEW.JOB,:NEW.MGR,:NEW.HIREDATE,
			:NEW.SAL,:NEW.COMM,:NEW.DEPTNO)

  END IF;
  IF UPDATING THEN
    UPDATE SYBS_EMP SET

		EMPNO = :NEW.EMPNO, ENAME = :NEW.ENAME, JOB = :NEW.JOB,
		MGR = :NEW.MGR, HIREDATE = :NEW.HIREDATE, SAL = :NEW.SAL,
		COMM = :NEW.COMM, DEPTNO = :NEW.DEPTNO
	WHERE EMPNO = :OLD.EMPNO;

  END IF;
  IF DELETING THEN
    DELETE FROM SYBS_EMP WHERE EMPNO=:OLD.EMPNO;   END IF;
END; With the above trigger, any changes made to the Oracle EMP table will be reflected in the Sybase EMP table. Because Oracle provides two-phase commit between the two systems, any changes made will be guaranteed to be made in both locations.

Hope this helps.

Dave (ddargo_at_us.oracle.com) Received on Sat Sep 09 1995 - 00:00:00 CEST