Ndtyjky

This is the trigger to check if the actual salary is greater than maximum salary. But it only raised the error and did not record the data in audit table. I want this trigger to insert the data as well as to raise the error.

     Create or replace trigger trg_audit

     BEFORE UPDATE ON lds_placement

     for each row

    BEGIN  

    if :NEW.actual_salary>:new.max_salary then



    insert into pradip_audit_table(audit_date, table_name, operation_type, 

    primary_key_column, primary_key_value,

   column_affected, max_salary, actual_salary, host, ip_adddress, terminal ) 

   values (sysdate, 

    'lds_placement',

    'update', 

    'plt_id', 

     :OLD.placement_id,

     'actual_salary', 

    :new.max_salary, 

    :NEW.actual_salary,

    sys_context('USERENV', 'HOST'),

    sys_context('USERENV', 'IP_ADDRESS', 15),

    sys_context('USERENV', 'TERMINAL'));



   raise_application_error(-20111, 'Salary cannot be more than maximum 

    salary');



end if;

  end;

I advise use autonomic transaction in separate procedure for insert into audit table for this case.
There is documentation
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

Problem is that you do not make commit after insert. And you can't do this in trigger. I'd prefer make log in autonomic transaction and this should help.

Whatever you do in the trigger will follow the fate of the whole transaction.
If you insert the data and then raise an exception, the whole operation will be rolled back.

You need to perform the insert in ANOTHER TRANSACTION. oracle allows you to define functions and procedures that run in a separate transaction from the one of the calling program. You can even define these procedures as local procedures.

This is what you need:

 Create or replace trigger trg_audit  

 AFTER UPDATE ON lds_placement

 for each row

 declare 



     procedure WriteAuditLog is

     pragma autonomous_transaction;

     begin

          insert into pradip_audit_table(

              audit_date, table_name, operation_type,  primary_key_column, primary_key_value,

             column_affected, max_salary, actual_salary, host, ip_adddress, terminal 

          ) 

         values 

         (

            sysdate, 

            'lds_placement',

            'update', 

            'plt_id', 

            :OLD.placement_id,

            'actual_salary', 

            :new.max_salary, 

            :NEW.actual_salary,

            sys_context('USERENV', 'HOST'),

            sys_context('USERENV', 'IP_ADDRESS', 15),

            sys_context('USERENV', 'TERMINAL')

         );

         -- you can't leave a autonomous_transaction function without committing or rolling back:

         --    if you leave the transaction open you get an error!

         commit;                                                  

     exception when others then

        rollback;

        raise;

     end WriteAuditLog;



 BEGIN  

     if :NEW.actual_salary>:new.max_salary then

        WriteAuditLog;

        raise_application_error(-20111, 'Salary cannot be more than maximum  salary');

     end if;

 end;

Please note that I changed the trigger to be run AFTER the statement, not BEFORE.

1. There could be MULTIPLE before triggers on your table


2. Every BEFORE trigger has the opportunity of changing the actual data that will be written


3. you have no guarantee that your trigger will be the last one to be executed.

It is entirely possible that there is another trigger that would cap the salary to the maximum limit and would insert a record that doesn't violate the rule: your trigger, if is run before this one, would make this second trigger useless.

Another thing that could happen is that another trigger gets run after the "before" data has passed your check, and this second trigger could change the data by, for example, doubling the salary and write data that violates your check, regardless of what your trigger is supposed to do.

AFTER triggers can't change the data, since it has already been written, so the right place for this kind of checks, and for logging, is an AFTER trigger.

Notice that is you raise an exception the update command will be rolled back even if you are in an AFTER trigger: don't let the "AFTER" word misguide you.

From Documentation:

In most cases, if a trigger runs a statement that raises an exception,
and the exception is not handled by an exception handler, then the
database rolls back the effects of both the trigger and its triggering
statement.

Oracle creates an implicit savepoint before your update statement, and if either update or trigger cause an exception, then the transaction is rolled back to that savepoint. The solution is to create a separate procedure for your insert, mark it as pragma AUTONOMOUS_TRANSACTION and perform a commit within that procedure; then the data you've inserted won't be rolled back when the trigger fails.