Re: MUPSERT IN TRIGGER

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 08 Apr 2005 08:44:33 -0400
Message-ID: <3bncljF6j63rbU1@individual.net>

JAW wrote:
> 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
> -- PRAGMA autonomous_transaction ;
> v_device_id NUMBER(6);
> v_device_dsc VARCHAR2(255);
> v_prefix VARCHAR2(3);
> v_numrows NUMBER;
> v_errcode NUMBER;
> v_errmsg VARCHAR2(300);
>
> BEGIN
>
> -- check the device table for the description since it is not in
> export_device_state
> -- SQLCODE = +100 when a device is not found and triger would exit
>
> 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
> -- PRAGMA autonomous_transaction ;
> v_device_id NUMBER(6);
> v_device_dsc VARCHAR2(255);
> v_prefix VARCHAR2(3);
> v_numrows NUMBER;
> v_errcode NUMBER;
> v_errmsg VARCHAR2(300);
>
> BEGIN
>
> -- check the device table for the description since it is not in
> export_device_state
> -- SQLCODE = +100 when a device is not found and triger would exit
>
> 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
> -- Is not vehicle we need
> NULL;
> END CASE;
>
> 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;
> /
>

This might do:
MERGE INTO GPS_MOBILITY A
USING DUAL
ON A.v_RemoteName = v_device_dsc
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);

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Fri Apr 08 2005 - 07:44:33 CDT