If I am not misunderstanding something obvious...
Why not do it the "MaxDB" way?
Create a sequence "allocated" for your identity column such as:
CREATE SEQUENCE "SEQ_SOMEKEY"
INCREMENT BY 1 START WITH 1
MINVALUE 1 MAXVALUE 99999999999999999999999999999999999999
NOCYCLE CACHE 100
Then utilize that sequence in your trigger, like so:
CREATE TRIGGER ITRG_SOMETABLE FOR DBA.SOMETABLE
AFTER INSERT EXECUTE
(
BEGIN
UPDATE DBA.SOMETABLE
SET SOMEKEY = DBA.SEQ_SOMEKEY.NEXTVAL
WHERE SOMEKEY = 0;
END;
)
You won't be maintaining the original trigger code in this case, but you
also need not modify your original tables.
Don Gray
CTO, VigilantMinds Inc.
-----Original Message-----
From: Ruth, Brice [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 4:57 PM
To: [EMAIL PROTECTED]
Subject: Help w/ SQL stored procedure
I'm not having much luck looking through the MaxDB docs on putting
together a simple SQL stored procedure. I was hoping someone might be
able to help me with this simple TRIGGER that I'm trying to pull over
from a DB2 instance:
CREATE TRIGGER WEBTST.F58INSNJDT
BEFORE INSERT ON WEBTST.F58NONJDET
REFERENCING NEW AS NEW_ROW
FOR EACH ROW
MODE DB2ROW
BEGIN
DECLARE NEWID INTEGER ;
DECLARE C1 CURSOR FOR
SELECT MAX ( WEBTST . F58NONJDET . NJITM ) FROM WEBTST .
F58NONJDET ;
OPEN C1 ;
FETCH C1 INTO NEWID ;
IF NEW_ROW . NJITM = 0 THEN
SET NEWID = NEWID + 1 ;
SET NEW_ROW . NJITM = NEWID ;
END IF;
CLOSE C1 ;
END ;
Its purpose is quite simply to create a sequence/identity column, in a
database that doesn't natively support it (DB2). But, in bringing things
over to MaxDB, I want to maintain this as a trigger, so that I don't
need to change the table layouts at all.
Any assistance would be much appreciated!
Respectfully,
Brice Ruth
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
