Herumurti, D., Subakti, M.M.I. and Rupawan, D.K.I. (2006) An Oracle’s Mutating Table Visual Detector Using Table’s Trigger Dependency Method

Abstract

A visualization is a popular method for the problem modeling. By using visualization processing, a problem is easier to comprehend, analyze and solve. In an active database (e.g., Oracle), the occurences of the mutating table is usual. A mutating table is a table which is currently being modified by an update, delete, or insert statement. When a trigger tries to refer a table which is in state of flux (i.e., being changed), it is considered as mutating. And it raises an error since Oracle should not return data which has not yet reached its final state. This problem is difficult to trace manually (i.e, by reading the source code of trigger) and it found frequently only at the runtime.

This paper describes a method for mutating table detection by a visualization (i.e, visualizing trigger dependency on the table) and then tracing it. Visualization processing is done by parsing the body of trigger, and furthermore it can be shown in a diagram. A dependency trigger shows the events (insert, update, delete) and the actions from the table. Here we proposed two mechanism to overcome the mutating table: (1) Chain Reaction Verification Algorithm which is used for comparing the previous Action trigger with the next event trigger; and (2) Vertex to Circuit Expansion (VtCX) Algorithm as an expansion principal from vertices to circuit. This expansion is done by substituting a vertex in path of one single circuit with another single circuit.

Finally, base on our experiments, trigger can be visualized into visual diagram. Together with graph theory, this diagram detects a mutating table easier , better than reading the source code directly.

Keywords

mutating table, trigger, active database, graph theory, diagram.