|Subject||RE: [firebird-support] Firebird, Access and views, ODBC tracing|
> I have a table Payments, which has these fields:You don't actually insert records into a view - you insert them into the
> 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:
> 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,
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.