Hi All,
My first foray into DELETE TRIGGER has struck a problem demonstrated by the
following SQL through websql into MaxDB
Kernel Kernel 7.6.00 Build 016-123-109-428
Runtime Environment W32/INTEL 7.6.00 Build 016-123-109-428
CREATE TABLE "myTable" ("myTableID" INTEGER KEY DEFAULT SERIAL)
CREATE TRIGGER "myTrigger"
FOR "myTable"
AFTER DELETE
EXECUTE (RETURN;)
/* this insert not required to demonstrate problem */
INSERT INTO "myTable" ("myTableID") VALUES (1)
DELETE FROM "myTable" KEY "myTableID" = 1
/* Fails with message
[MySQL MaxDB][SQLOD32 DLL][MaxDB] General error;-9111
and the knldiag.err entries
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE VAK506:23
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE 2006-08-08 17:38:27 MOVECODE
Error 20011
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE Bad parameter: limit source 11
destination 32768, source [0X7D6D9BA0]+12, destination [0X7F860498]+12, 3 bytes
2006-08-08 17:38:27 0xD1C ERR 51080 SYSERROR -9111 Move error
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE VAK506:23
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE 2006-08-08 17:38:27 MOVECODE
Error 20011
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE Bad parameter: limit source 11
destination 32768, source [0X7D6D9BA0]+12, destination [0X7F860498]+12, 3 bytes
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE +
SAPDB_PascalMoveKernel.cpp:96
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE + 2006-08-08 17:38:27
MOVECODE Error 20013
2006-08-08 17:38:27 0xD1C ERR 20011 MOVECODE - Module VAK506 call index
23
*/
I have found two solutions:
/* A: Remove the default */
ALTER TABLE "myTable" COLUMN "myTableID" DROP DEFAULT
-OR-
/* B: Delete by WHERE clause rather than KEY */
DELETE FROM "myTable" WHERE "myTableID" = 1
As it happens, key values are always provided so losing DEFAULT SERIAL is an
option.
Before I proceed though, am I doing something silly here? I can't see why the
trio of DEFAULT SERIAL, DELETE by KEY and a DELETE TRIGGER should be
problematic.
Either solution requires changes to our domain engine so I'd like to be sure it
actually needs to be done. And there are likely to be other solutions I haven't
considered.
If I went for B option, am I right in thinking MaxDB will execute the WHERE
DELETE on a Key field as efficiently as the explicit KEY syntax?
Thanks for any advice.
James Prosser
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
