Can a trigger catch updates done by a select for update + lob.write()?

All we need is an easy explanation of the problem, so here it is.

It’s maybe a stupid question, but I didn’t find an explicit answer…

I want to catch any update on my oracle db, so I want to use triggers, but the triggers not been activated when using SELECT FOR UPDATE, then lob.write(...) and COMMIT. (I have tested it with c++ using occi)

Is there any way to catch it in an update (or any other) trigger?

I find it hard to believe that there is command that can’t be monitored by any trigger…

If it’s matter I’m using version 11g.

Thanks!

EDIT

Trigger for example:

CREATE OR REPLACE TRIGGER TR1
BEFORE DELETE OR INSERT OR UPDATE ON TEST_CLOB 
FOR EACH ROW
BEGIN
IF DELETING THEN
  WRITE_LOG('LOG_DIR','deleting:'||:old.filename);
  goto endproc;
END IF;
IF INSERTING THEN
  WRITE_LOG('LOG_DIR','inserting:'||:new.filename);
  goto endproc;
END IF;
IF UPDATING THEN
  WRITE_LOG('LOG_DIR','updating:'||:old.filename||'-'||:new.filename);
  goto endproc;
END IF;
WRITE_LOG('LOG_DIR','other:'||:old.filename||'-'||:new.filename);
<<endproc>>
NULL;
END;

Now in my log I see the inserting and deleting, but I never see any update.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Copied comment :

You can’t do that , at least up to 12c : “Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not fire triggers defined on the table that contains the columns or attributes.” (docs.oracle.com/database/121/ADDCI/lobs.htm#ADDCI4383 )

Method 2

A SELECT FOR UPDATE is not an UPDATE. It is a SELECT that also places a lock, so that it impacts read-consistency of other sessions.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply