print values

[annecarterfredi]

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.

[annecarterfredi]

On Mar 27, 2:02 pm, "[e-mail]"
<[e-mail]:

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.

[Serge Rielau]

an*************@gmail.com wrote:

On Mar 27, 2:02 pm, "[e-mail]"
<[e-mail]:

>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

[Knut Stolze]

Serge Rielau wrote:

an*************@gmail.com wrote:

>On Mar 27, 2:02 pm, "[e-mail]"
<[e-mail]:

>>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