Über i Access SQL-Scrips ausführen:
update V820QPRMF.CMF
set CFID = 'CF'
where CFPROD = 'ZPA04A01AAA105'
;
Ergebnis: SQL-Status: 09000 Anbietercode: -723 Nachricht: [SQL0723] SQL-Auslöser DECMF0002U in V820QPRMF ist mit SQLCODE -330 SQLSTATE 22021 fehlgeschlagen. Ursache . . . . : In einer ausgelösten SQL-Anweisung in Auslöser DECMF0002U in Schema V820QPRMF ist ein Fehler aufgetreten. Der SQLCODE ist -330, der SQLSTATE ist 22021 und die Nachricht ist Zeichenumsetzung kann nicht ausgeführt werden.. Fehlerbeseitigung: Das Jobprotokoll enthält weitere Informationen über den erkannten Fehler. Die Fehler berichtigen und die Anforderung wiederholen. Fehlgeschlagene Anweisungen: 1

Mit STRSQL in der 5250 EMU
update V820QPRMF.CMF
set CFID = 'CF'
where CFPROD = 'ZPA04A01AAA105'
Ergebnis: 72 Zeilen in CMF in V820QPRMF aktualisiert.

Definition:
Die Tabelle CMF ist eine mit DDS erstellte Standardtabelle, von der Ich den Quellcode nicht habe, aber die Erstellung des SQL-Codes bringt folgendes:
CREATE TABLE V820EPRMF.CMF(CFID CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
CFPROD CHAR(35) CCSID 937 NOT NULL DEFAULT '' ,
CFFAC CHAR(3) CCSID 37 NOT NULL DEFAULT '' ,
CFCSET DECIMAL(2, 0) NOT NULL DEFAULT 0 ,
CFCBKT DECIMAL(3, 0) NOT NULL DEFAULT 0 ,
CFTLVL DECIMAL(15, 5) NOT NULL DEFAULT 0 ,
CFPLVL DECIMAL(15, 5) NOT NULL DEFAULT 0 )

RCDFMT IPC100MF ;
-- SQL150D 10 EDTCDE in Spalte CFPLVL ignoriert.
LABEL ON TABLE V820EPRMF.CMF
IS 'Cost Master File' ;

LABEL ON COLUMN V820EPRMF.CMF
( CFID IS 'Rec Id' ,
CFPROD IS 'Item Number' ,
CFFAC IS 'Fac' ,
CFCSET IS 'Cost Set' ,
CFCBKT IS 'Dist Flg' ,
CFTLVL IS 'Std Cst/ Unt-Glbl' ,
CFPLVL IS 'Std Cst/ Unt-Glbl' ) ;

LABEL ON COLUMN V820EPRMF.CMF
( CFID TEXT IS 'Record ID; CF/CZ' ,
CFPROD TEXT IS 'Item Number' ,
CFFAC TEXT IS 'Facility' ,
CFCSET TEXT IS 'Cost Set' ,
CFCBKT TEXT IS 'Cost Bucket' ,
CFTLVL TEXT IS 'Cost This Level' ,
CFPLVL TEXT IS 'Cost Previous Level' ) ;

GRANT DELETE , INSERT , SELECT , UPDATE
ON V820EPRMF.CMF TO PUBLIC ;

GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE
ON V820EPRMF.CMF TO SSA WITH GRANT OPTION ;


Hat jemand eine Idee wie ich den Trigger bauen kann, damit er in beiden Fällen funktioniert?

Definitionen in SQL:
create table history_template for system name BBHST0000T(action_timestamp timestamp not null,
action_name varchar(6) not null,
system_user_id varchar(10) not null,
client_applname varchar(255) not null,
client_programid varchar(255) not null,
client_wrkstnname varchar(255) not null);

label on table history_template is 'Template For History';

create table cost_master_id_0 for system name DECMF0000T(cost_master_ID for column PO_ID bigint generated by default as identity primary key,
item_number for column CFPROD char(35) CCSID 937 not null default '',
facility for column CFFAC char(3) CCSID 37 not null default '',
cost_set for column CFCSET decimal(2, 0) not null default 0,
cost_bucket for column CFCBKT decimal(3, 0) not null default 0)
rcdfmt DECMF0000R;

label on table cost_master_id_0 is 'cost_master_id_0';

grant delete, insert, select, update on cost_master_id_0 to public;
grant delete, insert, select, update, alter, index, references on cost_master_id_0 to QPGMR with grant option;

create table cost_master_history_0 for system name DECMF0000H(cost_master_history_ID for column CFHID bigint generated by default as identity primary key,
like history_template,
cost_master_ID for column CF_ID bigint, -- from cost_master_id_0
like CMF);

label on table cost_master_history_0 is 'Table: cost_master_history_0';

insert into cost_master_id_0(item_number,
facility,
cost_set,
cost_bucket)
select CFPROD,
CFFAC,
CFCSET,
CFCBKT
from CMF;

insert into cost_master_history_0 (action_timestamp,
action_name,
system_user_id,
client_applname,
client_programid,
client_wrkstnname,
cost_master_ID,
CFID,
CFPROD,
CFFAC,
CFCSET,
CFCBKT,
CFTLVL,
CFPLVL)
select current_timestamp,
'init',
user,
current client_applname,
current client_programid,
current client_wrkstnname,
(select cost_master_ID from cost_master_id_0 where item_number = CMF.CFPROD and facility = CMF.CFFAC and cost_set = CMF.CFCSET and cost_bucket = CMF.CFCBKT),
CMF.CFID,
CMF.CFPROD,
CMF.CFFAC,
CMF.CFCSET,
CMF.CFCBKT,
CMF.CFTLVL,
CMF.CFPLVL
from CMF as CMF;

create view cost_master for system name DECMF0001V as (select CMI.cost_master_ID,
CMF.*
from cost_master_id_0 as CMI
left join CMF as CMF
on CMI.item_number = CMF.CFPROD
and CMI.facility = CMF.CFFAC
and CMI.cost_set = CMF.CFCSET
and CMI.cost_bucket = CMF.CFCBKT);
label on table cost_master is 'View: cost_master';

create trigger DECMF0000U after insert on CMF referencing new as nnn for each row mode db2sql
insert into cost_master_id_0 values(default,
nnn.CFPROD,
nnn.CFFAC,
nnn.CFCSET,
nnn.CFCBKT);
label on trigger DECMF0000U is 'TRIGGER: cost_master_id_0 insert';

create trigger DECMF0001U after insert on CMF referencing new as nnn for each row mode db2sql
insert into cost_master_history_0 values(default,
current_timestamp,
'update',
user,
current client_applname,
current client_programid,
current client_wrkstnname,
(select cost_master_ID from cost_master_id_0 where item_number = nnn.CFPROD and facility = nnn.CFFAC and cost_set = nnn.CFCSET and cost_bucket = nnn.CFCBKT),
nnn.CFID,
nnn.CFPROD,
nnn.CFFAC,
nnn.CFCSET,
nnn.CFCBKT,
nnn.CFTLVL,
nnn.CFPLVL);
label on trigger DECMF0001U is 'TRIGGER: CMF insert';

create trigger DECMF0002U after update on CMF referencing new as nnn old as ooo for each row mode db2sql
insert into cost_master_history_0 values(default,
current_timestamp,
'update',
user,
current client_applname,
current client_programid,
current client_wrkstnname,
(select cost_master_ID from cost_master_id_0 where item_number = nnn.CFPROD and facility = nnn.CFFAC and cost_set = nnn.CFCSET and cost_bucket = nnn.CFCBKT),
nnn.CFID,
nnn.CFPROD,
nnn.CFFAC,
nnn.CFCSET,
nnn.CFCBKT,
nnn.CFTLVL,
nnn.CFPLVL);
label on trigger DECMF0002U is 'TRIGGER: CMF update';

create trigger DECMF0003U after delete on CMF referencing old as ooo for each row mode db2sql
insert into cost_master_history_0 values(default,
current_timestamp,
'delete',
user,
current client_applname,
current client_programid,
current client_wrkstnname,
(select cost_master_ID from cost_master_id_0 where item_number = ooo.CFPROD and facility = ooo.CFFAC and cost_set = ooo.CFCSET and cost_bucket = ooo.CFCBKT),
ooo.CFID,
ooo.CFPROD,
ooo.CFFAC,
ooo.CFCSET,
ooo.CFCBKT,
ooo.CFTLVL,
ooo.CFPLVL);
label on trigger DECMF0003U is 'TRIGGER: CMF delete';

create trigger DECMF0004U after delete on CMF referencing old as ooo for each row mode db2sql
delete cost_master_id_0 where item_number = ooo.CFPROD and facility = ooo.CFFAC and cost_set = ooo.CFCSET and cost_bucket = ooo.CFCBKT;
label on trigger DECMF0004U is 'TRIGGER: CMF delete';