DB21034E with triggers 
October 27th, 2008, 01:45 PM
| | | |
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
|
October 28th, 2008, 05:15 PM
| | | | re: DB21034E with triggers
Gregor Kovač wrote: Quote:
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 |
October 29th, 2008, 07:35 AM
| | | | re: DB21034E with triggers
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: Quote:
Gregor Kovač wrote: Quote:
>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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- |
October 30th, 2008, 02:35 AM
| | | | re: DB21034E with triggers
Gregor Kovač wrote: Quote:
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 |
October 30th, 2008, 08:55 AM
| | | | re: DB21034E with triggers
Unfortunately, yes.
Best regards,
Kovi
Serge Rielau pravi: Quote:
Gregor Kovač wrote: Quote:
>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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- |  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 225,689 network members.
|
