Submitted by: Attila Molnár (e_pluribus_unum)

Duplicates CORE3282

Hi!

I've got two tables, and one of them has a trigger, which insert or
update data to the other table in case of inserting.

The followong SQL gives error when I connect with WIN1250, but works
fine with UTF8. If I'm not mistaken the client side charset sould not
affect the result.

INSERT INTO sz_felirat_szall (id, nyelv_kod, mezonev, ertek)
VALUES (2065664, '00', 'LEV_SKOMP', 'Szállítói tartozásunk kiegyenlítése')

Error :
"arithmetic exception, numeric overflow, or string truncation.
string right truncation.
At trigger 'INSERTSZ_FELIRAT_SZALL' line: 17, col: 3."

CREATE TABLE SZ_FELIRAT (
NYELV_KOD XVAR2N /* XVAR2N = VARCHAR(2) NOT NULL */,
...
LEV_SKOMP XVAR40 /* XVAR40 = VARCHAR(40) */,
...
);

CREATE TABLE SZ_FELIRAT_SZALL (
ID XIDN /* XIDN = INTEGER NOT NULL */,
NYELV_KOD XVAR2N DEFAULT '00' /* XVAR2N = VARCHAR(2) NOT NULL */,
MEZONEV XVAR30N /* XVAR30N = VARCHAR(30) NOT NULL */,
ERTEK XVAR100N /* XVAR100N = VARCHAR(100) NOT NULL */
);

CREATE OR ALTER TRIGGER insertsz_felirat_szall FOR sz_felirat_szall
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE ins VARCHAR(300);
DECLARE VARIABLE db INTEGER DEFAULT 0;
BEGIN
IF (http://NEW.id IS NULL) THEN http://NEW.id = GEN_ID(xid_gen, 1);
IF (NEW.nyelv_kod IS NULL) THEN NEW.nyelv_kod = '00';
SELECT
COUNT(1)
FROM sz_felirat
WHERE nyelv_kod = NEW.nyelv_kod
INTO :db;
IF (db = 0) THEN INSERT INTO sz_felirat (nyelv_kod)
VALUES (NEW.nyelv_kod);
ins = 'update sz_felirat set ' || NEW.mezonev || '=''' || REPLACE(NEW.ertek, '''', '''''') || ''' where nyelv_kod = ''' || NEW.nyelv_kod || ''' and coalesce(' || NEW.mezonev || ', '''') = ''''';
EXECUTE STATEMENT ins;
END