Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> MUPSERT IN TRIGGER
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
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
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
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
v_errcode := SQLCODE; v_errcode := SUBSTR(SQLERRM,1,300); INSERT INTO ERRORTAB VALUES