--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
wrote:

> > I have a table Payments, which has these fields:
> > PaymentID (int, connected to generator PaymentID)
> > PaymentDate (date)
> > PaymentMethod (varchar)
> > SaleYear (int)
> > I can successfully link tables and add entries ot them in Access,

using

> > the ODBC driver from:
> > http://www.firebirdsql.org/index.php?op=files&id=odbc
> > <http://www.firebirdsql.org/index.php?op=files&id=odbc>
> >
> > I also created a view:
> > CREATE VIEW CAT_PAYMENTS AS
> > SELECT * FROM Payments
> > WHERE SaleYear = EXTRACT(YEAR FROM CAST('NOW' AS DATE))
> > WITH CHECK OPTION;
> >
> > I would like to be able to use the view in Access so that a particular
> > user can only make entries for SaleYear being this year.
> >
> > I can successfully insert items into the view using Marathon's SQL
> > execution function, and succesfully SELECT data from this view. I can
> > also successfully insert items into the view using a pass-thru

query in

> > Access. However, when I attempt to use a regular query to add data to
> > this view, I get the error:
> > "Single-row update/delete affected more than one row of a linked

table.

> > Unique index contains duplicate values."
> >
> > Has anybody encountered this error or know anything about it? All

I get

> > on the google newsgroups is stuff about checking for dupes, and I

am not

> > violating any unique indexes.
> >
> > To try to trace this problem, I would like to enable tracing in

the ODBC

> > driver to figure out what SQL is sent to the server. However, there
> > don't appear to be any options in the driver dialog box for doing so.
> > Can anyone point me to resources on tracing ODBC calls?
> >
> > Many thanks,
> >
> > Eric
>
> You don't actually insert records into a view - you insert them into the
> table. Marathon is hiding this fact from you perhaps.
> This pass-thru query is also trying to insert into the table - not

the view.

> Your view is simple and only references one table so you may get

errors in

> future if/when your view become joins and reference more than one table.
> In both of these successful cases, the tool is inserting a record

which , I

> suspect, omits the PK field and your trigger does not check for NULL, it
> just creates a gen value. This will be successful.
> If your trigger tests for NULL then you may have been able to insert one
> record only - is your gen field a PK? perhaps not also... it will accept
> NULLs if it is not a PK.
> Show us your table metadata including your trigger and generator.
>
> Alan

Hi Alan,
I certainly agree that the records reside in the table, but my
understanding is that Firebird allows records to be inserted through a
view.

Here's the SQL to create the table:
create table PAYMENTS(
PAYMENTID integer not null,
PAYMENTDATE date,
PAYMENTMETHOD varchar(50) not null,
CCTYPE varchar(50),
CCNAME varchar(200),
CCEXPIREMONTH varchar(20),
CCEXPIREYEAR varchar(10),
AMOUNT float not null,
CCNUMBER varchar(50),
NOTES blob sub_type 0 segment size 80,
PAYMENTFOR varchar(25) not null,
INVITEEID integer not null,
AUCTIONYEAR integer);

alter table PAYMENTS add constraint CHECK_PAYMENTFOR check (PAYMENTFOR
IN ("Item","Entrance Fee","Donation","Other"));

alter table PAYMENTS add constraint CHECKK_AUCTIONYEAR CHECK
(AUCTIONYEAR >= 2003);

/* Primary Key */
alter table PAYMENTS add constraint PK_PAYMENTS primary key (PAYMENTID);

/* Foreign Key */
alter table PAYMENTS add constraint FK1_PAYMENTS foreign key
(INVITEEID) references GUESTS(INVITEEID);

/* Triggers */
create trigger PAYMENTID_INCREMENT for PAYMENTS active before insert
position 0
as
begin
IF (PAYMENTS.PAYMENTID IS NULL) THEN
PAYMENTS.PAYMENTID = GEN_ID(PAYMENTID,1);
end