Oracle if updating

Another advantage of this approach: it allows the statement UPDATE MONEDAS SET MON_OFICIAL = CASE MON_ID WHEN 'A' THEN 'Y' ELSE 'N' END; to work without a problem when the row-level trigger might raise an error if the row with MON_ID = 'A' is updated to Y before the previous official currency is updated to N.

With the compound trigger, both the statement-level and row-level action can be put up in a single trigger.

The compound trigger is useful when you want to accumulate facts that characterize the “for each row” changes and then act on them as a body at “after statement” time. With this in mind, one can see that it make sense to move variable declared in trigger body can be used and assigned value of logged in user in the declarative section itself.

Two popular reasons to use compound trigger are: Hopefully this example with make things more clear. NAME IS NULL) ) THEN v_index := v_index 1; v_emp_changes(v_index).upd_dt := SYSDATE; v_emp_changes(v_index).upd_by := SYS_CONTEXT (' : NEW. So that same computation is not made during after-each-row section, and is computed and stored in a variable just once before row-level execution begins.

Luckily enough, we can fake a staging table using DUAL: create table employees ( employee_id integer not null, employee_name varchar2(100) not null); alter table employees add constraint employee_pk primary key (employee_id); create or replace procedure merge_employee( i_emp_id in integer, i_emp_name in varchar2 ) is begin merge into employees e using (select i_emp_id id, i_emp_name name from dual) s on (e.employee_id = s.id) when matched then update set employee_name = when not matched then insert (employee_id, employee_name) values (s.id, s.name); end; / With the merge statement, we now have a single more complex query instead of the 3 we started with, or the two of the refined approach.

Any changes made in any field of ’employees’ table needs to be logged in as a separate row in audit table ‘aud_empl’. For e.g., consider the same example with another restriction, “Any update of salary should be such that it is not less than 1/12th of maximum salary of any employee, or else an error is raised”.This common state is established at the start of triggering statement and is destroyed after completion of trigger (regardless of trigger being in error or not). SALARY IS NULL) ) THEN v_index := v_index 1; v_emp_changes(v_index).upd_dt := SYSDATE; v_emp_changes(v_index).upd_by := SYS_CONTEXT (' Now any changes in any field of employees will to be written in aud_emp table.If same had to be done without compound-trigger, it might have been required to share data using packages. A beauty of this approach is we were able to access same data ‘v_emp_changes’ between statement and row triggering events.All INSTEAD OF triggers are fired for each row and you cannot narrow down the event by column.Instead you can check to see what columns are updated in the body of the trigger by using the UPDATING ('column_name') clause.

Leave a Reply