Update trigger in oracle on updating a column
If you are at the conference, drop into the Groundbreaker area and say Hello. I could not give a Blobfield in the trigger-observed Field List ... Greetings from Dresden, Andreas Näther Well, this is a sticky issue (i think we can solve it though).Check out all our database development sessions at OOW19 Hallo Tom, I 'd like to use a trigger in Oracle as follows : 1. in case of Updating the Blob-Field by any user-action the fiered Update-trigger makes a copy of the Blob to an other Blob in an other table I use this kind of trigger-action with "normal" Fields fora long time. The problem is two fold:o Triggers are not supported on LOBs.Likewise, use of a view in combination with an INSTEAD OF trigger would likely bomb due to Bug 1992006 (Assuming the use of ODBC, SQL*Loader, Oracle Forms etc.).On the other hand, these issues seem to have been solved by mod_plsql, since I can upload a file directly into a documents table, and the mime_type, doc_size, and last_updated columns all get populated.My requirement is when effective_date = current_date it should update and if it is not equal then it should insert.It is working but while inserting if we modify any data then it is not reflecting.
Which in turn results in much better insert performance. Example what works for an INSERT create sequence tmp_idea_insert_seq cache 10000;create table tmp_idea_insert ( id number default tmp_idea_insert_seq.nextval not null ,name varchar2(100 byte) ,inserted_by varchar2(128) default coalesce(sys_context('userenv','client_identifier'),sys_context('userenv','session_user')) not null ,inserted_on date default sysdate not null ); insert into tmp_idea_insert (name) values ('Test');select * from tmp_idea_insert; ID NAME INSERTED_BY INSERTED_ON1 Test MYSELF Of cause this is not restricted to an insert.Syntax could also use the keyword ALWAYS instead of DEFAULT On UPDATE.This might be a bit more in line with the current logic.Now I need that feature for the blobs too -but it seems it is very difficult. However, you can use a LOB in the body of a trigger as follows:- in regular triggers old values may be read but not written and new values may not be read nor written.- in 'instead of triggers', the:old and:new values can be read but not written.o dbms_lob.write (and other dbms_lob calls) do not fire a trigger -- people can and will change the value of a blob and you will not be able to detect it with a trigger.In order to update a LOB, I might never issue an UPDATE. I am a DBA for a large development team, and just received some new ddl that has a table with 2 blob columns.