RAM >> CREATE TABLE transaction_test
  2  (id number(2),
  3   value varchar2(25)) ;

Table created.

RAM >>  BEGIN
  2  FOR i IN 1..10 LOOP
  3  INSERT INTO transaction_test VALUES (i , 'No Value') ;
  4  END LOOP ;
  5  COMMIT ;
  6  END ;
  7  /

PL/SQL procedure successfully completed.

RAM >> SELECT * FROM transaction_test ;

        ID VALUE
---------- -------------------------
         1 No Value
         2 No Value
         3 No Value
         4 No Value
         5 No Value
         6 No Value
         7 No Value
         8 No Value
         9 No Value
        10 No Value

RAM >> UPDATE transaction_test
    SET value = 'Transaction A' WHERE id BETWEEN 1 AND 4 ;
4 rows updated.

RAM >> DECLARE
  2   PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4  UPDATE transaction_test
     SET value = 'Transaction B' where id BETWEEN 5 AND 10 ;
  5  COMMIT ;
  6  END ;
  7  /

PL/SQL procedure successfully completed.

RAM >> SELECT * FROM transaction_test ;

        ID VALUE
---------- -------------------------
         1 Transaction A
         2 Transaction A
         3 Transaction A
         4 Transaction A
         5 Transaction B
         6 Transaction B
         7 Transaction B
         8 Transaction B
         9 Transaction B
        10 Transaction B

10 rows selected.

RAM >> ROLLBACK ;
Rollback complete.

RAM >> SELECT * FROM transaction_test ;
        ID VALUE
---------- -------------------------
         1 No Value
         2 No Value
         3 No Value
         4 No Value
         5 Transaction B
         6 Transaction B
         7 Transaction B
         8 Transaction B
         9 Transaction B
        10 Transaction B

10 rows selected.

CREATE TABLE emps AS SELECT * FROM hr.employees ;

CREATE TABLE log (id number, change_date date, comments varchar2(100)) ;

CREATE SEQUENCE log_seq ;

CREATE OR REPLACE PROCEDURE update_sal
    (emp_id emps.employee_id%type, emp_sal emps.salary%type)
AS
    e_error exception ;
    PROCEDURE insert_log
    (emp_id emps.employee_id%type, emp_sal emps.salary%type, status number)
    AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    IF status = 1 THEN
    INSERT INTO log VALUES (log_seq.nextval , sysdate,  'new salary - ' || emp_sal
                                                       || ' for employee ' || emp_id
                                                       || ' completed successfully ');
    COMMIT ;
    ELSE
    INSERT INTO log VALUES (log_seq.nextval , sysdate,  'new salary - ' || emp_sal
                                                       || ' for employee ' || emp_id
                                                         || ' Failed ');
    COMMIT ;
    END IF;
    END ;
BEGIN
    UPDATE emps
    SET salary = emp_sal
    WHERE employee_id = emp_id ;
    IF sql%rowcount = 0 THEN raise e_error  ; END IF ;
    insert_log (emp_id , emp_sal , 1) ;
EXCEPTION
    WHEN e_error THEN
    insert_log (emp_id , emp_sal , 0) ;
END ;
 /

RAM >> -- Existing value
RAM >> EXEC update_sal (100 , 5000)

PL/SQL procedure successfully completed.

RAM >> -- Non existing value
RAM >> EXEC update_sal (555 , 5000)

PL/SQL procedure successfully completed.

RAM >> -- Value Changed
RAM >> SELECT salary FROM emps WHERE employee_id = 100 ;

    SALARY
----------
      5000

RAM >> -- Log table reflects the two updates
RAM >> SELECT * FROM log ;

        ID CHANGE_DA COMMENTS
---------- --------- -----------------------------------------------------------------
         2 16-DEC-12 new salary - 5000 for employee 100 completed successfully
         3 16-DEC-12 new salary - 5000 for employee 555 Failed

RAM >> COL comments FOR a80
RAM >> ROLLBACK ;

Rollback complete.

RAM >> -- Value for employee_id = 100 was rollbacked
RAM >> SELECT salary FROM emps WHERE employee_id = 100 ;

    SALARY
----------
     24002

RAM >> -- Rollback didn't affect log table values
RAM >> SELECT * FROM log ;

        ID CHANGE_DA COMMENTS
---------- --------- -----------------------------------------------------------------
         2 16-DEC-12 new salary - 5000 for employee 100 completed successfully
         3 16-DEC-12 new salary - 5000 for employee 555 Failed

RAM >> CREATE TABLE copy_emp AS select * FROM hr.employees ;

Table created.

RAM >>
RAM >> CREATE TABLE log_emp
  2  (action VARCHAR2(3),
  3   user_n VARCHAR2(25),
  4   change_date DATE,
  5   changed_employee_id NUMBER)
  6  /

Table created.

RAM >>
RAM >> CREATE OR REPLACE TRIGGER log_emp_trig
  2  AFTER UPDATE ON copy_emp
  3  FOR EACH ROW
  4  DECLARE
  5     PRAGMA AUTONOMOUS_TRANSACTION;
  6  BEGIN
  7      INSERT INTO log_emp
  8      VALUES ('DML', USER, sysdate , :old.employee_id);
  9      COMMIT ;
 10  END;
 11  /

Trigger created.

RAM >>
RAM >> -- Before Change
RAM >> SELECT salary FROM copy_emp WHERE employee_id IN (100, 101, 102) ;

    SALARY
----------
     24002
     17000
     17000

RAM >>
RAM >> UPDATE copy_emp SET salary = salary + 500 WHERE employee_id IN (100, 101, 102) ;

3 rows updated.

RAM >>
RAM >> -- After Change
RAM >> SELECT salary FROM copy_emp WHERE employee_id IN (100, 101, 102) ;

    SALARY
----------
     24502
     17500
     17500

RAM >>
RAM >> SELECT action , user_n ,
  2         TO_CHAR(change_date , 'dd/mm/yyyy - hh24:mi:ss') CHANGE_DATE ,
  3         changed_employee_id
  4  FROM log_emp
  5  ORDER BY CHANGE_DATE ;

ACT USER_N                    CHANGE_DATE           CHANGED_EMPLOYEE_ID
--- ------------------------- --------------------- -------------------
DML RAM                       16/12/2012 - 11:29:11                 100
DML RAM                       16/12/2012 - 11:29:11                 102
DML RAM                       16/12/2012 - 11:29:11                 101

RAM >>
RAM >> -- After Rollback
RAM >> ROLLBACK ;

Rollback complete.

RAM >>
RAM >> SELECT salary FROM copy_emp WHERE employee_id IN (100, 101, 102) ;

    SALARY
----------
     24002
     17000
     17000

RAM >>
RAM >> SELECT action , user_n ,
  2         TO_CHAR(change_date , 'dd/mm/yyyy - hh24:mi:ss') CHANGE_DATE ,
  3         changed_employee_id
  4  FROM log_emp
  5  ORDER BY CHANGE_DATE ;

ACT USER_N                    CHANGE_DATE           CHANGED_EMPLOYEE_ID
--- ------------------------- --------------------- -------------------
DML RAM                       16/12/2012 - 11:29:11                 100
DML RAM                       16/12/2012 - 11:29:11                 102
DML RAM                       16/12/2012 - 11:29:11                 101

RAM >>

נכתב ע"י רם קדם