By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

print values

P: n/a
I am developing a new trigger and I would like to print Old and New
values of few columns. I know I can create new table and insert Old
and New values, but I would like to have simpler solution for this.

In Oracle,
DBMS_OUTPUT.PUT_LINE('Old Total wages = ' || TO_CHAR(O.total_wages));
DBMS_OUTPUT.PUT_LINE('New Total wages = ' || TO_CHAR(N.total_wages));
would print Old and New values. How to do it in DB2?

Thanks.

Mar 27 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Mar 27, 2:02 pm, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
I am developing a new trigger and I would like to print Old and New
values of few columns. I know I can create new table and insert Old
and New values, but I would like to have simpler solution for this.

In Oracle,
DBMS_OUTPUT.PUT_LINE('Old Total wages = ' || TO_CHAR(O.total_wages));
DBMS_OUTPUT.PUT_LINE('New Total wages = ' || TO_CHAR(N.total_wages));
would print Old and New values. How to do it in DB2?

Thanks.
Someone out there might be wonderign why I need this...here is my
trigger outline:

CREATE TRIGGER trig.sal_B
NO CASCADE BEFORE UPDATE OF total_wages, num_of_years
ON EMP_T
REFERENCING OLD AS O
NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SET N.commision = (N.total_wages*N.num_of_years/1000.00);
END
;

I want to verify what would be Old and New values in these scenarios:
During update of both total_wages, num_of_years
During update of only num_of_years
During update of only total_wages

Thanks.

Mar 27 '07 #2

P: n/a
an*************@gmail.com wrote:
On Mar 27, 2:02 pm, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
>I am developing a new trigger and I would like to print Old and New
values of few columns. I know I can create new table and insert Old
and New values, but I would like to have simpler solution for this.

In Oracle,
DBMS_OUTPUT.PUT_LINE('Old Total wages = ' || TO_CHAR(O.total_wages));
DBMS_OUTPUT.PUT_LINE('New Total wages = ' || TO_CHAR(N.total_wages));
would print Old and New values. How to do it in DB2?

Thanks.

Someone out there might be wonderign why I need this...here is my
trigger outline:

CREATE TRIGGER trig.sal_B
NO CASCADE BEFORE UPDATE OF total_wages, num_of_years
ON EMP_T
REFERENCING OLD AS O
NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SET N.commision = (N.total_wages*N.num_of_years/1000.00);
END
;

I want to verify what would be Old and New values in these scenarios:
During update of both total_wages, num_of_years
During update of only num_of_years
During update of only total_wages
SELECT * FROM NEW TABLE(UPDATE ....)

:-)

If you insist on DBMS_OUTPUT style logging. I propose to write said
stored procedure in SQL and log to a DECLARE GLOBAL TEMPORARY TABLE ..
NOT LOGGED ON ROLLBACK PRESERVE ORWS ON COMMIT PRESERVE ROWS.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 27 '07 #3

P: n/a
Serge Rielau wrote:
an*************@gmail.com wrote:
>On Mar 27, 2:02 pm, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
>>I am developing a new trigger and I would like to print Old and New
values of few columns. I know I can create new table and insert Old
and New values, but I would like to have simpler solution for this.

In Oracle,
DBMS_OUTPUT.PUT_LINE('Old Total wages = ' || TO_CHAR(O.total_wages));
DBMS_OUTPUT.PUT_LINE('New Total wages = ' || TO_CHAR(N.total_wages));
would print Old and New values. How to do it in DB2?

Thanks.

Someone out there might be wonderign why I need this...here is my
trigger outline:

CREATE TRIGGER trig.sal_B
NO CASCADE BEFORE UPDATE OF total_wages, num_of_years
ON EMP_T
REFERENCING OLD AS O
NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SET N.commision = (N.total_wages*N.num_of_years/1000.00);
END
;

I want to verify what would be Old and New values in these scenarios:
During update of both total_wages, num_of_years
During update of only num_of_years
During update of only total_wages

SELECT * FROM NEW TABLE(UPDATE ....)

:-)

If you insist on DBMS_OUTPUT style logging. I propose to write said
stored procedure in SQL and log to a DECLARE GLOBAL TEMPORARY TABLE ..
NOT LOGGED ON ROLLBACK PRESERVE ORWS ON COMMIT PRESERVE ROWS.
Alternatively, use a UDF as is described here: http://tinyurl.com/lu4wg

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Mar 28 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.