At 02:37 PM 08-02-01 -0500, you wrote:

>Hi Guys,
>
>How do I get IB to respect table constraints? For instance, I have the
>following table:
>
>CREATE TABLE xStore
>(
> StoreID Integer DEFAULT 0 NOT NULL,
> StoreName varchar(25) NOT NULL,
> StoreCode VarChar(25) DEFAULT '' NOT NULL,
> LastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> PRIMARY KEY (StoreID)
>);
>
>When I insert a record with StoreCode missing, I get an error message that
>StoreCode is required; it should be populated with the default specified in
>the metadata.

No, Delphi requires a value for any NOT NULL column that is specified in
the INSERT statement. Defaults work only if the insert statement doesn't
contain the column in question; a default of 0 on the primary key doesn't
make sense unless your trigger handles it.

Rather than create an exception in the trigger, you should handle the case...

>Also, I have triggers for insert and update which are not firing.
>
>CREATE TRIGGER trgInsertStore FOR xStore
> BEFORE INSERT POSITION 0
> As BEGIN
> /* EXCEPTION I_FIRED; */

if you raise an exception, the SP will exit here.

if )(new.StoreID is null) or (new.StoreID < 1)) then

> NEW.StoreID = GEN_ID(genStore, 100);
> NEW.LastUpdate = CURRENT_TIMESTAMP;
> END !!
>
>When I use a TIBOQuery component to insert a record, the exception doesn't
>fire.
>
>I know that I can use GeneratorLinks to make generators fire, but I really
>need triggers because they are capable of doing other cool stuff also like
>updating the LastUpdate field with the timestamp. How can I get triggers to
>fire?

Well, don't raise an exception that stops it from firing (as you have done
here).

And make sure the trigger can actually handle the data it is likely to receive.

If you are inserting into a scrollable dataset, you should use
GeneratorLinks or, in your case because you are incrementing by a factor >
1, you should call the Gen_ID function in your BeforeInsert event.

If your insert call is via DSQL, you could simply pass 0 or a negative
number; or remove the generated column entirely from the statement, e.g.

Insert into xStore(
StoreName,
StoreCode)
Values('Something',99);

Alternatively, use ColumnAttributes to set the column's REQUIRED attribute
to False. But, REPEAT, this is not recommended for inserts into a
scrollable dataset - it will stuff up your KeyLinks.

Helen


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