3 Replies Latest reply on Dec 5, 2012 1:17 PM by myOra_help

    requirement

    PVM
      Hi All,

      suppose i am deleting some rows from one table and those deleted rows have to insert into other empty table...how can we do that? i know this is very simple..but i am not an sql or pl/sql expert..

      Thanks,
      Mani
        • 1. Re: requirement
          Chanchal Wankhade
          Hi,
          You can achive this by creating trigger.
          create trigger trig on emp after delete
          for each row
          begin
          insert into emp_bkp values(:old.empno,:old.empname);
          end;
          Read more about triggers.
          • 2. Re: requirement
            Paul  Horth
            Manikandan wrote:
            Hi All,

            suppose i am deleting some rows from one table and those deleted rows have to insert into other empty table...how can we do that? i know this is very simple..but i am not an sql or pl/sql expert..

            Thanks,
            Mani
            insert into new_table
            select <whatever> from old_table
            where <some conditions>;
            
            delete from old_table
            where <some conditions>;
            • 3. Re: requirement
              myOra_help
              In PLSQL, you can use RETURNING INTO clause (an extention to DML) in INSERT/UPDATE/DELETE staement to return affected rows by the statement. like. (not tested)
              DECLARE
                l_id t1.id%TYPE;
              BEGIN
                INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')
                RETURNING id INTO l_id;
                COMMIT;
              
                DBMS_OUTPUT.put_line('ID=' || l_id);
              
              
                UPDATE t1
                SET    description = description
                WHERE  description = 'FOUR'
                RETURNING id INTO l_id;
              
                DBMS_OUTPUT.put_line('UPDATE ID=' || l_id);
              
                DELETE FROM t1
                WHERE  description = 'FOUR'
                RETURNING id INTO l_id;
              
                DBMS_OUTPUT.put_line('DELETE ID=' || l_id);
              
                COMMIT;
              END;
              
              Output
              ---------------
              ID=4
              UPDATE ID=4
              DELETE ID=4
              
              PL/SQL procedure successfully completed.