Devart Forums

Hello together,

after some investigation I found out, that when I have a trigger running I get the error when I update a record the second time.

More detailed:
- editing the first record - commit the changes
- editing the second record - commit the changes
- editing the first record again - trying to commit the changes - getting the above error

when I disable the trigger everything works without the error. The data the trigger changes is not changed while editing the record (the data is not part of the select (update) statement). The trigger stores the time and date of changing the record.

Any ideas ?

Thanks

Michael

Try to set StrictUpdate option of TOraQuery to False, and check if a record can be updated successfully (you see new data when opening the query next time). If so, probably that is some Oracle problem - the count of rows affected by the update query is returned incorrectly.

I tried this before. When setting StrictUpdate to False the effect is as expected - there is no instant error message but the data is not updated at all. I don't believe that it is a "problem" with oracle I rather believe there is something wrong with ODAC or, more reasonable, with my program. The reason for this opinion is, that toad from quest for example is working without any problem. I can update records in every manner without getting the problem. If you want to reproduce the problem - here is my current environment.

Oracle XE
latest ODAC
Delphi 2006

the following trigger is used:

CREATE OR REPLACE TRIGGER USR_MOD_BU
BEFORE UPDATE
ON USR_MOD
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
:NEW.UDate := SYSDATE;
:NEW.UUser := USER;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;

the following table is used:

CREATE TABLE USR_MOD
(
IDX NUMBER NOT NULL,
IDX_USR NUMBER NOT NULL,
MOD_HUEL INTEGER DEFAULT 1,
MOD_CONF INTEGER DEFAULT 1,
UDATE DATE,
UUSER VARCHAR2(50 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX USR_MOD_PK ON USR_MOD
(IDX)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

the idx field of the table is filled by a before insert trigger and a sequence but is not important here i think because the problem is with existing records when updating the second time.

Possibly, all fields are added to WHERE clause of update statement because your table has no primary key. To see the update statement that is executed when you modify a record, set Debug property of TOraQuery component to True, and add OdacVcl unit to the 'uses' clause.
If there are all fields in WHERE, you can resolve the problem by setting KeyFields property of TOraQuery to IDX.

Thanks for the hint, the problem seams to be solved. I had not defined a unique index. At a first view the problem is gone after making idx a unique index.