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

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

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

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

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)

>>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.
  

>
* 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)

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

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