Re: MUPSERT IN TRIGGER

From: JAW <jwilliam_at_aglresources.com>
Date: 4 May 2005 13:05:38 -0700
Message-ID: <1115237138.940187.10970@g14g2000cwa.googlegroups.com>

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
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

82 CASE
83 WHEN (v_prefix = 'ETG') THEN

    84     MERGE INTO GPS_MOBILITY A USING DUAL
    85      ON A.v_RemoteName = v_device_dsc
    86     WHEN MATCHED THEN UPDATE SET A.v_d_Latitude= :NEW.latitude,
A.v_d_longitude = :NEW.longitude,A.v_UpdateTime = :NEW.lud
    87     WHEN NOT MATCHED THEN INSERT

(LTRIM(RTRIM(SUBSTR(v_device_dsc,instr(v_device_dsc,' ',-1),length(v_device_dsc)))),A.v_d_La
titude,A.v_d_longitude,A.v_UpdateTime)
VALUES(v_device_dsc,:NEW.latitude,:NEW.longitude,:NEW.lud);

    88 ELSE
    89 -- Is not vehicle we need
    90 NULL;
    91 END CASE;
    92
    93 EXCEPTION
    94 WHEN OTHERS THEN

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

(seq_errortab.NEXTVAL,'TRG_GPS_MOBILITY',SYSDATE,v_errcode,v_errmsg); "gps_nui_trigger.sql" 99 lines, 2730 characters

OPS$ORACLE>@gps_nui_trigger
Connected.
CREATE PUBLIC SYNONYM GPS_MOBILITY FOR GPS.GPS_MOBILITY

ERROR at line 1:
ORA-00955: name is already used by an existing object

Connected.
ALTER USER gps IDENTIFIED BY VALUES '9C4210CF71314AA0'; Connected.

Table dropped.

Table created.

Table altered.

Grant succeeded.

Table dropped.

Table created.

Sequence dropped.

Sequence created.

Table altered.

Warning: Trigger created with compilation errors.

OPS$ORACLE>show errors
Errors for TRIGGER TRG_GPS_MOBILITY:

LINE/COL ERROR

24/4     PL/SQL: SQL Statement ignored
25/8     PL/SQL: ORA-00969: missing ON keyword

OPS$ORACLE>edit Received on Wed May 04 2005 - 15:05:38 CDT