At 11:42 PM 30-06-01 +0000, you wrote:

>Hi Helen,
>
>I'm using IBConsole to do the creating of table, triggers etc etc.
>
>So when i see the text used to create the trigger disappear after
>clicking on the run button i'd expect it to be IN the database.
>
>In fact when i look at the table and go to the last TAB on the
>resulting window i see the trigger name (as i'd typed it appear) as
>an object.

Then the trigger exists.

[snip]

> To get the above i just copied the sample from the SQL HELP that
> comes with IBConsole, changes the column name and generator name.

Fair enough. The trigger code looked fine to me and you say it is appearing as a metadata object, so it exists.

[snip]

Err as mentioned above i'm using IBConsole is there something

> better I should use (making it easier on me) ?

That's a matter of personal preference. There are several good interactive desktop tools available. I don't use IBConsole myself.

With SQL databases, you create and alter metadata with scripts consisting of multiple data definition language (DDL) statements. You add, update and delete data with SQL data manipulation language (DML) statements, either one at a time as "dynamic SQL" (DSQL) or in batches, through various means, including scripts. A DSQL statement is the usual way to submit an insert, update or delete statement. Various development platforms provide ways to create user-friendly interfaces between user applications and the database.

A tool such as IBConsole is such an application. It is written in Delphi and provides an interactive interface for entering single DSQL statements. This is probably the "data grid" that you referred to. As you typed in values for each column in "insert mode", a middle layer was assembling a statement like "INSERT INTO MYTABLE ( col2, col3, col4) VALUES ( 'a', 'b', 'c')".

What happens when you get to the end of the grid row depends on how the application handles it. I *think* IBConsole probably autocommits the statement when you move onto the next empty row or go out of edit mode.

The app is a "client", a surface layer which, on a Windows client, connects to the Firebird client program gds32.dll. The development tool (in this case Delphi plus data access components) provides more or less support for a set of functions (the API) that gds32.dll uses to communicate with the database server program.

In InterBase, everything is done under transaction control. A transaction provides a set of conditions that isolates one unit of work on the database from another. Once the transaction starts, nothing that happens on the client gets written into the database until that unit of work gets committed.

In the case of triggers, on the client you won't see the result of a trigger's action until *after* the transaction is committed. The grid is only showing you the empty container for a row that won't exist in the database until after the underlying INSERT statement has been committed. If the transaction gets rolled back, the row will never be written into the database.

That's why you don't see the trigger value appearing in your data grid. You won't see it until the visual display in the grid gets refreshed again after the work has been committed.

I guess the big quantum leap you need to make when getting to grips with a client/server database is that your client application never physically touches data. SELECT statements bring extracted sets of data across the wire that are "views" of those sets at a particular moment in time. Data manipulation happens by asking the gds32.dll client program to send messages to the server (via SQL statements) defining what you want to happen. If the server decides it's OK, it will let the work be committed; if not, it will send back an exception. The client program then has to resolve the problem of the uncommitted work and do whatever can be done to ensure that the transaction will end quickly.

> If i used the domain CUSTNUM and had no table column define as
> custnum it'd be a "sin".

A domain is just a "user-defined data type". It's a convenient way to encapsulate all of the behaviour you want onto a specific sort of column - constraints like NOT NULL, UNIQUE, or CHECK VALUE(), defaults, character set, collation order...along with data type of course. It's not a sin to create a domain and then not use it (even if it's a waste of space! <g>)

>SO if i create them both (as shown in the
> original message) how do i get the domain name CUSTNUM to control
> the table column ?.

After the fact, you can't. You create the domain first and commit it. Once it exists, you use it in place of the data type when you define the column, e.g.

CREATE DOMAIN CUSTNUM AS INTEGER;

/* Table: CUSTOMERS, Owner: SYSDBA */

CREATE TABLE CUSTOMERS
(
LASTNAME CHAR(30), /* CHARACTER SET ASCII, */
FIRSTNAME CHAR(30), /* CHARACTER SET ASCII, */
HOMEPHONE CHAR(15), /* CHARACTER SET ASCII, */
CUSTNUM CUSTNUM,
);

In fact, the engine itself creates a unique domain for every column you create if you don't apply your own domain to it. That's neither good nor bad; but most people find defining your own domains beneficial from the points of view of encapsulating the business rules and self-documenting the DDL.

> Also can the Domain name be referance instead of the table column
> in other SQL statements etc.

No. You always refer to columns by their object names (or by aliases in joins or by the special process variables "NEW.Colname" and "OLD.Colname" in triggers - but those are two other stories!)

The idea is that, wherever you want to define a column that has to obey the rules defined by the domain, you use the domain in place of the datatype. If you define an invoice header table, for example, you would define CUSTNUM in there under the CUSTNUM domain. There is one gotcha, though - you can't pass domain names as arguments to a stored procedure or trigger and you can't use them to define variables inside a SP/trigger. You need to know and use the corresponding native datatype in these circumstances.

Also, I'm not comfortable with defining a domain with the same name as a column. Call me pernicketty if you like. I'm not certain it wouldn't raise problems somewhere, sometime.

Oh, why did I comment out the character set declarations? Normally, you define the character set and collation order (if you need to) when you create the database...then all text columns will use those unless they are declared differently.

> (Lo think i'm missing the point of Domains here).

Hope this helps.

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________