Trigger not firing for one particular CLOB column

问题:

We have a C++ legacy application that connects to an Oracle 11g database. The application uses Microsoft Data Access Objects (DAO) library to allow record browsing and modification. We also have some triggers on tables to track row updates and insertions.

The problem is that the triggers don't fire for the CLOB columns that we have in our tables. It gets fired for other columns but for this one CLOB column, it neither fires during update nor during delete. I've added the trigger for all three: UPDATE, INSERT and DELETE.

Is there some option that manages triggers for CLOBs? Or some other setting that might be affecting this? Any ideas where should I look for a solution?

回答1:

I found a possible explanation to this non-firing trigger:

Your trigger actually works -- WHEN it is fired!

The problem is -- you are NOT updating the table when you set the lob value. You might be modifying the lob contents but -- and this is key -- you are NOT modifying the lob locator in the table itself. The row values of the table are not changing, the trigger does NOT fire for the dbms_lob.copy (or write, or trim, or append, or ...)

I think the solution proposed here at askTom requires to have a specific procedure but I haven't quite understand it.