Serge Rielau wrote:
> 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
Finally get Oracle installed or just a guess? ;-)
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Apr 08 2005 - 18:44:10 CDT
