Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Is it possible to get name of View that caused the trigger to execute? |
Tue, Dec 3 2013 6:01 PM | Permanent Link |
Barry | Views don't have triggers, but when a new row is inserted into a view, the underlying table's insert trigger will be fired. This is all fine and good, but can this trigger determine the name of the view that triggered it?
I'm asking because I want to have several views on a table where each view will display only rows with a certain code value. To simplify things, lets say I have a table called "All_States" with thousands of rows pertaining to each state and each row has a State_Code like "TX" or "MA" etc.. The view vTexas will select only rows with State_Code='TX' and there are 50 views defined on the "All_States" table, one for each State. (This is only an example, not an actual working database). Everything is fine except when I insert a row into vTexas, I'd like the Insert Trigger for "All_States" to assign "TX" for State_Code for the row. If I can't do that, then this will shoot down my idea of using updateable Views on this All_States table. Any suggestions? Barry |
Wed, Dec 4 2013 10:47 AM | Permanent Link |
Adam Brett Orixa Systems | Barry
You are not working in exactly the way I would, and I don't think it is possible for a table to know which VIEW is calling it. A way around the problem might be to create a StoredProcedure which returns a dataset and takes an input var to filter the data. In the code of the procedure you can set a variable which you can call in the table definition and also create a Function on the database which is used by the Table to set the value of the field. ALTER TABLE States ALTER COLUMN State as VARCHAR DEFAULT GetState() Then you don't need 50 views. You use 1 SP, passing in the input var, plus 1 GetState and 1 SetState Function GetState and SetState Functions would update the value of a single field in a single row in a dummy table. -- CREATE PROCEDURE "FilteredView" (IN "aCondition" VARCHAR COLLATE ANSI) BEGIN DECLARE Crsr CURSOR WITH RETURN FOR Stmt; Call SetState(aCondition); PREPARE Stmt FROM 'SELECT SomeFields FROM States WHERE AField = '''+aCondition+'''; OPEN Crsr; END -- CREATE FUNCTION GetState RETURNS VARCHAR BEGIN DECLARE Crsr FOR Stmt; PREPARE Stmt FROM 'SELECT "State" FROM "DummyState" '; OPEN Crsr; FETCH Crsr("State") INTO RESULT; RETURN Result; END CREATE PROCEDURE "SetState" (IN "aState" VARCHAR) BEGIN EXECUTE IMMEDIATE 'UPDATE "States" SET "State" = '''+aState+''''; END |
Wed, Dec 4 2013 2:38 PM | Permanent Link |
Barry | Adam,
You've given me something to think about. I have to admit I never use Stored Procedures that returns a cursor because I never found a need for it. I was under the assumption (which may be wrong) that returning a cursor could only be used in the another stored procedure (the one that called it). After looking at your code, how do I get a TEDBTable or TEDBQuery to access the rows returned by the FilteredView stored procedure? 90% of my application is done in Delphi and returning a cursor from a Stored Procedure to Delphi would really be useful if it can be used directly by a TEDBTable or TEDBQuery component. Is there a way to do this? (I believe Firebird/Interbase can do this) TIA Barry |
Wed, Dec 4 2013 3:05 PM | Permanent Link |
Uli Becker | Barry,
> After looking at your code, how do I get a TEDBTable or TEDBQuery to access the rows returned by the FilteredView stored procedure? 90% of my application is done in Delphi and returning a cursor from a Stored Procedure to Delphi would really be useful if it can be used directly by a TEDBTable or TEDBQuery component. I do that quite often in my apps. Just use a TEDBStoredProc component, link a TDatasource component to it and you are all set. You can even add persistent fields to the TEDBStoredProc component and thus use it like a table or query. Regards Uli |
Thu, Dec 5 2013 1:32 AM | Permanent Link |
Barry | Adam & Uli,
Wow! It looks like XMAS came early this year. I didn't realize TEDBStoredProc was derived from TEDBDataset. This opens up a whole new use for Stored Procedures. Tim, shame on you for not including an example in your PDF manuals or having a technical article written about it. Why are you keeping it a secret? <vbg> I spend most of my time in the EDB2SQL.PDF and it wasn't mentioned in there. It was mentioned on page 94 of edb2rsdelphiwin32*.pdf, but it didn't have any example of how stored procedures could dynamically create and return a dataset to Delphi. And because I didn't read every paragraph in the manual, I missed it entirely. Barry (300w light just went on) |
Thu, Dec 5 2013 11:11 AM | Permanent Link |
Adam Brett Orixa Systems | >>Tim, shame on you for not including an example in your PDF manuals or having a technical article written about it. >>Why are you keeping it a secret? <vbg>
Tim is surprisingly good at keeping secrets about incredible features of his products (... A couple I really like (you may already have spotted these yourself): * In EDBMgr, - open a new SQL Script. - Highlight and drag any Function, View, Table, Procedure from the Treeview on the right into the Script. ... EDBMgr converts this DB Object into SQL! * In EDBMgr - Select any DB in a session, with a second version of the DB containing structural changes already created in the same session. - Click on "Reverse Engineer" - Click "Upgrade" Check-box, then under Upgrade Options and select the DB you want to use as a template. - EDBMgr will generate a full script to upgrade your Db to turn it into the selected DB Structure. ... there are others. |
Thu, Dec 5 2013 2:59 PM | Permanent Link |
Barry | >
* In EDBMgr, - open a new SQL Script. - Highlight and drag any Function, View, Table, Procedure from the Treeview on the right into the Script. ... EDBMgr converts this DB Object into SQL! < That's handy. I also like Explorer > SQL (Ctrl-Alt-S) that opens an SQL pane that gets updated automatically with the create item SQL when you click on any item in the Treeview. > * In EDBMgr - Select any DB in a session, with a second version of the DB containing structural changes already created in the same session. - Click on "Reverse Engineer" - Click "Upgrade" Check-box, then under Upgrade Options and select the DB you want to use as a template. - EDBMgr will generate a full script to upgrade your Db to turn it into the selected DB Structure. < I guess this doesn't work for a remote session to a client database and my local C/S database (because of different sessions)? I'll put this on my list of things to try. Thanks, Barry P.S. I think Tim should allow end users to submit technical articles for his review (via email?). If he likes it, he can post it with the rest of his technical articles. That way users have a resource of tips and knowledge at a central location that they can incorporate without Tim having to do it all. (I suspect he's too busy) |
Thu, Dec 5 2013 3:18 PM | Permanent Link |
Raul Team Elevate | On 12/5/2013 2:59 PM, Barry wrote:
> P.S. I think Tim should allow end users to submit technical articles for his review (via email?). If he likes it, he can post it with the rest of his technical articles. That way users have a resource of tips and knowledge at a central location that they can incorporate without Tim having to do it all. (I suspect he's too busy) I'm sure Tim would welcome such articles - speaking from personal experience the issue comes down to actually writing such an article and sending it to him. User editable Wiki might be a way around it though again i see too many Wiki's out there that just have placeholders for topics which might be even worse then not having a wiki. Not to mention this would somehow have to work with Tim's website back end which at this point is custom i believe written by Tim. Raul |
Mon, Dec 9 2013 5:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Barry,
<< Tim, shame on you for not including an example in your PDF manuals or having a technical article written about it. Why are you keeping it a secret? <vbg> >> I know that you're kidding, but on a serious note I do intend to get back on to adding more technical articles to the web site after the first of the year. I've just got some humps to get over coding-wise, and then I'll have some time to dedicate to more how-to stuff, especially for EDB and EWB. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, June 17, 2024 at 07:11 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |