Alexander Rupsch wrote :
>I want to migrate a schema from oracle to sapdb. the schema is changing from
>time to time, so i want to
>import original schema with minimal changes. that's why I'm trying to import
>it in oracle mode (via repmcli).
>it does well for tables and sequences but not for triggers.
>Here a simple version for what i want to do:
>// The table, runs well..
>CREATE TABLE "TEST"."MYTEST"
>(
> "ID" Number (20,0) DEFAULT 0,
> "NAME" Varchar(20) ASCII
>)
>// the sequnece, runs as well
>CREATE SEQUENCE "ID_GEN" INCREMENT BY 1 MINVALUE 0 NOMAXVALUE NOCYCLE CACHE
<100 ORDER
>// the trigger, makes trouble!
>CREATE OR REPLACE TRIGGER "MYTRIGGER" AFTER ON "MYTEST"
<BEGIN
> UPDATE "TEST"."MYTEST"
> SET id = (SELECT ID_GEN.NextVal FROM DUAL)
< WHERE id = 0
>END
>The Problem for the moment is the creation of the trigger (yes, the update
>clause may not do what i expect, but that's for later).
>When I try to run the statement thru SQLStudio I get the following error:
>--> Syntax error or access violation;-5015 POS(20) Missing
>keyword:VIEW,PACKAGE.
>By removing the "OR REPLACE" I get:
>--> Syntax error or access violation;-3014 POS(29) Invalid end of SQL
>statement.
>I played around with quotation marks which changes nothing...
>Hope someone can help, thanks.....
The Syntax of your create trigger statement is wrong. Please try
CREATE TRIGGER "MYTRIGGER" FOR MYTEST AFTER INSERT EXECUTE (
UPDATE "TEST"."MYTEST"
SET id = (SELECT ID_GEN.NextVal FROM DUAL)
WHERE id = 0;
)
Please note, that you don't need the select expression in the update statement
and that you should check the return code :
CREATE TRIGGER "MYTRIGGER" FOR MYTEST AFTER INSERT EXECUTE (
UPDATE "TEST"."MYTEST"
SET id = ID_GEN.NextVal
WHERE id = 0;
IF $RC <> 0 THEN STOP($RC);
)
Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
