trigger fire at not null columns

[narayana]

hi gurus,
here is the problem
i have a table with 2 columns first one is the primary key
with small int next one is integer
then i need a trigger with out using sequence or identity that when i
insert a second column the first column should be fired with and number
next onward it should be incremented

[Rhino]

"narayana" <va***********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

hi gurus,
here is the problem
i have a table with 2 columns first one is the primary key
with small int next one is integer
then i need a trigger with out using sequence or identity that when i
insert a second column the first column should be fired with and number
next onward it should be incremented

I just read your question twice and I don't understand what you are trying
to do. Can you clarify the question?

Also, what operating system are you using and what version of DB2 are you
running?

Rhino

[Serge Rielau]

Rhino wrote:

"narayana" <va***********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

hi gurus,
here is the problem
i have a table with 2 columns first one is the primary key
with small int next one is integer
then i need a trigger with out using sequence or identity that when i
insert a second column the first column should be fired with and number
next onward it should be incremented

I just read your question twice and I don't understand what you are trying
to do. Can you clarify the question?

Also, what operating system are you using and what version of DB2 are you
running?

Rhino

Taking some guesses....

CREATE TABLE T(c1 NOT NULL SMALLINT PRIMARY KEY, c2 INTEGER);
CREATE TRIGGER trg1 NO CASCADE BEFORE INSERT ON T
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
WHEN (n.pk IS NULL)
SET n.pk = (SELECT COALESCE(MAX(pk), 0) + 1 FROM T);

Should work as long as only single rows are inserted.
For multirow need to use the counter function in sqllib/samples/c

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[narayana]

this thing i want in windows 2000 OS DB2 v 8.2

waiting for result

[p175]

Not sure if this is what you want BUT you could try ..

CREATE FUNCTION GET_NEXT_PK_ID()
RETURNS SMALLINT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SELECT DISTINCT CASE
WHEN MAX(COL1) IS NULL THEN 1
WHEN MAX(COL1) = 0 THEN 1
ELSE MAX(COL1)+1
END
FROM TABLE_NAME ;

INSERT INTO TABLE_NAME (COL1, COL2) VALUES (GET_NEXT_PK_ID(), 123);