IDUG : Forums : Using a trigger to get a value

Mike Holmans

Using a trigger to get a value

January 13, 2005 06:47 AM

DB2 for z/OS v7.

The requirement seems fairly simple:

- when inserting or updating a row in table B, column X should get its value from column Y of table A.

If DB2 for z/OS supported a SELECT in the SET statement of a trigger, it would be dead simple:

CREATE TRIGGER ON T_ORDERS REFERENCING NEW AS N
BEGIN ATOMIC
SET N.CLASS = (SELECT CLASS FROM T_CUSTOMER WHERE CUSTOMER_ID = N.CUSTOMER_ID);
END;

But DB2 for z/OS V7 does not support that, so I'm wondering how to go about it. Going to DB2 V8 is a possibility if the requisite functionality is available at that release.

Any workable ideas welcome.

Mike Holmans
Lead DBA
BT Exact In-Life Services
[login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Backlund

Re: Using a trigger to get a value

January 13, 2005 06:53 AM (in response to Mike Holmans)

DB2 for z/OS V8 will not provide this functionality.

You can use an AFTER trigger which can update CLASS in T_ORDERS
with the SELECTed value. You have to specify the appropriate WHERE
clause on the update statement

Peter

[login to unmask email] wrote:
DB2 for z/OS v7. The requirement seems fairly simple: - when inserting or updating a row in table B, column X should get its value from column Y of table A. If DB2 for z/OS supported a SELECT in the SET statement of a trigger, it would be dead simple: CREATE TRIGGER ON T_ORDERS REFERENCING NEW AS N BEGIN ATOMIC SET N.CLASS = (SELECT CLASS FROM T_CUSTOMER WHERE CUSTOMER_ID = N.CUSTOMER_ID); END; But DB2 for z/OS V7 does not support that, so I'm wondering how to go about it. Going to DB2 V8 is a possibility if the requisite functionality is available at that release. Any workable ideas welcome. Mike Holmans Lead DBA BT Exact In-Life Services [login to unmask email] --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
-- ******************************************************** * * **** My new e-mail address is [login to unmask email] **** * * ******************************************************** ====> See you in 2005, October 24-27, at IDUG in Berlin <==== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Holmans

Re: Using a trigger to get a value

January 14, 2005 04:46 AM (in response to Peter Backlund)

Peter,

Thanks for that, but now I get a bit more confused. I want to do the same thing for UPDATEs, ie set CLASS to the same as whatever is now in the parent. But if I do that as an after trigger as well, don't I end up endlessly triggering the same trigger?

Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Peter Backlund
Sent: 13 January 2005 12:54
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

DB2 for z/OS V8 will not provide this functionality.

You can use an AFTER trigger which can update CLASS in T_ORDERS
with the SELECTed value. You have to specify the appropriate WHERE
clause on the update statement

Peter

[login to unmask email] wrote:

DB2 for z/OS v7.

The requirement seems fairly simple:

- when inserting or updating a row in table B, column X should get its value from column Y of table A.

If DB2 for z/OS supported a SELECT in the SET statement of a trigger, it would be dead simple:

CREATE TRIGGER ON T_ORDERS REFERENCING NEW AS N

BEGIN ATOMIC

SET N.CLASS = (SELECT CLASS FROM T_CUSTOMER WHERE CUSTOMER_ID = N.CUSTOMER_ID);

END;

But DB2 for z/OS V7 does not support that, so I'm wondering how to go about it. Going to DB2 V8 is a possibility if the requisite functionality is available at that release.

Any workable ideas welcome.

Mike Holmans

Lead DBA

BT Exact In-Life Services

[login to unmask email]

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--

********************************************************

* *

**** My new e-mail address is [login to unmask email] ****

* *

********************************************************

====> See you in 2005, October 24-27, at IDUG in Berlin <====

+-------------------------------+---------------------------------+

| Peter G Backlund | Telephone: 08 - 38 69 08 |

| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |

| Smastuguvagen 2 | Country Code (Sweden): 46 |

| S-165 72 HASSELBY | |

| Sweden | E-mail: [login to unmask email] |

+-------------------------------+---------------------------------+

| IBM Database GOLD Consultant |

+-------------------------------+---------------------------------+

| Working with DB2 since 1981 ... and forever! |

+-----------------------------------------------------------------+
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Backlund

Re: Using a trigger to get a value

January 14, 2005 05:29 AM (in response to Mike Holmans)

Mike,

maybe (I can't test it at home) you could have a WHEN clause
WHEN (n.CLASS <> (Select CLASS from parent...))
UPDATE Child set ...

Peter

[login to unmask email] wrote:

Peter,

Thanks for that, but now I get a bit more confused. I want to do the same thing for UPDATEs, ie set CLASS to the same as whatever is now in the parent. But if I do that as an after trigger as well, don't I end up endlessly triggering the same trigger?

Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]]On Behalf Of Peter Backlund
Sent: 13 January 2005 12:54
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

DB2 for z/OS V8 will not provide this functionality.

You can use an AFTER trigger which can update CLASS in T_ORDERS
with the SELECTed value. You have to specify the appropriate WHERE
clause on the update statement

Peter

[login to unmask email] wrote:
DB2 for z/OS v7. The requirement seems fairly simple: - when inserting or updating a row in table B, column X should get its value from column Y of table A. If DB2 for z/OS supported a SELECT in the SET statement of a trigger, it would be dead simple: CREATE TRIGGER ON T_ORDERS REFERENCING NEW AS N BEGIN ATOMIC SET N.CLASS = (SELECT CLASS FROM T_CUSTOMER WHERE CUSTOMER_ID = N.CUSTOMER_ID); END; But DB2 for z/OS V7 does not support that, so I'm wondering how to go about it. Going to DB2 V8 is a possibility if the requisite functionality is available at that release. Any workable ideas welcome. Mike Holmans Lead DBA BT Exact In-Life Services [login to unmask email] --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

-- ******************************************************** * * **** My new e-mail address is [login to unmask email] **** * * ******************************************************** ====> See you in 2005, October 24-27, at IDUG in Berlin <==== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
-- ******************************************************** * * **** My new e-mail address is [login to unmask email] **** * * ******************************************************** ====> See you in 2005, October 24-27, at IDUG in Berlin <==== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Holmans

Re: Using a trigger to get a value

January 14, 2005 06:49 AM (in response to Peter Backlund)

Peter,

I realise I'm being dense here, but I'm not sure if I've followed this.

1 Application prepares a row for INSERT, and gives CLASS some default value.

2 I now want to set CLASS via an AFTER INSERT trigger, so do I get

FOR EACH ROW
REFERENCING NEW AS N
BEGIN ATOMIC
UPDATE TCORD C
SET CLASS = (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID = N.CUSTOMER_ID)
WHERE C.ORDER_ID = N.ORDER_ID
END
(assuming ORDER_ID to be the unique key of TCORD)?

3 Then the idea is an AFTER UPDATE trigger such as this?

WHEN (N.CLASS <> (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID = N.CUSTOMER_ID))
BEGIN ATOMIC
UPDATE TCORD C
SET CLASS = (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID = N.CUSTOMER_ID)
WHERE C.ORDER_ID = N.ORDER_ID
END

I'm having a bit of trouble getting my mind round how I can issue an UPDATE on a row which only exists when the INSERT which is triggering this is complete - in other words, at what point does the row become a row rather than the N row, if you see what I mean?

Thanks,

Mike
----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Peter Backlund
Sent: 14 January 2005 11:30
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

Mike,

maybe (I can't test it at home) you could have a WHEN clause
WHEN (n.CLASS <> (Select CLASS from parent...))
UPDATE Child set ...

Peter

[login to unmask email] wrote:

Peter,

Thanks for that, but now I get a bit more confused. I want to do the same thing for UPDATEs, ie set CLASS to the same as whatever is now in the parent. But if I do that as an after trigger as well, don't I end up endlessly triggering the same trigger?

Mike
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Peter Backlund
Sent: 13 January 2005 12:54
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

DB2 for z/OS V8 will not provide this functionality.

You can use an AFTER trigger which can update CLASS in T_ORDERS
with the SELECTed value. You have to specify the appropriate WHERE
clause on the update statement

Peter

[login to unmask email] wrote:

DB2 for z/OS v7.

The requirement seems fairly simple:

- when inserting or updating a row in table B, column X should get its value from column Y of table A.

If DB2 for z/OS supported a SELECT in the SET statement of a trigger, it would be dead simple:

CREATE TRIGGER ON T_ORDERS REFERENCING NEW AS N
BEGIN ATOMIC
SET N.CLASS = (SELECT CLASS FROM T_CUSTOMER WHERE CUSTOMER_ID = N.CUSTOMER_ID);
END;

But DB2 for z/OS V7 does not support that, so I'm wondering how to go about it. Going to DB2 V8 is a possibility if the requisite functionality is available at that release.

Any workable ideas welcome.

Mike Holmans
Lead DBA
BT Exact In-Life Services
[login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--
********************************************************
* *
**** My new e-mail address is [login to unmask email] ****
* *
********************************************************

====> See you in 2005, October 24-27, at IDUG in Berlin <====

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--
********************************************************
* *
**** My new e-mail address is [login to unmask email] ****
* *
********************************************************

====> See you in 2005, October 24-27, at IDUG in Berlin <====

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Backlund

Re: Using a trigger to get a value

January 14, 2005 07:16 AM (in response to Mike Holmans)

Mike,

I don't know how it works, must be something electronic,
but I tested the following on my DB2 for Linux, UNIX, Windows 8.2
and it worked like a charm!

Peter

drop table mikep;

create table mikep(id int,class int);

insert into mikep values(10,10);

drop table mikec;

create table mikec(id int,class int);

drop trigger mikei;

create trigger mikei
after insert on mikec
referencing new as n
for each row mode db2sql
update mikec set class =
  (select class from mikep
   where id = n.id);

drop tigger mikeu;

create trigger mikeu
after update of class on mikec
referencing new as n
for each row mode db2sql
when (n.class <> (select class from mikep
                  where id = n.id))
update mikec set class =
  (select class from mikep
   where id = n.id);

insert into mikec values(10,50);

select * from mikec;

[login to unmask email] wrote:
Peter, I realise I'm being dense here, but I'm not sure if I've followed this. 1 Application prepares a row for INSERT, and gives CLASS some default value. 2 I now want to set CLASS via an AFTER INSERT trigger, so do I get FOR EACH ROW REFERENCING NEW AS N BEGIN ATOMIC UPDATE TCORD C SET CLASS = (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID = N.CUSTOMER_ID) WHERE C.ORDER_ID = N.ORDER_ID END (assuming ORDER_ID to be the unique key of TCORD)? 3 Then the idea is an AFTER UPDATE trigger such as this? WHEN (N.CLASS <> (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID = N.CUSTOMER_ID)) BEGIN ATOMIC UPDATE TCORD C SET CLASS = (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID = N.CUSTOMER_ID) WHERE C.ORDER_ID = N.ORDER_ID END I'm having a bit of trouble getting my mind round how I can issue an UPDATE on a row which only exists when the INSERT which is triggering this is complete - in other words, at what point does the row become a row rather than the N row, if you see what I mean? Thanks, Mike ----Original Message----- From: DB2 Data Base Discussion List [mailto:[login to unmask email]]On Behalf Of Peter Backlund Sent: 14 January 2005 11:30 To: [login to unmask email] Subject: Re: [DB2-L] Using a trigger to get a value Mike, maybe (I can't test it at home) you could have a WHEN clause WHEN (n.CLASS <> (Select CLASS from parent...)) UPDATE Child set ... Peter [login to unmask email] wrote: Peter, Thanks for that, but now I get a bit more confused. I want to do the same thing for UPDATEs, ie set CLASS to the same as whatever is now in the parent. But if I do that as an after trigger as well, don't I end up endlessly triggering the same trigger? Mike -----Original Message----- From: DB2 Data Base Discussion List [mailto:[login to unmask email]]On Behalf Of Peter Backlund Sent: 13 January 2005 12:54 To: [login to unmask email] Subject: Re: [DB2-L] Using a trigger to get a value DB2 for z/OS V8 will not provide this functionality. You can use an AFTER trigger which can update CLASS in T_ORDERS with the SELECTed value. You have to specify the appropriate WHERE clause on the update statement Peter [login to unmask email] wrote: DB2 for z/OS v7. The requirement seems fairly simple: - when inserting or updating a row in table B, column X should get its value from column Y of table A. If DB2 for z/OS supported a SELECT in the SET statement of a trigger, it would be dead simple: CREATE TRIGGER ON T_ORDERS REFERENCING NEW AS N BEGIN ATOMIC SET N.CLASS = (SELECT CLASS FROM T_CUSTOMER WHERE CUSTOMER_ID = N.CUSTOMER_ID); END; But DB2 for z/OS V7 does not support that, so I'm wondering how to go about it. Going to DB2 V8 is a possibility if the requisite functionality is available at that release. Any workable ideas welcome. Mike Holmans Lead DBA BT Exact In-Life Services [login to unmask email] --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm -- ******************************************************** * * **** My new e-mail address is [login to unmask email] **** * * ******************************************************** ====> See you in 2005, October 24-27, at IDUG in Berlin <==== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm -- ******************************************************** * * **** My new e-mail address is [login to unmask email] **** * * ******************************************************** ====> See you in 2005, October 24-27, at IDUG in Berlin <==== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
-- ******************************************************** * * **** My new e-mail address is [login to unmask email] **** * * ******************************************************** ====> See you in 2005, October 24-27, at IDUG in Berlin <==== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Humphris

Re: Using a trigger to get a value

January 14, 2005 09:22 AM (in response to Peter Backlund)

Hi Mike,

I think the trigger finds the row because, committed or not, the trigger
is running as part of your thread. So when your thread commits (or
rollbacks) the new row and the trigger update will be committed (or
rolled back) as part of your unit of work.

Rich Humphris

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, January 14, 2005 6:50 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

Peter,

I realise I'm being dense here, but I'm not sure if I've followed this.

1 Application prepares a row for INSERT, and gives CLASS some default
value.

2 I now want to set CLASS via an AFTER INSERT trigger, so do I get

FOR EACH ROW
REFERENCING NEW AS N
BEGIN ATOMIC
UPDATE TCORD C
SET CLASS = (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID =
N.CUSTOMER_ID)
WHERE C.ORDER_ID = N.ORDER_ID
END
(assuming ORDER_ID to be the unique key of TCORD)?

3 Then the idea is an AFTER UPDATE trigger such as this?

WHEN (N.CLASS <> (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID =
N.CUSTOMER_ID))
BEGIN ATOMIC
UPDATE TCORD C
SET CLASS = (SELECT CLASS FROM TCUST AS C WHERE C.CUSTOMER_ID =
N.CUSTOMER_ID)
WHERE C.ORDER_ID = N.ORDER_ID
END

I'm having a bit of trouble getting my mind round how I can issue an
UPDATE on a row which only exists when the INSERT which is triggering
this is complete - in other words, at what point does the row become a
row rather than the N row, if you see what I mean?

Thanks,

Mike
----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Peter Backlund
Sent: 14 January 2005 11:30
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

Mike,

maybe (I can't test it at home) you could have a WHEN clause
WHEN (n.CLASS <> (Select CLASS from parent...))
UPDATE Child set ...

Peter

[login to unmask email] wrote:

Peter,

Thanks for that, but now I get a bit more confused. I want to do the
same thing for UPDATEs, ie set CLASS to the same as whatever is now in
the parent. But if I do that as an after trigger as well, don't I end up
endlessly triggering the same trigger?

Mike
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Peter Backlund
Sent: 13 January 2005 12:54
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

DB2 for z/OS V8 will not provide this functionality.

You can use an AFTER trigger which can update CLASS in T_ORDERS
with the SELECTed value. You have to specify the appropriate WHERE
clause on the update statement

Peter

[login to unmask email] wrote:

DB2 for z/OS v7.

The requirement seems fairly simple:

- when inserting or updating a row in table B, column X should get its
value from column Y of table A.

If DB2 for z/OS supported a SELECT in the SET statement of a trigger, it
would be dead simple:

CREATE TRIGGER ON T_ORDERS REFERENCING NEW AS N
BEGIN ATOMIC
SET N.CLASS = (SELECT CLASS FROM T_CUSTOMER WHERE CUSTOMER_ID =
N.CUSTOMER_ID);
END;

But DB2 for z/OS V7 does not support that, so I'm wondering how to go
about it. Going to DB2 V8 is a possibility if the requisite
functionality is available at that release.

Any workable ideas welcome.

Mike Holmans
Lead DBA
BT Exact In-Life Services
[login to unmask email]

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

--
********************************************************
* *
**** My new e-mail address is [login to unmask email] ****
* *
********************************************************

====> See you in 2005, October 24-27, at IDUG in Berlin <====

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

--
********************************************************
* *
**** My new e-mail address is [login to unmask email] ****
* *
********************************************************

====> See you in 2005, October 24-27, at IDUG in Berlin <====

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the
addressee(s) and may contain confidential and/or legally privileged information. If you are not the
intended recipient of this message or if this message has been addressed to you in error, please
immediately alert the sender by reply e-mail and then delete this message and any attachments. If you
are not the intended recipient, you are notified that any use, dissemination, distribution, copying, or
storage of this message or any attachment is strictly prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Holmans

Re: Using a trigger to get a value

January 20, 2005 06:41 AM (in response to Richard Humphris)

Peter,

Thanks very much. I tried it on our DB2 for z/OS V7 system and it too worked like a charm.

I'm a bit antsy about the various stages of trigger this process goes through, but since the triggered UPDATE and its own after trigger count as part of the original UOW, and therefore failures down the line will rollback everything, I'm probably just scaring myself for the hellavit.

Mike
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Peter Backlund
Sent: 14 January 2005 13:16
To: [login to unmask email]
Subject: Re: [DB2-L] Using a trigger to get a value

Mike,

I don't know how it works, must be something electronic,
but I tested the following on my DB2 for Linux, UNIX, Windows 8.2
and it worked like a charm!

Peter

drop table mikep;

create table mikep(id int,class int);

insert into mikep values(10,10);

drop table mikec;

create table mikec(id int,class int);

drop trigger mikei;

create trigger mikei
after insert on mikec
referencing new as n
for each row mode db2sql
update mikec set class =
(select class from mikep
where id = n.id);

drop tigger mikeu;

create trigger mikeu
after update of class on mikec
referencing new as n
for each row mode db2sql
when (n.class <> (select class from mikep
where id = n.id))
update mikec set class =
(select class from mikep
where id = n.id);

insert into mikec values(10,50);

select * from mikec;

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm