Topic: Problem with trigger and AnyDAC
|
|
dmozejko
User
Junior Boarder
| Posts: 11 |   |
Karma: 0
|
|
Problem with trigger and AnyDAC
|
|
Posted: 2007/07/19 03:14 |
|
|
|
|
Hi.
I have a problem. I have a trigger, which updates multiple records. It works fine in most cases, but when I use TADquery to manipulate table this trigger works incorrect. I can demonstrates this using NorthWind database (MS SQL Server 2000).
create this trigger at Northwind DB on [Products] table:
CREATE TRIGGER [dbo].[Products_tru] ON [dbo].[Products]
FOR UPDATE
AS
BEGIN
set nocount on;
update [Order Details]
set UnitPrice = A.UnitPrice
from inserted A
inner join [Order Details] B on (B.ProductID = A.ProductID)
set nocount off;
END
GO
It's the simplest example of my problem. This trigger updates unit price in [Order Details], when price has changed in [Products] table (it makes little sens, but it shows my problem  ).
When I use any DB tool (e.g. Enterprise Manager) or component (except AnyDAC's TADQuery) to change Unit Price in Products table, trigger updates all necessary records in Order Details.
Now try use AnyDAC Explorer, open Product table and change unit price. Trigger updates only one record in Order Details  . It should updates all records with adequate ProductID, but it update only first record :/. I don't know why. Is this a bug?
Same is when I use TADQuery in my programs. Attaching TAdUpdateSQL fixes the problem. Weird…
I'm using AnyDAC 1.12.2, MS SQL Server 2000 and BDS 2006.
Can anyone reproduce this problem?
Damian Możejko
|
|
wloochacz
User
Fresh Boarder
| Posts: 9 | |
Karma: 0
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/07/19 06:38 |
|
|
|
|
|
Yes, I have the same problem as described by dmozejko...
|
|
Diman
Admin
Admin
| Posts: 1509 | |
Karma: 20
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/07/19 23:40 |
|
|
|
|
Hello
I see, the trigger is FOR UPDATE, but inside of trigger you are referring to INSERTED. Should I correct it and use UPDATED ?
Regards,
Dmitry
|
|
dmozejko
User
Junior Boarder
| Posts: 11 | |
Karma: 0
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/07/20 02:23 |
|
|
|
|
Hi.
Hmm... I'm using MS SQL Server 2000. There is no such thing as table "UPDATED" at MS SQL Server 2000. There are only tables INSERTED and DELETED. In triggers FOR UPDATE both table exists. In table DELETED you have old values of the changed record, and in table INSERTED you have new values of changed records. This is why I reference to INSERTED table, there are new values of changed records.
Best regards,
Damian Możejko
Post edited by: dmozejko, at: 2007/07/20 02:25
|
|
wloochacz
User
Fresh Boarder
| Posts: 9 | |
Karma: 0
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/07/20 05:07 |
|
|
|
|
Diman wrote:
I see, the trigger is FOR UPDATE, but inside of trigger you are referring to INSERTED. Should I correct it and use UPDATED ?
Diman, check this out:
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
Source:
http://msdn2.microsoft.com/en-us/library/ms191300.aspx
Post edited by: wloochacz, at: 2007/07/20 05:08
|
|
Diman
Admin
Admin
| Posts: 1509 | |
Karma: 20
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/07/20 05:25 |
|
|
|
|
Ok, ok, guys Sorry, my fault. I am investigating issue ...
Regards,
Dmitry
|
|
dmozejko
User
Junior Boarder
| Posts: 11 | |
Karma: 0
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/08/03 04:45 |
|
|
|
|
Hi Diman!
Did you find anything? Can you reproduce this problem?
Regards, Damian
|
|
Diman
Admin
Admin
| Posts: 1509 | |
Karma: 20
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/08/06 23:43 |
|
|
|
|
Hello
To fix issue, open daADPhysODBCBase.pas. Find there TADPhysODBCCommand.SetupStatementBeforePrepare method and comment there code:
// if oFtchOpts.RecsMax <> -1 then
// AStmt.MAX_ROWS := oFtchOpts.RecsMax
// else
// AStmt.MAX_ROWS := 0;
|
Regards,
Dmitry
Post edited by: Diman, at: 2007/08/06 23:44
|
|
dmozejko
User
Junior Boarder
| Posts: 11 | |
Karma: 0
|
|
Re:Problem with trigger and AnyDAC
|
|
Posted: 2007/08/09 05:13 |
|
|
|
|
Hi!
Fix works well. Thank you Dmitry
Best regards,
Damian Możejko
|
|
|
