This content has been marked as final. Show 15 replies
You need to explicitly mention commit.Inside a trigger you can write commit as given below
CREATE OR REPLACE TRIGGER bef_ins_emp BEFORE INSERT ON emp FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO emp_audit VALUES ( :new.ename, 'BEFORE INSERT', SYSDATE); COMMIT; END;
is the record inserted when the change is commited by default?I assume you mean: is the insert committed, when the change (that fired the trigger) is commited?
The answer would then be: yes.
I see no reason why you would want to make the trigger an autonomous-transaction and have it commit just the insert.
I wouldn't recommend putting the autonomous transaction directly in the trigger. Mostly because of human error (or the potential to it).
If someone comes along and see the trigger, misses the autonomous transaction declaration (or doesn't know what it is and the ramifications are) then you've got quite a mess on your hands.
Also, if you end up needing more functionality in the trigger (outside of pure auditing, which is one of the VERY few cases i'd actually recommend using an autonomous transaction in) you would need to create another trigger, which gets ugly from a maintenance / understandability perspective.
A better (in my opinion anyway) approach would be something like
create table emp as select * from scott.emp where 1 = 0; create table emp_audit ( ename varchar2(30), log_action varchar2(10), log_date date ); create or replace procedure log_emp_audit ( p_emp_audit emp_audit%rowtype ) is PRAGMA AUTONOMOUS_TRANSACTION; begin insert into emp_audit values p_emp_audit; commit; end log_emp_audit; / CREATE OR REPLACE TRIGGER bef_ins_emp BEFORE INSERT ON emp FOR EACH ROW declare l_emp_audit emp_audit%rowtype; BEGIN l_emp_audit.ename := :new.ename; l_emp_audit.log_action := 'INSERT'; l_emp_audit.log_date := sysdate; log_emp_audit (l_emp_audit); END; / TUBBY_TUBBZ?insert into emp select * from scott.emp where rownum = 1; 1 row created. Elapsed: 00:00:00.62 TUBBY_TUBBZ?rollback; Rollback complete. Elapsed: 00:00:00.57 TUBBY_TUBBZ?select * from emp_audit; ENAME LOG_ACTION LOG_DATE ------------------------------------------------------------------------------------------ ------------------------------ -------------------------- SMITH INSERT 27-SEP-2010 03 04:41 1 row selected. Elapsed: 00:00:00.93 TUBBY_TUBBZ? TUBBY_TUBBZ?select * from emp; no rows selected Elapsed: 00:00:00.51
Forreging wrote:I'm not entirely sure i understood your question. Are you asking how to make the audit data persist even when the transaction does a rollback (please check my last post and you will see a demonstration of that working)?
I wrote a trigger to capture a field change.
it inserts a record to audit table when the field value changes.
I am a newbie.
is the record inserted when the change is commited by default?
or any script to set it (on commit)
If not, can you clarify your question?
Forreging wrote:Assuming no Autonomous Transaction is in place then the insert into the audit table will happen, and will either persist (if the session performs a commit) or not (if the session performs a rollback) just the same as the modified data that fired the trigger (the trigger is bound by the transactional boundaries of the session assuming you don't use an Autonomous Transaction).
I havent stated clearly.
I just wanna ask
if the field is changed but not commited, will it insert a new record into audit table (by default)?
You don`t, can`t and no need to do COMMIT in trigger except the autonomous transaction.
It is in the same transaction with the operation that invokes it, so rollback or commit to that operation will rollback or commit the change done by trigger.
Trigger as below:
First test result with NO COMMIT in trigger:
create or replace TRIGGER TEST BEFORE insert ON EMP FOR EACH ROW begin INSERT INTO EMP_AUDIT VALUES (:NEW.EMPNO, :NEW.ENAME, SYSDATE); END;
Second test result with COMMIT in trigger:
SQL> INSERT INTO EMP VALUES (8081, 'BAI', 'CLERK', 8080, SYSDATE, 5000, NULL, 30); 1 row created. SQL> select * from emp_audit; EMPNO ENAME INS_DATE ---------- ---------- --------- 8081 BAI 28-SEP-10 SQL> rollback; Rollback complete. SQL> select * from emp_audit; no rows selected SQL>
SQL> INSERT INTO EMP VALUES (8081, 'BAI', 'CLERK', 8080, SYSDATE, 5000, NULL, 30); INSERT INTO EMP VALUES (8081, 'BAI', 'CLERK', 8080, SYSDATE, 5000, NULL, 30) * ERROR at line 1: ORA-04092: cannot COMMIT in a trigger ORA-06512: at "PHOENIX.TEST", line 4 ORA-04088: error during execution of trigger 'PHOENIX.TEST'
It would have the same result as your original table. Once a commit is issued the data is persisited in your original table as well as the table that gets manipulated through the trigger. Once a rollback is issued the data is not persisited in your original table as well as the table manipulated through the trigger. You need not do any explicit TCL in a trigger.
But in my case there is no autonomous transaction defined & hence trigger is not able to update the required changes for all* the inserts which invoke the trigger. Although for some inserts, the trigger is able to update the changes.
I have defined a before insert trigger for each row, which updates the average of particular field from particular table (not the table which invoked this trigger). I'm confused what to do. If I define the trigger as autonomous transaction & commit the changes and if the original insert is rollbacked by the application then also this update will still be commited. [ I do not have the access to the application code, so I don't know when & where the commit is given. I can only alter the trigger. ] Please suggest a way out.
Welcome to Forums.
Basically as a good practice it is not adviced to terminate a transaction within a trigger. I would suggest you to change the way you handle the details in the trigger. Do not treat it as an autonomous transaction as there is all means that the original statement that triggers the change you are making in the other table via trigger can deem to fail. Also check your trigger if you have any conditions defined such that only particular inserts trigger the changes.
Guess you are from Western part of India, The VT in my signature does not correspond to (Victoria Terminus) and should not be changed to CST which i dislike as it is the initials of my last name and first name which i will not tolerate to be changed ;)
Hi sir, thnx for the reply. I did not mean it. It was just a humourous way to start.
I do not have any conditions for responding to only particular inserts. I'll post the trigger itself below.
CREATE OR REPLACE TRIGGER HMSTRANS.trg_ST_GRNDT1
BEFORE INSERT OR UPDATE
ON HMSTRANS.T_T_ST_GRNDT REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
Rate1 NUMBER(10,2) ;
Rate2 NUMBER(10,2) ;
Rate NUMBER(10,2) ;
dbms_output.put_line('Before Trigger fired...'||:old.n_item_id||' '||:new.n_item_id );
SELECT ROUND ((AVG (n_purchasecost ) * ( SUM (n_totalqty) - SUM (n_freeqty))) / SUM (n_totalqty), 2), COUNT(*) INTO Rate1, Cnt
FROM T_T_ST_GRNDT grndt where grndt.N_ITEM_ID = :new.N_ITEM_ID
group by N_ITEM_ID ;
Rate2 := (:new.n_purchasecost * ( :new.n_totalqty - :new.n_freeqty )) / :new.n_totalqty;
Rate := ( Rate1 + Rate2 ) / 2;
dbms_output.put_line('Rate:'||rate||' Rate1:'||rate1||' Rate2:'||rate2);
UPDATE hmsmaster.T_M_IN_ITEMHD im set N_AVGPURCOST = Rate where im.N_ITEM_ID = :new.N_ITEM_ID ;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
Every evening I run the query to see how many items are received that day in GRNDT table. But that many number of items are not found to be updated in the ITEMHD with the average purchase cost by this trigger. The purpose is to have the patest purchase cost defined in the master as & when the new receipt is made in the stores.
Should I define it as pragma autonomous transaction & give the commit after update?
Are you sure about the table ITEMHD has all the item_id corresponding to the item_id of GRNDT? Can you execute the following query
and see if there are some mismatch between the tables.
SELECT COUNT(*) FROM GRNDT WHERE ITEM_ID NOT IN (SELECT ITEM_ID FROM ITEMHD);
No need to call me sir, because we are just sharing our knowledge and not teaching others.
There are zero mismatches. There is a referential integrity constraint between the two. So no chance.
My only doubt is that the application may not be issuing coimmit on each save & redo must be getting generated. But then also when it writes to data file, it must update into itemhd also. My problem is I can not see the code. It is being maintained by the vendor.