db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kathey Marsden <kmarsdende...@sbcglobal.net>
Subject Re: [jira] Commented: (DERBY-1603) ERROR 54038: "Maximum depth of nested triggers was exceeded" occurs when trigger fires after upating 10.1.2.5 jars to 10.1.3.1
Date Mon, 07 Aug 2006 15:11:47 GMT
Kathey Marsden wrote:

> Kathey Marsden wrote:
>
>>>
>> A few questions/concerns  on this issue:
>> 1)  CORRECT TRIGGER DEFINITION:
>
>
I think this is it.  To update the timestamp when info is updated we do 
this:

with 10.1.2.4
ij version 10.1
ij> connect 'jdbc:derby:wombat;create=true';
ij> CREATE TABLE "TEST" (

 "TESTID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START
 WITH 1,
 INCREMENT BY 1),

 "INFO" INTEGER NOT NULL,

 "TIMESTAMP" TIMESTAMP NOT NULL DEFAULT
 '1980-01-01-00.00.00.000000'
 );
0 rows inserted/updated/deleted
ij> CREATE TRIGGER UPDATE_TEST
  AFTER UPDATE ON TEST
  REFERENCING OLD AS OLD
  FOR EACH ROW MODE DB2SQL
  UPDATE TEST SET TIMESTAMP = CURRENT_TIMESTAMP WHERE
  TESTID = OLD.TESTID AND INFO != OLD.INFO ;
0 rows inserted/updated/deleted
ij> INSERT INTO TEST (INFO) VALUES
 (1),
 (2),
 (3);
3 rows inserted/updated/deleted
ij> UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
1 row inserted/updated/deleted

with 10.1.2.5 ...

ij> ij version 10.1
ij> connect 'jdbc:derby:wombat';
ij> UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
1 row inserted/updated/deleted
ij> SELECT * FROM TEST;
TESTID     |INFO       |TIMESTAMP
--------------------------------------------------
1          |1          |1980-01-01 00:00:00.0
2          |1          |2006-08-07 08:08:32.958
3          |3          |1980-01-01 00:00:00.0

3 rows selected



Mime
View raw message