topic the problem about oracle logical backup in Operating System - OpenVMS

the problem about oracle logical backup

ora_lion — Thu, 01 May 2008 06:56:06 GMT

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.

Re: the problem about oracle logical backup

Hoff — Thu, 01 May 2008 14:07:52 GMT

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

http://www.oracle.com

Re: the problem about oracle logical backup

Hein van den Heuvel — Thu, 01 May 2008 17:50:55 GMT

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.

Re: the problem about oracle logical backup

Hein van den Heuvel — Mon, 05 May 2008 14:14:38 GMT

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;