same trigger problem

[mob1012 via DBMonster.com]

Hi All,
I wrote last week about a trigger problem I was having. I want a trigger to
produce a unique id to be used as a primary key for my table. I used the
advice I received, but the trigger is still not working correctly. Here is my
code:

create trigger emp_update_id
BEFORE update on emp_update
REFERENCING NEW AS N for each row
SET unique_id = Generate_unique();

This works, but here's what happens when I try to input data into the table:

------------------------------ Commands Entered ------------------------------

insert into emp_update (empno, text)
values ('000050', 'update entry'), ('000060', 'update entry');
------------------------------------------------------------------------------

insert into emp_update (empno, text) values ('000050', 'update entry'),
('000060', 'update entry')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=2, COLNO=0" is not allowed. SQLSTATE=23502

SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=2, COLNO=0 " is not allowed.

Explanation:

One of the following occurred:

o The update or insert value was NULL, but the object column
was declared as NOT NULL in the table definition.
Consequently:

- NULL values cannot be inserted into that column.

- An update cannot set values in that column to NULL.

- A SET transition-variable statement in a trigger cannot set
values in that column to NULL.

o The update or insert value was DEFAULT, but the object column
was declared as NOT NULL without WITH DEFAULT in the table
definition. Consequently:

- A default value of NULL cannot be inserted into that
column.

- An update cannot set default values of NULL in that column.

- A SET transition-variable statement in a trigger cannot set
default values of NULL in that column.

o The column name list for the INSERT statement omits a column
declared NOT NULL and without WITH DEFAULT in the table
definition.

o The view for the INSERT statement omits a column declared NOT
NULL and without WITH DEFAULT in the base table definition.

If the value for "<name>" is of the form "TBSPACEID=n1,
TABLEID=n2, COLNO=n3", then the column name from the SQL
statement was not available when the error was issued. The values
provided identify the tablespace, table, and column number of the
base table that does not allow NULL value.

Federated system users: this situation can be detected by the
federated server or by the data source. Some data sources do not
provide the appropriate values for "<name>". In these cases the
message token will have the following format: "<data
source>:UNKNOWN", indicating that the actual value for the
specified data source is unknown.

The statement cannot be processed.

Note: Under some circumstances, the token "<name>" may not be
filled in (sqlerrmc field of the SQLCA not filled in).

User Response:

Correct the SQL statement after examining the object table
definition to determine which columns of the table have the NOT
NULL attribute and do not have the WITH DEFAULT attribute.

If the value for "<name>" is of the form "TBSPACEID=n1,
TABLEID=n2, COLNO=n3", you can determine the table name and
column name using the following query:
SELECT C.TABSCHEMA, C.TABNAME,
C.COLNAME
FROM SYSCAT.TABLES AS T,
SYSCAT.COLUMNS AS C
WHERE T.TBSPACEID = n1
AND T.TABLEID = n2
AND C.COLNO = n3
AND C.TABSCHEMA = T.TABSCHEMA
AND C.TABNAME = T.TABNAME
The table and column identified by this query may be the base
table of a view for which the SQL statement failed.

Federated system users: if the reason is unknown, isolate the
problem to the data source failing the request (see the problem
determination guide for procedures to follow to identify the
failing data source) and examine the object definition for that
data source. Remember that the defaults (NULL and NOT NULL) are
not necessarily the same between data sources.

sqlcode : -407

sqlstate : 23502

SQLCODE: -407
Being new at this - I imagine that this means that because it is a primary
key it cannot be left null so the trigger won't work - Can anyone help me to
reword this or have any ideas about what to do in its place? Thanks for the
help.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200607/1

[Serge Rielau]

INSERT trigger, not UPDATE trigger :-)

CREATE TABLE T(pk VARCHAR(13) FOR BIT DATA NOT NULL PRIMARY KEY,
c1 INT);
CREATE TRIGGER TRG BEFORE INSERT ON T
REFERENCING NEW AS N FOR EACH ROW
SET N.pk = GENERATE_UNIQUE();
SELECT pk FROM NEW TABLE(INSERT INTO T(c1) VALUES (1), (2), (3), (4));
PK
-----------------------------
x'20060724131726776837000000'
x'20060724131726831227000000'
x'20060724131726831297000000'
x'20060724131726831308000000'

4 record(s) selected.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[mob1012 via DBMonster.com]

thank you so much
Serge Rielau wrote:

>INSERT trigger, not UPDATE trigger :-)

CREATE TABLE T(pk VARCHAR(13) FOR BIT DATA NOT NULL PRIMARY KEY,
c1 INT);
CREATE TRIGGER TRG BEFORE INSERT ON T
REFERENCING NEW AS N FOR EACH ROW
SET N.pk = GENERATE_UNIQUE();
SELECT pk FROM NEW TABLE(INSERT INTO T(c1) VALUES (1), (2), (3), (4));
PK
-----------------------------
x'20060724131726776837000000'
x'20060724131726831227000000'
x'20060724131726831297000000'
x'20060724131726831308000000'

4 record(s) selected.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200607/1