Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> MUPSERT IN TRIGGER

MUPSERT IN TRIGGER

From: JAW <jwilliam_at_aglresources.com>
Date: 8 Apr 2005 05:17:18 -0700
Message-ID: <1112962638.155158.145140@g14g2000cwa.googlegroups.com>


I create a trigger to perform a mupsert.

Oracle 9.2.0.6 on Solaris 64-bit.

The old time way works.

CREATE OR REPLACE TRIGGER TRG_GPS_MOBILITY   AFTER INSERT
  ON EXPORT_DEVICE_STATE
  FOR EACH ROW
/*

    NAME - TRG_GPS_MOBILITY
*/
 DECLARE

BEGIN

SELECT DEVICE_ID,DEVICE_DSC INTO v_device_id,v_device_dsc
  FROM EXPORT_DEVICE
 WHERE DEVICE_ID = :NEW.DEVICE_ID

 AND ROWNUM = 1; v_prefix := SUBSTR(v_device_dsc,1,3);

IF v_prefix = 'ETG' THEN
  INSERT INTO GPS_MOBILITY
VALUES(v_device_dsc,:NEW.latitude,:NEW.longitude,:NEW.lud); END IF; EXCEPTION
 WHEN NO_DATA_FOUND THEN
    null;
 WHEN DUP_VAL_ON_INDEX THEN
  UPDATE GPS_MOBILITY A
    SET A.v_d_Latitude= :NEW.latitude, A.v_d_longitude = :NEW.longitude,A.v_UpdateTime = :NEW.lud   WHERE v_RemoteName = v_device_dsc;
 WHEN OTHERS THEN

     v_errcode := SQLCODE;
     v_errcode := SUBSTR(SQLERRM,1,300);
     INSERT INTO ERRORTAB VALUES

(seq_errortab.NEXTVAL,'TRG_GPS_MOBILITY',SYSDATE,v_errcode,v_errmsg); END;
/

Using MERGE does not.

CREATE OR REPLACE TRIGGER TRG_GPS_MOBILITY   AFTER INSERT
  ON EXPORT_DEVICE_STATE
  FOR EACH ROW
/*

    NAME - TRG_GPS_MOBILITY
    DATE - 04-06-2005
*/
 DECLARE

BEGIN

SELECT DEVICE_ID,DEVICE_DSC INTO v_device_id,v_device_dsc
  FROM EXPORT_DEVICE
 WHERE DEVICE_ID = :NEW.DEVICE_ID

 AND ROWNUM = 1; v_prefix := SUBSTR(v_device_dsc,1,3);

CASE
 WHEN (v_prefix = 'ETG') THEN
  MERGE INTO GPS_MOBILITY A
  USING (SELECT v_RemoteName from GPS_MOBILITY WHERE v_RemoteName = v_device_dsc ) B

   ON (A.v_RemoteName = B.v_RemoteName)
  WHEN MATCHED THEN UPDATE SET A.v_d_Latitude= :NEW.latitude, A.v_d_longitude = :NEW.longitude,A.v_UpdateTime = :NEW.lud   WHEN NOT MATCHED THEN INSERT
(A.v_RemoteName,A.v_d_Latitude,A.v_d_longitude,A.v_UpdateTime) VALUES(v_device_dsc,:NEW.latitude,:N;
ELSE

EXCEPTION
 WHEN OTHERS THEN
     v_errcode := SQLCODE;
     v_errcode := SUBSTR(SQLERRM,1,300);
     INSERT INTO ERRORTAB VALUES

(seq_errortab.NEXTVAL,'TRG_GPS_MOBILITY',SYSDATE,v_errcode,v_errmsg); END;
/ Received on Fri Apr 08 2005 - 07:17:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US