SQL & PL/SQL

[oracle@localhost ~]$ oerr ora 24344

24344, 00000, "success with compilation error"

// *Cause:  A sql/plsql compilation error occurred.

// *Action: Return OCI_SUCCESS_WITH_INFO along with the error code

//

There are other issues with your trigger in addition to the column list discrepancy.  You name the trigger HOME_BEFORE_DELETE, but define it as before insert.  The old values that you reference in the trigger body are not available in a before insert trigger.

John

Your code should be like following:

Create tables:

create table home (F_NAME VARCHAR2(25),M_NAME VARCHAR2(25),C_NAME VARCHAR2(25));

create table home_details (F_NAME VARCHAR2(25),M_NAME VARCHAR2(25),C_NAME VARCHAR2(25),V_USERNAME varchar2(25));

Create Triggers:

CREATE OR REPLACE TRIGGER HOME_BEFORE_DELETE

BEFORE INSERT

ON HOME

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(25);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO HOME_DETAILS

(F_NAME, M_NAME, C_NAME,V_USERNAME  )

values

(:new.F_NAME,

:new.M_NAME,

:new.C_NAME ,V_USERNAME);

END;

Test:

insert into home values('a','b','c');
commit;

select * from home_details

Output:

a b c user1