473,320 Members | 1,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DB21034E with triggers

Hi!

My trigger looks like this:

CREATE TRIGGER REVIZIJA.T_1_AU AFTER UPDATE ON TABLE1
REFERENCING OLD O
NEW N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

DECLARE OLD_VALUE VARCHAR(120);
DECLARE NEW_VALUE VARCHAR(120);

SET OLD_VALUE = SUBSTR(O.VALUE, 1, 120);
SET NEW_VALUE = SUBSTR(N.VALUE, 1, 120);

INSERT INTO LOG.TABLE (ID, OLD, NEW) VALUES (NEXTVAL FOR TABLE_SEQ,
OLD_VALUE, NEW_VALUE);
END

It is true that VALUE column in table TABLE1is defined as
VARCHAR(30000). Is there any way to make this trigger compile?

Best regards,
Kovi

--

____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 27 '08 #1
4 2850
Gregor Kovač wrote:
Hi!

My trigger looks like this:

CREATE TRIGGER REVIZIJA.T_1_AU AFTER UPDATE ON TABLE1
REFERENCING OLD O
NEW N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

DECLARE OLD_VALUE VARCHAR(120);
DECLARE NEW_VALUE VARCHAR(120);

SET OLD_VALUE = SUBSTR(O.VALUE, 1, 120);
SET NEW_VALUE = SUBSTR(N.VALUE, 1, 120);

INSERT INTO LOG.TABLE (ID, OLD, NEW) VALUES (NEXTVAL FOR TABLE_SEQ,
OLD_VALUE, NEW_VALUE);
END

It is true that VALUE column in table TABLE1is defined as
VARCHAR(30000). Is there any way to make this trigger compile?
I think you may be stuck here. DB2 needs to be able to build a temp with
both the old and new value.

VARCHAR(32000) is a lot of text....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 28 '08 #2
Hi!

Yes, a lot of text. It is just that I'm building sort of an archiving
system and on update I'd need new and old values.
Can I solve this some other way?

Best regards,
Kovi

Serge Rielau pravi:
Gregor Kovač wrote:
>Hi!

My trigger looks like this:

CREATE TRIGGER REVIZIJA.T_1_AU AFTER UPDATE ON TABLE1
REFERENCING OLD O
NEW N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

DECLARE OLD_VALUE VARCHAR(120);
DECLARE NEW_VALUE VARCHAR(120);

SET OLD_VALUE = SUBSTR(O.VALUE, 1, 120);
SET NEW_VALUE = SUBSTR(N.VALUE, 1, 120);

INSERT INTO LOG.TABLE (ID, OLD, NEW) VALUES (NEXTVAL FOR TABLE_SEQ,
OLD_VALUE, NEW_VALUE);
END

It is true that VALUE column in table TABLE1is defined as
VARCHAR(30000). Is there any way to make this trigger compile?
I think you may be stuck here. DB2 needs to be able to build a temp
with both the old and new value.

VARCHAR(32000) is a lot of text....
--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 29 '08 #3
Gregor Kovač wrote:
Hi!

Yes, a lot of text. It is just that I'm building sort of an archiving
system and on update I'd need new and old values.
Can I solve this some other way?
Why do you need to store both before and after image?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 30 '08 #4
Unfortunately, yes.

Best regards,
Kovi

Serge Rielau pravi:
Gregor Kovač wrote:
>Hi!

Yes, a lot of text. It is just that I'm building sort of an archiving
system and on update I'd need new and old values.
Can I solve this some other way?
Why do you need to store both before and after image?

--
____________________________
|http://kovica.blogspot.com|
-----------------------------~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 30 '08 #5

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

Similar topics

4
by: Mark Flippin | last post by:
I'm just starting to use triggers in my databases and find the support in Enterpise Manager lacking. Using Enterprise Manager and Query Analyzer you can maintain the triggers, but it's...
1
by: tim.pascoe | last post by:
I'm trying to generate scrips for a database, and everything so far has worked fine, except for the triggers. When I try and script existing triggers, all I get is a blank file - no SQL script. I...
4
by: stacdab | last post by:
We have a partitioned view with 4 underlying tables. The view and each of the underlying tables are in seperate databases on the same server. Inserts and deletes on the view work fine. We then...
1
by: Maria | last post by:
Hello we got a script automatically generated to populate a table that belongs to a partition table, then while executing we get this error message: "DB21034E The command was processed as an...
4
by: jacelyn Yswan via DBMonster.com | last post by:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL20075 The index "SQL050303003553230"...
0
debasisdas
by: debasisdas | last post by:
trigger sample code Ex#10 ======================= INSTEAD OF TRIGGER ---------------------------------------- create or replace trigger mytrig instead of delete or insert or update on eview...
0
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful. TRIGGERS: =============== Database trigger is a PL/SQL block that is...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
4
by: saravananmc | last post by:
Hi All, I am new to Stored Procedures, Cursors. I am getting an error "DB21034E The command was processed as an SQL statement because it was invalid Command Line Processor command. During SQL...
0
by: ccoq | last post by:
I received the below error message when I ran this command ALTER TABLE WAC.SUPPRESSED_CUSTOMER ALTER COLUMN CUSTOMER_ID SET DATA TYPE VARCHAR(28) DB21034E The command was processed as an SQL...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.