Continuing with the preceding example, you might want to record attempts made to update the rows of the employees table. To accomplish this, you will need to create a table to hold audit records as well as a BEFORE UPDATE trigger on the employees table, which will insert a record into the audit table whenever someone updates a row in employees table.
To set up the above data structures, you can execute the following SQL statement:
CONN usr/usr
CREATE TABLE emp_updates( emp_id NUMBER(6), job_id VARCHAR2(10), timedate DATE);
CONN /AS SYSDBA
GRANT INSERT on usr.emp_updates TO hr; CONN hr/hr
CREATE OR REPLACE TRIGGER emp_updates_trigger BEFORE UPDATE
ON employees FOR EACH ROW BEGIN
INSERT INTO usr.emp_updates VALUES (:new.employee_id, :new.job_id, SYSDATE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'An error raised in the trigger'); END;
/
With that done, you may run the updateQuickForm.php script discussed in the preceding section to check if your auditing mechanism works as expected. In the form generated by the script, choose rollback and then click Submit. Now, if you count the number of rows in the emp_updates table as follows:
CONN usr/usr;
SELECT * FROM emp_updates;
you should see that the emp_updates table still contains no rows: no rows selected
This indicates that when you roll back an UPDATE operation, it rolls back its audit record as well. It is the expected behavior, since you cannot roll back some effects of a transaction—you can either commit all effects of it or roll them all back.
An attempt to commit only the INSERT statement performed within the trigger will fail because no transaction control statements are allowed in a trigger. So, if you try to recreate the emp_updates_trigger trigger as follows:
CREATE OR REPLACE TRIGGER emp_updates_trigger BEFORE UPDATE
ON employees FOR EACH ROW BEGIN
INSERT INTO usr.emp_updates VALUES (:new.employee_id, :new.job_id, SYSDATE); COMMIT;
ROLLBACK; END;
/
You will get the following errors when running the updateQuickForm.php script: Warning: oci_execute()[function.oci-execute]:ORA-04092: cannot ROLLBACK in a trigger ORA-06512: at "HR.EMP_UPDATES_TRIGGER", line 6 ORA-04092: cannot COMMIT in a trigger ORA-04088: error during execution of trigger 'HR.EMP_UPDATES_TRIGGER'
Fatal error: Query failed: ORA-04092: cannot ROLLBACK in a trigger ORA-06512: at "HR.EMP_UPDATES_TRIGGER", line 6 ORA-04092: cannot COMMIT in a trigger ORA-04088: error during execution of trigger 'HR. EMP_UPDATES_TRIGGER'
The above error messages will be displayed only if you have the display_errors parameter set to On in php.ini.
One way to solve the above problem is to make use of an autonomous transaction. An autonomous transaction is a transaction within another transaction. Being totally independent of the calling transaction, an autonomous transaction lets you perform SQL operations and then either commit or roll back them, without committing or rolling back the calling transaction. Employing an autonomous transaction in this example will allow you to commit the INSERT performed within the emp_updates_trigger trigger independently of the transaction created in the updateQuickForm.php script, thus creating a record in the emp_updates table even if the effects of the UPDATE operation that fired the trigger are rolled back.
The following example shows how to recreate the emp_updates_trigger trigger so that it uses an autonomous transaction.
CREATE OR REPLACE TRIGGER emp_updates_trigger BEFORE UPDATE
ON employees FOR EACH ROW DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; BEGIN
INSERT INTO usr.emp_updates VALUES (:new.employee_id, :new.job_id, SYSDATE);
COMMIT; EXCEPTION
WHEN OTHERS THEN ROLLBACK; END;
/
The above example shows an autonomous transaction implemented in a database trigger. Using an autonomous transaction here ensures that an audit record will be created in the emp_updates table, regardless of whether an UPDATE operation on the employees table is committed or rolled back.
To test the newly created trigger, run the updateQuickForm.php script again and submit the form produced by the script, having the rollback radio button selected. Then, select the emp_updates again as follows:
SELECT * FROM emp_updates;
This time, you should see results that might look like the following: EMP_ID JOB_ID TIMEDATE
--- --- --- 101 AD_VP 29-MAY-06 102 AD_VP 29-MAY-06 101 AD_VP 29-MAY-06 102 AD_VP 29-MAY-06
Note that although you attempted to update only two rows in the employees table, four audit records have been inserted into the emp_updates table. Recall that the updateQuickForm.php script actually performs the UPDATE twice—first, in order to count the number of rows to be updated, and then, to actually update these rows.
Summary
Some operations performed against a database make sense only when grouped together. A classic example involves a transfer of funds between two bank accounts. The only way to perform such an operation safely is to use a transaction. Using transactions lets you group SQL statements together into logical, indivisible units of work, each of which can be either all committed or all rolled back.
applications. The discussion began with a brief overview of Oracle transactions and why you may want to use them in PHP applications built on top of Oracle. Then, it explained how to organize a PHP/Oracle application to effectively control transactions, focusing on the benefits from moving the business logic of a transactional application from PHP to the database. You learned which OCI8 connection function to choose when it comes to using transactions, and how to create simultaneous transactions within the same script. Finally, you saw how to call an independent transaction from within another transaction and looked at the situation where it might be desired.