473,574 Members | 3,131 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Instead of Trigger results into SQL1424N

Hi,

I was asked to recreate a new clean database for our developers because
the current one they use is not entirely up to date. So I created a new
database and I run into the followin strange problem. First some facts:

System: DB2 V8.1 Fixpack5 Redhat Linux 8.0 dual processor
Database A is the current database and all DDL I currently have executes
fine. Database B is the new one and is created on the same instance as A.

The following DDL runs fine on DATABASE A but on B it results into
SQL1424N when the Instead of trigger is created. Is there anybody that
knows what to do to solve this?

Thanks

William

DDL and explaination of SQL1424N

SET CURRENT SCHEMA='TEST';

CREATE TABLE T_MEMO(
MEM_NR BIGINT NOT NULL,
MEM_DATE TIMESTAMP NOT NULL,
MEM_DATEINV BIGINT NOT NULL,
MEM_USER BIGINT NOT NULL,
MEM_SUBJECT VARCHAR(75) NOT NULL,
MEM_MEMO VARCHAR(4096),
MEM_ACTION SMALLINT NOT NULL,
MEM_ACTIONDATE TIMESTAMP,
MEM_ACTIONUSER BIGINT,
MEM_AT TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP
NOT NULL
)IN TAB16
;

CREATE TABLE T_MEMOINDEX(
MEMIDX_SRCNR BIGINT NOT NULL,
MEMIDX_REFNR BIGINT NOT NULL,
MEMIDX_NR BIGINT NOT NULL,
MEMIDX_STATE SMALLINT NOT NULL
)IN TAB04
;
CREATE VIEW V_MEMO_4000(
MEM_NR, MEM_MAINREFNR, MEM_REFNR, MEM_DATE, MEM_DATEINV,
MEM_STATE, MEM_USER, MEM_SUBJECT, MEM_MEMO, MEM_ACTION,
MEM_ACTIONDATE, MEM_ACTIONUSER, MEM_AT
)
AS
SELECT
T_MEMO.MEM_NR, T_MEMOINDEX.MEM IDX_REFNR, T_MEMOINDEX.MEM IDX_REFNR,
T_MEMO.MEM_DATE , T_MEMO.MEM_DATE INV,
T_MEMOINDEX.MEM IDX_STATE, T_MEMO.MEM_USER , T_MEMO.MEM_SUBJ ECT,
T_MEMO.MEM_MEMO , T_MEMO.MEM_ACTI ON,
T_MEMO.MEM_ACTI ONDATE, T_MEMO.MEM_ACTI ONUSER, T_MEMO.MEM_AT
FROM
T_MEMO,T_MEMOIN DEX
WHERE
MEMIDX_SRCNR = 4000 AND
MEMIDX_NR = MEM_NR
;

-- INSTEAD OF INSERT
CREATE TRIGGER IOI_V_MEMO_4000
INSTEAD OF INSERT
ON V_MEMO_4000
REFERENCING NEW AS NEXT
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO T_MEMO
(
MEM_NR,
MEM_DATE, MEM_DATEINV, MEM_USER,
MEM_SUBJECT, MEM_MEMO, MEM_ACTION, MEM_ACTIONDATE,
MEM_ACTIONUSER, MEM_AT
)
VALUES
(
NEXT.MEM_NR, NEXT.MEM_DATE,
NEXT.MEM_DATEIN V, NEXT.MEM_USER,
NEXT.MEM_SUBJEC T, NEXT.MEM_MEMO, NEXT.MEM_ACTION ,
NEXT.MEM_ACTION DATE, NEXT.MEM_ACTION USER, NEXT.MEM_AT
);
INSERT INTO T_MEMOINDEX
(
MEMIDX_SRCNR, MEMIDX_REFNR,ME MIDX_NR, MEMIDX_STATE
)
VALUES
(
4000, NEXT.MEM_REFNR, NEXT.MEM_NR, NEXT.MEM_STATE
);
END;

Error Message I get:
SQL1424N Too many references to transition variables and transition table
columns or the row length for these references is too long. Reason
code="2". LINE NUMBER=1. SQLSTATE=54040

Explanation:

The trigger includes a REFERENCING clause that identifies one or
more transition tables and transition variables. The triggered
action of the trigger contains references to transition table
columns or transition variables with one of the following
conditions identified by the reason code:
1 references total more than the limit of the number of columns
in a table

2 sum of the lengths of the references exceeds the maximum
length of a row in a table.

User Response:

Reduce the number of references to transition variables and
transition table columns in the trigger action of the trigger so
that the length is reduced or the total number of such references
is less than the maximum number of columns in a table.

Nov 12 '05 #1
5 4515
Hi,

I found a bizar solution for the problem I described. After carefully
comparing the Databases A and B I noticed that Database A had a temporary
tablespace of the same page size as the one T_MEMO was located in and B
hadn't! So after saying a little prayer I created the temporary
tablespace for B and the Trigger just run fine. As the problem is solved
I'd still like to know why that temporary tablespace is needed.

William

William of Ockham wrote:
Hi,

I was asked to recreate a new clean database for our developers because
the current one they use is not entirely up to date. So I created a new
database and I run into the followin strange problem. First some facts:

System: DB2 V8.1 Fixpack5 Redhat Linux 8.0 dual processor
Database A is the current database and all DDL I currently have executes
fine. Database B is the new one and is created on the same instance as A.

The following DDL runs fine on DATABASE A but on B it results into
SQL1424N when the Instead of trigger is created. Is there anybody that
knows what to do to solve this?

Thanks

William

DDL and explaination of SQL1424N

SET CURRENT SCHEMA='TEST';

CREATE TABLE T_MEMO(
MEM_NR BIGINT NOT NULL,
MEM_DATE TIMESTAMP NOT NULL,
MEM_DATEINV BIGINT NOT NULL,
MEM_USER BIGINT NOT NULL,
MEM_SUBJECT VARCHAR(75) NOT NULL,
MEM_MEMO VARCHAR(4096),
MEM_ACTION SMALLINT NOT NULL,
MEM_ACTIONDATE TIMESTAMP,
MEM_ACTIONUSER BIGINT,
MEM_AT TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP
NOT NULL
)IN TAB16
;

CREATE TABLE T_MEMOINDEX(
MEMIDX_SRCNR BIGINT NOT NULL,
MEMIDX_REFNR BIGINT NOT NULL,
MEMIDX_NR BIGINT NOT NULL,
MEMIDX_STATE SMALLINT NOT NULL
)IN TAB04
;
CREATE VIEW V_MEMO_4000(
MEM_NR, MEM_MAINREFNR, MEM_REFNR, MEM_DATE, MEM_DATEINV,
MEM_STATE, MEM_USER, MEM_SUBJECT, MEM_MEMO, MEM_ACTION,
MEM_ACTIONDATE, MEM_ACTIONUSER, MEM_AT
)
AS
SELECT
T_MEMO.MEM_NR, T_MEMOINDEX.MEM IDX_REFNR, T_MEMOINDEX.MEM IDX_REFNR,
T_MEMO.MEM_DATE , T_MEMO.MEM_DATE INV,
T_MEMOINDEX.MEM IDX_STATE, T_MEMO.MEM_USER , T_MEMO.MEM_SUBJ ECT,
T_MEMO.MEM_MEMO , T_MEMO.MEM_ACTI ON,
T_MEMO.MEM_ACTI ONDATE, T_MEMO.MEM_ACTI ONUSER, T_MEMO.MEM_AT
FROM
T_MEMO,T_MEMOIN DEX
WHERE
MEMIDX_SRCNR = 4000 AND
MEMIDX_NR = MEM_NR
;

-- INSTEAD OF INSERT
CREATE TRIGGER IOI_V_MEMO_4000
INSTEAD OF INSERT
ON V_MEMO_4000
REFERENCING NEW AS NEXT
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO T_MEMO
(
MEM_NR,
MEM_DATE, MEM_DATEINV, MEM_USER,
MEM_SUBJECT, MEM_MEMO, MEM_ACTION, MEM_ACTIONDATE,
MEM_ACTIONUSER, MEM_AT
)
VALUES
(
NEXT.MEM_NR, NEXT.MEM_DATE,
NEXT.MEM_DATEIN V, NEXT.MEM_USER,
NEXT.MEM_SUBJEC T, NEXT.MEM_MEMO, NEXT.MEM_ACTION ,
NEXT.MEM_ACTION DATE, NEXT.MEM_ACTION USER, NEXT.MEM_AT
);
INSERT INTO T_MEMOINDEX
(
MEMIDX_SRCNR, MEMIDX_REFNR,ME MIDX_NR, MEMIDX_STATE
)
VALUES
(
4000, NEXT.MEM_REFNR, NEXT.MEM_NR, NEXT.MEM_STATE
);
END;

Error Message I get:
SQL1424N Too many references to transition variables and transition
table columns or the row length for these references is too long.
Reason code="2". LINE NUMBER=1. SQLSTATE=54040

Explanation:

The trigger includes a REFERENCING clause that identifies one or
more transition tables and transition variables. The triggered
action of the trigger contains references to transition table
columns or transition variables with one of the following
conditions identified by the reason code:
1 references total more than the limit of the number of columns
in a table

2 sum of the lengths of the references exceeds the maximum
length of a row in a table.

User Response:

Reduce the number of references to transition variables and
transition table columns in the trigger action of the trigger so
that the length is reduced or the total number of such references
is less than the maximum number of columns in a table.

Nov 12 '05 #2
Ian
William of Ockham wrote:
Hi,

I found a bizar solution for the problem I described. After carefully
comparing the Databases A and B I noticed that Database A had a
temporary tablespace of the same page size as the one T_MEMO was located
in and B hadn't! So after saying a little prayer I created the temporary
tablespace for B and the Trigger just run fine. As the problem is solved
I'd still like to know why that temporary tablespace is needed.


Because any rows that get materialized in a temporary tablespace
will not fit on to a 4k page. Therefore, you needed a temporary
tablespace with a page size of at least 8kb.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #3
So SQL1424N indicates that the transition variables can't be stored
anywhere when the instead of trigger is fired. Right?
Ian wrote:
William of Ockham wrote:
Hi,

I found a bizar solution for the problem I described. After carefully
comparing the Databases A and B I noticed that Database A had a
temporary tablespace of the same page size as the one T_MEMO was
located in and B hadn't! So after saying a little prayer I created the
temporary tablespace for B and the Trigger just run fine. As the
problem is solved I'd still like to know why that temporary tablespace
is needed.

Because any rows that get materialized in a temporary tablespace
will not fit on to a 4k page. Therefore, you needed a temporary
tablespace with a page size of at least 8kb.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #4
Ian
William of Ockham wrote:
So SQL1424N indicates that the transition variables can't be stored
anywhere when the instead of trigger is fired. Right?


Not the transition variables, the transition table. The "transition
table" is a temporary table, which goes into a temporary tablespace
and has the same requirements for row/page size as standard tables.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #5
DB2 may need to materialiaze the NEW TABLE transition table.
The table gets materialized as a temp.
Given that you ghave a sizable VARCHAR column teh 4k pagesize won't suffize.
I'm curious though: Did you get the message on CREATE TRIGGER or on INSERT?
I wasn't aware that DB2 checks this at CREATE TRIGGER time.

Cheers
Serge
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
6506
by: Stuart McGraw | last post by:
Is Microsoft full of #*$#*% (again) or am I badly misunderstanding something? Quote from Microsoft's T-SQL doc: > INSTEAD OF triggers are executed instead of the triggering action. > These triggers are executed after the inserted and deleted tables > reflecting the changes to the base table are created, but before any > other actions are...
4
1953
by: Dan | last post by:
I am loading data from table A into table B. Certain columns in B have check constraints. I'd like for any rows from A, which violate constraints, to be inserted into a third table, C. When the process is finished, I'll have only good rows in B, and exeption rows in C. I am investigating INSTEAD OF triggers, however my question to the group...
1
2210
by: MM | last post by:
I currently have a trigger on a table which works fine. It performs some audit trail functions. ****************************************************** CREATE TRIGGER trg_1 ON .  FOR INSERT, UPDATE, DELETE AS BEGIN  SET NOCOUNT ON
8
2928
by: Remove the obvious for replies | last post by:
I am using DB2 8.1.4 with SDE and Info Integrator. I am trying to create a trigger to populate a spatial table based on values from an attribute table. However, it appears to not recognize the CASE condition. Here's the trigger : create trigger MDA.TUA_AMNGROWNDIR after update of UTM8315N, UTM8315E on MDA.MNGROWNDIR referencing new as...
5
2183
by: Chris | last post by:
I'm attempting to get Instead Of triggers working. My platform in Solaris, DB2 verison 8.1.x (not sure which). If I create two simple tables with 1 row each: create table test_cc_1 (col1 char(2), col2 integer); insert into test_cc_1 values ('ME', 1); create table test_cc_2 (col1 char(2), int integer, ltime timestamp); insert into...
11
37015
by: serge | last post by:
When i debug a trigger is it possible to add a WATCH on the INSERTED or DELETED? I think not, at least I couldn't figure out a way to do so. Does someone have a suggestion on how I can see the values? I did try to do something like INSERT INTO TABLE1(NAME) SELECT NAME FROM INSERTED
5
5343
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table schema1.emp ( fname varchar(15) not null, lname varchar(15) not null, dob date,
1
1141
by: Jimbo | last post by:
Im having problems with an after insert trigger... Im trying to join the INSERTED table with other tables to retrieve data....Some of these tables it must link to get data inserted into them at the same time my original table does...my question is why when the trigger runs, my sql statement gets no data...however afterwards if I run the...
0
2081
Brad Orders
by: Brad Orders | last post by:
Hi all Here is my situation: When table A is updated, I need to record some data in table B, then apply the update to table A Normally I would use a FOR UPDATE trigger, but the table has a TEXT field in it. Therefore, I must use an INSTEAD OF trigger. Columns may be added to this table without my knowledge or control, and without...
0
7764
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8110
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8273
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7862
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6514
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5658
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5336
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3775
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1375
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.