Torsten L�hr wrote :
>Hello there,
>when trying to use SAPDB I stumbled over the following phenomena:
>I created a small table with a TIMESTAMP-column. This column schould always
>list the timestamp of the last time a specific row was changed. To acomplish
>this
>I used a trigger.
>Now, when the trigger fires the database shuts down on the server. I have to
>bring it back online. There is no data loss or anything, but the server puts
>the database offline.
>Sometimes there is a error message, somtetimes not. The error message
>reads: General Error; 800 Implicit SERVERDB restart (connection aborted)
>If you change the datetype from TIMESTAMP to TIME or DATE everything works
>fine.
>Can anyone help me to fix it. BTW I am using a LINUX Server (SUSE 8.1, Kernel
>2.4.19).
>The SAPDB Version is 7.4.3.10 installed from RPMs.
>Bye, Torsten...
>-------------------------- Appendix --------------------------
>Here is a test script with which I was able to reproduce the error.
>// -------------- Crash
>DROP TABLE "DBA"."Test"
//
>CREATE TABLE "DBA"."Test"
>(
> "TSTID" Char (8) BYTE DEFAULT STAMP,
> "TEXT" Char (8) ASCII,
> "_mdate" Timestamp DEFAULT TIMESTAMP,
> PRIMARY KEY ("TSTID")
>)
>//
>CREATE TRIGGER "tstupd" FOR "DBA"."Test" AFTER UPDATE EXECUTE
>(
> UPDATE "DBA"."Test" SET "_mdate"=timestamp WHERE TSTID=:NEW.TSTID;
>)
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test1')
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test2')
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test3')
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test4')
>//
>UPDATE "DBA"."Test" SET TEXT='test10' WHERE TEXT='test1'
>If you change the script like this. It's working.
>// ----------- Work
>DROP TABLE "DBA"."Test"
>//
>CREATE TABLE "DBA"."Test"
>(
> "TSTID" Char (8) BYTE DEFAULT STAMP,
> "TEXT" Char (8) ASCII,
> "_mdate" Time DEFAULT TIME,
> PRIMARY KEY ("TSTID")
>)
>//
>CREATE TRIGGER "tstupd" FOR "DBA"."Test" AFTER UPDATE EXECUTE
>(
> UPDATE "DBA"."Test" SET "_mdate"=time WHERE TSTID=:NEW.TSTID;
>)
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test1')
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test2')
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test3')
>//
>INSERT INTO "DBA"."Test" (TEXT) VALUES ('test4')
>//
>UPDATE "DBA"."Test" SET TEXT='test10' WHERE TEXT='test1'
Your problem is that the trigger defines a endless recursion, i.e. an update of
DBA.TEST fires the
trigger which updates DBA.TEST what fires the trigger again and so on.
On linux platforms this results in a stack overflow which kills die SAPDB kernel. This
a known
problem, but be have not found a solution yet.
To avoid the problem, you must prevent the endless recursion. Inside your trigger code
you can
tell the update statement to ignore the trigger :
UPDATE "DBA"."Test" SET "_mdate"=timestamp WHERE TSTID=:NEW.TSTID IGNORE TRIGGER;
I hope this will solve your problem.
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
