Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger with blob column problem

515915May 6 2007 — edited May 7 2007
I have a instead of trigger on a view with blob column. Deleting and inserting on this view is ok, but updating not work.

These are view, trigger and base table:

base table:
SQL> desc trademark_basics;
Name Null? Type
----------------------------------------- -------- ----------------------------
TRADEMARK_CODE NOT NULL VARCHAR2(20)
IMAGE BLOB
COLOR_OF_MARK VARCHAR2(200)
DISCLAIMERS VARCHAR2(200)
PREVIOUS_OWNER_CODE CHAR(6)
PENDING_CHANGE NUMBER(1)
PREVIOUS_SEARCH_CODE VARCHAR2(20)
REMARKS VARCHAR2(3000)

view:
create or replace view v$trademark_basics as select
t.TRADEMARK_CODE,t.IMAGE,t.COLOR_OF_MARK,t.DISCLAIMERS,
t.PREVIOUS_OWNER_CODE,t.PENDING_CHANGE,t.PREVIOUS_SEARCH_CODE,t.REMARKS,
ac.action_type_code,ac.due_date
from trademark_basics t,
(select a.code,a.prosecution_work_code,b.DUE_DATE,b.ACTION_TYPE_CODE from
(select max(code) code,prosecution_work_code
from actions
group by prosecution_work_code) a,
actions b
where a.code=b.code) ac
where ac.prosecution_work_code(+)=t.trademark_code;

trigger:
create or replace trigger trig$trademark_basics
instead of insert or update or delete on v$trademark_basics
for each row
begin
if deleting then
delete from trademark_basics where TRADEMARK_CODE=:old.trademark_code;
elsif inserting then
insert into trademark_basics values(
:new.TRADEMARK_CODE,:new.IMAGE,
:new.color_of_mark,:new.disclaimers,:new.PREVIOUS_OWNER_CODE,
:new.PENDING_CHANGE,:new.PREVIOUS_SEARCH_CODE,:new.REMARKS);
elsif updating then
update trademark_basics set
TRADEMARK_CODE=:new.TRADEMARK_CODE,
IMAGE=:new.IMAGE,
color_of_mark=:new.color_of_mark,
disclaimers=:new.disclaimers,
PREVIOUS_OWNER_CODE=:new.PREVIOUS_OWNER_CODE,
PENDING_CHANGE=:new.PENDING_CHANGE,
PREVIOUS_SEARCH_CODE=:new.PREVIOUS_SEARCH_CODE,
REMARKS=:new.REMARKS
where TRADEMARK_CODE=:old.trademark_code;
end if;
end;

About Oracle database:
Release 9.2.0.1.0 on Windows Server 2003

Please help me, thanks you very much
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 4 2007
Added on May 6 2007
8 comments
1,029 views