Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Triggers |
Wed, Jul 14 2010 4:43 AM | Permanent Link |
Ivan | 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. |
Wed, Jul 14 2010 5:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Jul 14 2010 5:23 AM | Permanent Link |
Ivan | 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); |
Wed, Jul 14 2010 5:25 AM | Permanent Link |
Ivan | 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. |
Wed, Jul 14 2010 6:51 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Ivan,
You can replace EXECUTE IMMEDIATE 'INSERT INTO Test (ProjectGUID) VALUES (''' + PG + ''')'; with: EXECUTE IMMEDIATE 'INSERT INTO Test (ProjectGUID) VALUES (?)' USING PG; Richard Harding |
Thu, Jul 15 2010 6:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |