Home » SQL & PL/SQL » SQL & PL/SQL » How to use trigger on blob column
icon8.gif  How to use trigger on blob column [message #226904] Mon, 26 March 2007 21:13 Go to next message
leton2008
Messages: 5
Registered: March 2007
Junior Member
I have 2 tables.these two tables have a blob column .
now I want to write a trigger on table one ,and when insert/update table one ,and this operation can make new data to table two.

But I found trigger can not work on trigger column.I search this question on internet,but I have not found a good way to resolve it.

my database is oracle 9,does any body can help me?
thx for help,contact me msn:leton2008#hotmail.com #=@.
Re: How to use trigger on blob column [message #226950 is a reply to message #226904] Tue, 27 March 2007 00:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Post your code and the error you got.
Re: How to use trigger on blob column [message #226958 is a reply to message #226950] Tue, 27 March 2007 00:32 Go to previous messageGo to next message
leton2008
Messages: 5
Registered: March 2007
Junior Member
I have not got any errorMessages.
If you know how to do it .may be your can show me a code block for example.
btw,thx for your reply
Re: How to use trigger on blob column [message #226962 is a reply to message #226904] Tue, 27 March 2007 00:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I have not got any errorMessages.
Then you have no problem that needs our assistance.
Congratulations. Problem has been solved!
Re: How to use trigger on blob column [message #226967 is a reply to message #226962] Tue, 27 March 2007 00:37 Go to previous messageGo to next message
leton2008
Messages: 5
Registered: March 2007
Junior Member
NOooooooooooo...
My problem has not been solved.
So If anybody know how to do it,show me your example code.thx..
Re: How to use trigger on blob column [message #226970 is a reply to message #226967] Tue, 27 March 2007 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, we don't show our example code.
You show us yours first.
Re: How to use trigger on blob column [message #226971 is a reply to message #226904] Tue, 27 March 2007 00:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You said you have no error.

So what can we fix for you if nothing is broken?

Do you honestly expect us to write your code for you?
Why should we do all the work, while you get all the credit & get paid for the results?
Re: How to use trigger on blob column [message #226977 is a reply to message #226971] Tue, 27 March 2007 00:47 Go to previous messageGo to next message
leton2008
Messages: 5
Registered: March 2007
Junior Member
OH,I see ,the code is :


-- create a global table just to store the blob column info.
create global temporary table FOO_TMP
(
  X BLOB
)
on commit delete rows;

--create the test table
create table TEST
(
  AA VARCHAR2(20),
  BB VARCHAR2(20),
  CC BLOB
)

--create the test table
create table TESTBLOB
(
  AA VARCHAR2(20),
  BB VARCHAR2(20),
  CC BLOB
)



-- creat trigger for insert on test
create or replace trigger trg_test_ins
  before insert on test  
  for each row
declare

begin
  -- insert null on blob column
  insert into testblob (aa,bb,cc) values (:NEW.aa, :NEW.bb, empty_blob() );
end trg_test_ins;


--trigger for update
create or replace trigger trg_test_upd
  before update on test  
  for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; 

begin
     -- update columns not blob
     update testblob set aa=:NEW.aa, bb=:NEW.bb where aa=:NEW.aa;
     
     --update blob column
     insert into foo_tmp select cc from test where aa=:NEW.aa; 
     update testblob set cc = ( select x from foo_tmp where rownum=1 ) where  aa=:NEW.aa;

     commit; --submit or rollback transaction 
end trg_test_upd;

[Updated on: Tue, 27 March 2007 00:48]

Report message to a moderator

Re: How to use trigger on blob column [message #226980 is a reply to message #226977] Tue, 27 March 2007 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why the autonomous transaction? This is a bug.
What if the original transaction gets rolledback?

Now, what exactly is your problem?
Re: How to use trigger on blob column [message #226984 is a reply to message #226980] Tue, 27 March 2007 01:04 Go to previous message
leton2008
Messages: 5
Registered: March 2007
Junior Member
First plz forgive my poor english writting.May be I have not descript my problem for your.
First,I have two table like these:
--create the test table
create table TEST
(
AA VARCHAR2(20),
BB VARCHAR2(20),
CC BLOB
)

--create the test table
create table TESTBLOB
(
AA VARCHAR2(20),
BB VARCHAR2(20),
CC BLOB
)

Then,I want to use trigger impelements that when I insert a new row into table test or I update one row on table test.I want the new data will insert to table testblob ,and the table testblob just can insert data(teacher's required).

So I write the code like that I have to show you.
Did you have know my exactly problem?

Previous Topic: Concatenation
Next Topic: Send email with attachement through PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 18:51:56 CST 2016

Total time taken to generate the page: 0.07196 seconds