trigger fire at not null columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

narayana - 29 Sep 2005 23:41 GMT

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

Reply to this Message

Rhino - 30 Sep 2005 00:12 GMT

> hi gurus,
>
[quoted text clipped - 6 lines]
> 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

Reply to this Message

Serge Rielau - 30 Sep 2005 01:20 GMT

>>hi gurus,
>>
[quoted text clipped - 14 lines]
>
> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

narayana - 30 Sep 2005 13:08 GMT

this thing i want in windows 2000 OS DB2 v 8.2

waiting for result

Reply to this Message

p175 - 30 Sep 2005 17:00 GMT

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);

Reply to this Message