| View previous topic :: View next topic |
| Author |
Message |
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
 Posted: Thu Mar 02, 2006 1:57 pm Post subject: Unique Key generated from Trigger in DB2 (z/OS) |
 |
|
I need to create a trigger to operate when a row gets updated. This trigger will create 2 rows on a second table - 1 "delete" and 1 "add" . I need to create a unique key to these rows and the 2 rows should be in the sequence that they were added. I have added a timestamp but this is not unique enough so I added a random number. My problem is that I need to use the same number in both rows.
Here is my code
| Code: |
BEGIN ATOMIC
INSERT INTO MY.TABLE
(SELECT
CURRENT TIMESTAMP CONCAT
SUBSTR(CHAR(RAND()),3,3),
'D',
OTBL.some fields
FROM MY.OTHER_TABLE);
INSERT INTO MY.TABLE
(SELECT
CURRENT TIMESTAMP CONCAT
SUBSTR(CHAR(RAND()),3,3),
'I',
NTBL.some fields
FROM MY.OTHER_TABLE);
END #
|
This code generates 2 different random numbers. Does anyone know how to make it the same? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 02, 2006 2:18 pm Post subject: |
|
|
Bithead,
I fully don't understand how adding same random number is going to make it unique? You only added the random number to keep the rows unique. Now if you generate the same random number ,I don't see how you can have it as an unique row.
What am I missing here?
Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Thu Mar 02, 2006 2:21 pm Post subject: |
|
|
Kolusu,
My mistake, I cut some code out to keep it brief - the 'D' and 'I' should be added to the end of the key. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 02, 2006 2:59 pm Post subject: |
|
|
Bithead,
I am not sure if this helps or not , here is something you can try.
| Code: |
INSERT INTO T1 (SELECT CHAR(CURRENT TIMESTAMP) CONCAT
CHAR('D')
FROM SYSIBM.SYSDUMMY1
UNION
SELECT CHAR(CURRENT TIMESTAMP) CONCAT
CHAR('I')
FROM SYSIBM.SYSDUMMY1)
;
|
this will insert 2 rows with
| Code: |
2006-03-02-14.56.33.753146A
2006-03-02-14.56.33.753146I
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Thu Mar 02, 2006 3:02 pm Post subject: |
|
|
Kolusu,
I like the code but I still need to get that random nunber in there. Any ideas? |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
