Operating System - OpenVMS
cancel
Showing results for 
Search instead for 
Did you mean: 

the problem about oracle logical backup

 
ora_lion
Advisor

the problem about oracle logical backup

there is two tables, test_bak and test
when i insert a record such as:
insert into test values('backup test_bak');

after insert a record into test,the table test_bak will be backuped auto(use exp/expdp)
i want to use trigger,but don't know how to write.
any body can help me,thank you.
3 REPLIES 3
Hoff
Honored Contributor

Re: the problem about oracle logical backup

Might want to check with Oracle and with the Oracle Metalink resources and the Oracle product documentation over there.

http://www.oracle.com


Hein van den Heuvel
Honored Contributor

Re: the problem about oracle logical backup

As Hoff points out, this is really a pure Oracle question, with no OpenVMS specific compontent.

What have you tried so far?

The best place to start this quest is the Oracle SQL Ref Manual under "CREATE TRIGGER".
It points to several other manuals for foundation knowledge.

Typically you would hook up some "anonymous PL/SQL" to the trigger.

CREATE TRIGGER my_schema.copy_test_insert
AFTER INSERT ON test
BEGIN
-- plsql stuff...
INSERT INTO test_bak(test_column)
VALUES ( :new.test_column);
END

Good luck!
Hein.
Hein van den Heuvel
Honored Contributor

Re: the problem about oracle logical backup

Just FYI, the SQL code below, copied from a SQLdeveloper session against an Oracle XE server on my laptop running Windoze XP, worked as expected to clone inserts.

Hein.

CREATE TABLE "HEIN"."TEST"
( "X" VARCHAR2(20 BYTE),
"Y" VARCHAR2(20 BYTE)
)

CREATE OR REPLACE TRIGGER "HEIN"."DUPLICATE_TEST_INSERTS"
after insert on test
for each row
BEGIN
insert into test_duplicate ( insert_date, x, y )
VALUES ( sysdate, :new.x, :new.y );
END;

/
ALTER TRIGGER "HEIN"."DUPLICATE_TEST_INSERTS" ENABLE;


CREATE TABLE "HEIN"."TEST_DUPLICATE"
( "X" VARCHAR2(20 BYTE),
"Y" VARCHAR2(20 BYTE),
"INSERT_DATE" DATE
);


insert into test values ('hello', 'world');

select x,y,to_char(insert_date,'dd/mm/yy hh:mi:ss') from test_duplicate;