Hi All,

I want to write a Trigger to Update a History Table in the Same Database as the Storage Table, try to create a Triger and get the following error:

ElevateDB Error #700 An error was found in the statement at line 4 and column 13 (Invalid expression IDProjects found, the referenced cursor does not exist)

IDProjects EXISTS within the DB, using a Remote Session, the files are present for IDProject but the following SQL returns the above error:

CREATE TRIGGER "Test" BEFORE INSERT
ON "IDDocumentStore"
BEGIN
INSERT INTO IDProjects (ProjectGUID)
VALUES ('llll');
END

So IDDocumentStore is the Primary (Storage) Table and it is simply going to set a test value of IIII in the IDProjects Table wherby the only other field in this table is an incremental field (at the moment)

I have gone through the Help files for ElevateDB and searched the Forums.. can any one shed some light on why this error should be occuring for a very simple Trigger between two tables in the same Database ?

Cheers.

Ivan

There are two things you can do

1. Use EXECUTE IMMEDIATE
2. declare and open a cursor onto the table

No. 1 is easier:

CREATE TRIGGER "Test" BEFORE INSERT
ON "IDDocumentStore"
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO IDProjects (ProjectGUID) VALUES (''llll'')'; <<<< Note the double quotes round IIII
END

No 2  would be something like


CREATE TRIGGER "Test" BEFORE INSERT
ON "IDDocumentStore"
BEGIN
DECLARE Worker SENSITIVE CURSOR FOR WorkerTable;
PREPARE WorkerTable FROM 'SELECT * FROM IDProjects';
OPEN Worker;
INSERT INTO Worker(ProjectGUID) VALUES ('lll'');
CLOSE Worker;
END

But since I use Version 1 I wouldn't guarantee it.

Roy Lambert [Team Elevate]

I have found that the statement below works:

CREATE TRIGGER "Test" AFTER INSERT ON "IDProjects"
BEGIN
DECLARE  PG VARCHAR(50);
SET      PG = NEWROW.ProjectGUID;
EXECUTE IMMEDIATE 'INSERT INTO Test (ProjectGUID) VALUES (''' + PG + ''')';
END

Still feel I should be able to replace:

EXECUTE IMMEDIATE 'INSERT INTO Test (ProjectGUID) VALUES (''' + PG + ''')';

with:

INSERT INTO Test (ProjectGUID) VALUES (PG);

Roy Lambert wrote:

Ivan

There are two things you can do

1. Use EXECUTE IMMEDIATE
2. declare and open a cursor onto the table

No. 1 is easier:

CREATE TRIGGER "Test" BEFORE INSERT
ON "IDDocumentStore"
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO IDProjects (ProjectGUID) VALUES (''llll'')'; <<<< Note the double quotes round IIII
END

No 2  would be something like


CREATE TRIGGER "Test" BEFORE INSERT
ON "IDDocumentStore"
BEGIN
DECLARE Worker SENSITIVE CURSOR FOR WorkerTable;
PREPARE WorkerTable FROM 'SELECT * FROM IDProjects';
OPEN Worker;
INSERT INTO Worker(ProjectGUID) VALUES ('lll'');
CLOSE Worker;
END

But since I use Version 1 I wouldn't guarantee it.

Roy Lambert [Team Elevate]


Thank you Roy for your prompt reply ! Much appreciate it. I will now look at using a Cursor, cheers, thank you.

Ivan,

You can replace

EXECUTE IMMEDIATE 'INSERT INTO Test (ProjectGUID) VALUES (''' + PG + ''')';

with:

EXECUTE IMMEDIATE 'INSERT INTO Test (ProjectGUID) VALUES (?)' USING PG;

Richard Harding

Ivan,

<< Still feel I should be able to replace:

EXECUTE IMMEDIATE 'INSERT INTO Test (ProjectGUID) VALUES (''' + PG + ''')';

with:

INSERT INTO Test (ProjectGUID) VALUES (PG); >>

ElevateDB uses 100% dynamic SQL statement execution in its SQL/PSM
implementation.  At first this seems a bit weird when coming from another
product that uses statically-bound statement execution in SQL/PSM, but it
gets around all of the issues of mixing the two:

1) There isn't any need to have two different types of access, one static
and one dynamic, ala Oracle.

2) It allows you, the developer to control when and how statements are
prepared/executed.  If you prepare a statement in a procedure and don't
unprepare it, then EDB will leave it prepared as long as the outer procedure
manager is prepared.  For example, with .NET you would set up an EDBCommand
for the stored procedure and then call the Prepare method.  Any PREPAREd
statements in the stored procedure will remain prepared until the EDBCommand
is disposed of or unprepared.

3) It avoids all of the issues of mixing DML and DDL, and all of the weird
restrictions on how/when tables can be created or dropped in stored
procedures/functions, as well as all of the compilation/binding issues that
arise from this.  For example, with SQL Server, creating/using a temporary
table in the middle of a stored procedure can cause re-compilation of the
stored procedure.

--
Tim Young
Elevate Software
www.elevatesoft.com