Weird error using :new in trigger

752032

Hi buddies:

I'm using 10g and I've created an after insert trigger:

CREATE OR REPLACE TRIGGER "TR_INSERT_PERSON" AFTER
INSERT ON "PERSON" FOR EACH ROW BEGIN
INSERT INTO CHANGETABLE (ROWGUID, OPERATION, DATEOPERATION, TABLEID) VALUES (:NEW.UUID, 2, SYSDATE, 'PERSON');
END;

When I apply, it shows me the error message: "Missing IN or OUT parameter at index:: 1"
I know the cause of the error is the ':NEW' parameter, because when I replace it with a value it works, but I'm on a trigger body, what's wrong?

Thanx a lot in advance.

Solomon Yakobson

Try the following in SQL*Plus:

CREATE OR REPLACE
  TRIGGER KIKO.TR_INSERT_PERSON
    AFTER INSERT
    ON KIKO.PERSON
    FOR EACH ROW
    BEGIN 
        INSERT
          INTO CHANGETABLE(
                           ROWGUID,
                           OPERATION,
                           DATEOPERATION,
                           TABLEID
                          )
          VALUES (
                  :NEW.UUID,
                  2,
                  SYSDATE,
                  'PERSON'
                 );
END;
/
SHOW ERRORS TRIGGER KIKO.TR_INSERT_PERSON

SY.

Hoek

Does :NEW.UUID have any value?
Usually these columns are populated through calls to some SEQUENCE.NEXTVAL value.
How are you populating the column?

Solomon Yakobson

Are you creating trigger using OEM and target database 9i? If so, there is a bug. Use other tool, e.g. SQL*Plus.

SY.

752032

In this case the UUID column is a nvarchar2(100) and is provided in the insert action....

Thanx for your response.

752032

No, I'm using OEM from client 10g and oracle database 10g

Thanx for your response...

Syed Ullah

Try putting a DBMS_OUTPUT.PUT_LINE() in your trigger to print the value of :NEW.UUID
and then do an insert in SQLPLUS to see what value gets printed for :NEW.UUID.

752032

The problem is: I can't save the trigger, it shows me the error message: "Missing IN or OUT parameter at index:: 1" and fails...
I'm working over the OEM

Syed Ullah

Try saving it using SQLPLUS and if that works that may indicate some bug on the OEM side.

752032

When I execute the script:

CREATE OR REPLACE TRIGGER "KIKO"."TR_INSERT_PERSON" AFTER
INSERT ON "KIKO"."PERSON" FOR EACH ROW BEGIN
INSERT INTO CHANGETABLE (ROWGUID, OPERATION, DATEOPERATION, TABLEID) VALUES (:NEW.UUID, 2, (SELECT SYSDATE FROM DUAL), 'PERSON');
END;Connect sys/****@orcl AS SYSDBA

It shows me the warning: "Trigger created with compilation errors."
It seems like a syntax or semantic error

Syed Ullah

Type 'show error' to see the errors.

Solomon Yakobson

Try the following in SQL*Plus:

CREATE OR REPLACE
  TRIGGER KIKO.TR_INSERT_PERSON
    AFTER INSERT
    ON KIKO.PERSON
    FOR EACH ROW
    BEGIN 
        INSERT
          INTO CHANGETABLE(
                           ROWGUID,
                           OPERATION,
                           DATEOPERATION,
                           TABLEID
                          )
          VALUES (
                  :NEW.UUID,
                  2,
                  SYSDATE,
                  'PERSON'
                 );
END;
/
SHOW ERRORS TRIGGER KIKO.TR_INSERT_PERSON

SY.