How to insert from old table to new table using trigger in pl/sql

There is a column which calls as text (column’s name) inside a table. I want to create different columns from this column. How to insert to another table these columns using trigger. I tried few triggers for this, but i got error. I splitted columns as below.

select 
trim(substr(text,1,62)) col1, 
trim(substr(text,63)) col2, 
systime col3 from table_1
where col4(in table_1)=6

After that, i created a new table existing 3 columns. How can i fill that new table after any changing in old table?

Can someone help me this issue? Thanks in advance.

Trigger i got error is as below. Table1 has text column, New table i created is table2.

CREATE TABLE table2
(column1 VARCHAR2(60),
column2 VARCHAR2(30),
column3 TIMESTAMP);

----------

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_1
FOR EACH ROW

DECLARE

v_column1 table_2.column1%type;
v_column2 table_2.column2%type;
v_column3 table_2.column3%type;

cursor c1 is 
select trim(substr(text,1,62)) column1, 
trim(substr(text,63)) column2,
systime column3 from event_list
where column4=6
and trim(substr(text,63)) not like 'LU%'
and trim(substr(text,1,62)) not like 'COM%';
    
BEGIN

OPEN c1;
LOOP
    FETCH c1 INTO v_column1, v_column2, v_column3;
    EXIT WHEN c1%NOTFOUND;
    INSERT INTO table2
                (column1,
                column2,
                column3)
    VALUES      (v_column1,
                v_column2,
                v_column3);
                
END LOOP;
CLOSE c1;
COMMIT;     
END;
/

Answer

Access the row that is being inserted with :new (resp. new without the colon in the WHEN clause). You cannot commit inside the trigger; its insert will be commited or rolled back with the transaction it occurs in.

CREATE OR REPLACE TRIGGER trg_fromt1_to_t2
AFTER INSERT ON table_1
FOR EACH ROW
WHEN 
(
  new.column4 = 6
  AND TRIM(SUBSTR(new.text, 63)) NOT LIKE 'LU%'
  AND TRIM(SUBSTR(new.text, 1, 62)) NOT LIKE 'COM%'
)
BEGIN
  INSERT INTO table2 (column1, column2, column3)
  VALUES (TRIM(SUBSTR(:new.text, 1, 62)), TRIM(SUBSTR(:new.text, 63)), :new.systime);
END trg_fromt1_to_t2;

Advertisement

Answer