By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 930 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

trigger fire at not null columns

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
this thing i want in windows 2000 OS DB2 v 8.2

waiting for result

Nov 12 '05 #4

P: n/a
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);

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.