-
Hi ,
I created a database trigger. In that i am dynamically inserting the values.
the questions is i am using EXECUTE IMMEDIATE.
when i am executing the database trigger, data is not inserting into the corresponding table.
can anyone help me for this issue..
i am using Oracle8i under HPunix 10.2.
I copied my coding here..
Jgun..
CREATE OR REPLACE TRIGGER T_DYN AFTER
INSERT ON EMP FOR EACH ROW
DECLARE
CURSOR C1 IS
SELECT COLUMN_NAME
FROM SYS.USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP';
v_ins_col VARCHAR2(100);
v_ins_val VARCHAR2(100);
v_ins_col_st VARCHAR2(100);
v_ins_val_st VARCHAR2(100);
v_column_name VARCHAR2(100);
BEGIN
v_ins_col := ' ';
v_ins_val := ' ';
v_ins_col_st := 'INSERT INTO AUDIT_EMP(';
v_ins_val_st := 'VALUES(';
OPEN C1;
WHILE 1=1 LOOP
FETCH C1 INTO v_column_name;
IF (C1%NOTFOUND) THEN
EXIT;
ELSE
IF (v_ins_col != ' ') THEN
v_ins_col := v_ins_col || ',';
END IF;
IF (v_ins_val != ' ') THEN
v_ins_val := v_ins_val || ',';
END IF;
END IF;
v_ins_col := v_ins_col || v_column_name;
v_ins_val := v_ins_val ||':new.'|| v_column_name;
END LOOP;
CLOSE C1;
v_ins_col := v_ins_col_st ||v_ins_col || ')';
v_ins_val := v_ins_val_st||v_ins_val || ')';
v_ins_col := v_ins_col ||v_ins_val;
EXECUTE IMMEDIATE v_ins_col;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|