473,700 Members | 2,588 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 4970
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
15421
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
3456
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
7143
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 manually call the stored proc, and the external trigger is created without any errors. However, when I do...
12
4757
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
6569
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 exclusing the
1
6208
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 a insert query on the statement. It triggers both insert and update trigger at the same time and...
2
2392
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
1886
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
6633
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 using the value that just got updated (trans_id), this way I know wich rows I needed to select, the...
0
8647
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9217
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9076
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7813
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4404
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4659
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3092
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2392
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2030
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.