473,574 Members | 3,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

same trigger problem

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
Jul 24 '06 #1
2 4960
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'2006072413172 6776837000000'
x'2006072413172 6831227000000'
x'2006072413172 6831297000000'
x'2006072413172 6831308000000'

4 record(s) selected.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 24 '06 #2
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'200607241317 26776837000000'
x'200607241317 26831227000000'
x'200607241317 26831297000000'
x'200607241317 26831308000000'

4 record(s) selected.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200607/1
Jul 24 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
15399
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
9
3451
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8)
0
7131
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined a SQL stored proc, and the trigger is set to call this SP. I've posted the simplified source below. I can...
12
4745
by: Bob Stearns | last post by:
I am trying to create a duplicate prevention trigger: CREATE TRIGGER is3.ard_u_unique BEFORE UPDATE OF act_recov_date ON is3.flushes REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.act_recov_date IS NOT NULL) BEGIN ATOMIC select count(*) into v_n from is3.flushes
13
6558
by: dennis | last post by:
Hello, I'm having trouble solving the following problem with DB2 UDB 8.2. I need to create a trigger that performs certain extra constraint validations (temporal uniqueness). One of the tables has no primary key. I'm having trouble expressing an update trigger that checks for existing rows, contemporary to the updated one, but...
1
6196
by: abhi81 | last post by:
Hello All, I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp. Insert and Update trigger work fine when i have only one of them defined. However when I have all the 3 triggers in place and when i try to fire...
2
2383
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL parent_sample_id int NOT NULL ....lots of other cols...) DELETEs on all tables are handled by the front end code, which just
1
1881
by: degno84 | last post by:
hi.. i have a problem with this simple trigger! I wont to insert a row in the same table where I update a value. The trigger is that: CREATE OR REPLACE TRIGGER LOG_SPOSTAMENTI_NEW_DEST AFTER update of data_fine, luogo_spostamento on LOG_SPOSTAMENTI for each row
22
6592
by: DreamersDelight | last post by:
Hi, I'm stuck on this problem and I can't find a sollution. I'm going to try and explain this step by step. 1 After certain rows get updated with a certain value. I don't know wich rows in advance. And they do not have a certain order, but after the update they all have the same trans_id. (in unload_details_tab). 2 I select select the rows...
0
7843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7764
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8274
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8140
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5660
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3776
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3795
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1102
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.