--------------------------------------------------------------------------------
----------------------------------- SCRIPT ONE ---------------------------------
--------------------------------------------------------------------------------

SCRIPT
BEGIN

EXECUTE IMMEDIATE 'CREATE DATABASE "DBS" PATH ''D:\[Development]\Projects\FD\Test\Server''';

EXECUTE IMMEDIATE 'CREATE DATABASE "DBL" PATH ''D:\[Development]\Projects\FD\Test\Local''';

END

--------------------------------------------------------------------------------
----------------------------------- SCRIPT TWO ---------------------------------
--------------------------------------------------------------------------------

SCRIPT
BEGIN

/************************************************************
* Table 1
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "Table1"
(
"Field1" VARCHAR(8) COLLATE "ANSI",
"Field2" INTEGER DEFAULT 0,
"Field3" VARCHAR(8) COLLATE "ANSI"
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'ALTER TABLE "Table1"
ADD CONSTRAINT "Primary_Key" PRIMARY KEY ("Field3")';

/************************************************************
* Table 2
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "Table2"
(
"Field1" VARCHAR(8) COLLATE "ANSI",
"Field2" VARCHAR(8) COLLATE "ANSI",
"Field3" VARCHAR(8) COLLATE "ANSI"
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'ALTER TABLE "Table2"
ADD CONSTRAINT "Primary_Key" PRIMARY KEY ("Field3")';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger1" AFTER INSERT ON "Table2"
WHEN NOT LOADINGUPDATES()
BEGIN 
    DECLARE Cursor1 CURSOR FOR Statement1;
    DECLARE Temp INTEGER DEFAULT 0;

    PREPARE Statement1 FROM ''SELECT Field2 FROM Table1 WHERE Field3 = ?'';
    OPEN Cursor1 USING NEWROW.Field1;
    IF ROWCOUNT(Cursor1) = 1 THEN
        FETCH FIRST FROM Cursor1 (Field2) INTO Temp;
        UPDATE Cursor1 SET Field2 = Temp + 5;
    END IF;
    CLOSE Cursor1;
    UNPREPARE Statement1;
END';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger2" AFTER UPDATE ON "Table2"
WHEN NOT LOADINGUPDATES()
BEGIN 
    DECLARE Cursor1 CURSOR FOR Statement1;
    DECLARE Temp INTEGER DEFAULT 0;

    PREPARE Statement1 FROM ''SELECT Field2 FROM Table1 WHERE Field3 = ?'';
    OPEN Cursor1 USING NEWROW.Field1;
    IF ROWCOUNT(Cursor1) = 1 THEN
        FETCH FIRST FROM Cursor1 (Field2) INTO Temp;
        UPDATE Cursor1 SET Field2 = Temp + 1000;
    END IF;
    CLOSE Cursor1;
    UNPREPARE Statement1;
END';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger3" AFTER DELETE ON "Table2"
WHEN NOT LOADINGUPDATES()
BEGIN 
    DECLARE Cursor1 CURSOR FOR Statement1;
    DECLARE Temp INTEGER DEFAULT 0;

    PREPARE Statement1 FROM ''SELECT Field2 FROM Table1 WHERE Field3 = ?'';
    OPEN Cursor1 USING OLDROW.Field1;
    IF ROWCOUNT(Cursor1) = 1 THEN
        FETCH FIRST FROM Cursor1 (Field2) INTO Temp;
        UPDATE Cursor1 SET Field2 = Temp - 1;
    END IF;
    CLOSE Cursor1;
    UNPREPARE Statement1;
END';

/************************************************************
* Publish Database
************************************************************/

EXECUTE IMMEDIATE 'PUBLISH DATABASE "DBS"';


/************************************************************
* Table Rows
************************************************************/

EXECUTE IMMEDIATE 'INSERT INTO "Table1" VALUES (''HOAG'', 0, ''HOAG-S1'')';

EXECUTE IMMEDIATE 'INSERT INTO "Table1" VALUES (''HODN'', 0, ''HODN-S2'')';

EXECUTE IMMEDIATE 'INSERT INTO "Table2" VALUES (''HOAG-S1'', ''HOAG'', ''HOAG-I1'')';

EXECUTE IMMEDIATE 'INSERT INTO "Table2" VALUES (''HODN-S2'', ''HODN'', ''HOAG-I2'')';


/************************************************************
* Procedures
************************************************************/

EXECUTE IMMEDIATE 'CREATE PROCEDURE "Save_Updates" ()
BEGIN    
    EXECUTE IMMEDIATE ''SAVE UPDATES FOR DATABASE "DBS" AS "TU1" TO STORE "Updates" TABLES Table1'';
END';

END

--------------------------------------------------------------------------------
---------------------------------- SCRIPT THREE --------------------------------
--------------------------------------------------------------------------------

SCRIPT
BEGIN

/************************************************************
* Table 1
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "Table1"
(
"Field1" VARCHAR(8) COLLATE "ANSI",
"Field2" INTEGER DEFAULT 0,
"Field3" VARCHAR(8) COLLATE "ANSI"
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'ALTER TABLE "Table1"
ADD CONSTRAINT "Primary_Key" PRIMARY KEY ("Field3")';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger1" BEFORE ALL ON "Table1"
WHEN LOADINGUPDATES()
BEGIN 
    IF OPERATION() = ''Delete'' THEN
        IF OLDROW.Field1  <> ''HOAG'' THEN
            RAISE ERROR CODE 10000 MESSAGE ''Record Excluded'';
        END IF;
    ELSE
        IF NEWROW.Field1  <> ''HOAG'' THEN
            RAISE ERROR CODE 10000 MESSAGE ''Record Excluded'';
        END IF;
    END IF;
END';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger2" ERROR ALL ON "Table1"
WHEN LOADINGUPDATES()
BEGIN
END';

/************************************************************
* Table 2
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "Table2"
(
"Field1" VARCHAR(8) COLLATE "ANSI",
"Field2" VARCHAR(8) COLLATE "ANSI",
"Field3" VARCHAR(8) COLLATE "ANSI"
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'ALTER TABLE "Table2"
ADD CONSTRAINT "Primary_Key" PRIMARY KEY ("Field3")';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger1" AFTER INSERT ON "Table2"
WHEN NOT LOADINGUPDATES()
BEGIN 
    DECLARE Cursor1 CURSOR FOR Statement1;
    DECLARE Temp INTEGER DEFAULT 0;

    PREPARE Statement1 FROM ''SELECT Field2 FROM Table1 WHERE Field3 = ?'';
    OPEN Cursor1 USING NEWROW.Field1;
    IF ROWCOUNT(Cursor1) = 1 THEN
        FETCH FIRST FROM Cursor1 (Field2) INTO Temp;
        UPDATE Cursor1 SET Field2 = Temp + 5;
    END IF;
    CLOSE Cursor1;
    UNPREPARE Statement1;
END';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger2" AFTER UPDATE ON "Table2"
WHEN NOT LOADINGUPDATES()
BEGIN 
    DECLARE Cursor1 CURSOR FOR Statement1;
    DECLARE Temp INTEGER DEFAULT 0;

    PREPARE Statement1 FROM ''SELECT Field2 FROM Table1 WHERE Field3 = ?'';
    OPEN Cursor1 USING NEWROW.Field1;
    IF ROWCOUNT(Cursor1) = 1 THEN
        FETCH FIRST FROM Cursor1 (Field2) INTO Temp;
        UPDATE Cursor1 SET Field2 = Temp + 1000;
    END IF;
    CLOSE Cursor1;
    UNPREPARE Statement1;
END';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger3" AFTER DELETE ON "Table2"
WHEN NOT LOADINGUPDATES()
BEGIN 
    DECLARE Cursor1 CURSOR FOR Statement1;
    DECLARE Temp INTEGER DEFAULT 0;

    PREPARE Statement1 FROM ''SELECT Field2 FROM Table1 WHERE Field3 = ?'';
    OPEN Cursor1 USING OLDROW.Field1;
    IF ROWCOUNT(Cursor1) = 1 THEN
        FETCH FIRST FROM Cursor1 (Field2) INTO Temp;
        UPDATE Cursor1 SET Field2 = Temp - 1;
    END IF;
    CLOSE Cursor1;
    UNPREPARE Statement1;
END';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger4" BEFORE ALL ON "Table2"
WHEN LOADINGUPDATES()
BEGIN 
    IF OPERATION() = ''Delete'' THEN
        IF OLDROW.Field2  <> ''HOAG'' THEN
            RAISE ERROR CODE 10000 MESSAGE ''Record Excluded'';
        END IF;
    ELSE
        IF NEWROW.Field2  <> ''HOAG'' THEN
            RAISE ERROR CODE 10000 MESSAGE ''Record Excluded'';
        END IF;
    END IF;
END';

EXECUTE IMMEDIATE 'CREATE TRIGGER "Trigger5" ERROR ALL ON "Table2"
WHEN LOADINGUPDATES()
BEGIN
END';

/************************************************************
* Publish Database
************************************************************/

EXECUTE IMMEDIATE 'PUBLISH DATABASE "DBL"';

/************************************************************
* Procedures
************************************************************/

EXECUTE IMMEDIATE 'CREATE PROCEDURE "Load_Updates" ()
BEGIN     
    EXECUTE IMMEDIATE ''LOAD UPDATES FOR DATABASE "DBL" FROM "TU1" IN STORE "Updates"'';
END';

END



€