asked on

Trigger on DB2

DB2 on V5R4M0.

I have a table1.
with 2 fields:
NAME char(20), RIGHTNAME char(20).
I'd like that after I insert/update the Key Field NAME,
it places in RIGHTNAME the trim(name) with
a quantity of space (or other character like '=') in order to have the trim(name) alligned
on the right.
So, if I insert/update the name ARNOLD in NAME,
I will have in RIGHTNAME: ' ARNOLD'

How to create a trigger which does it?

momi_sabag

try something like

CREATE TRIGGER set_name
AFTER INSERT, UPDATE ON table1
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE table1 SET rightname REPEAT(' ', 20-length(trim(new_row.name)) || new_row.name
where name = new_row.name;
END

bobdylan75

ASKER

There is something diffent from your and mine syntax..
It says that after INSERT there are a wrong character (,)
and if I delete ", update" he says che after rightname there will be '='
and thata REPEAT in unknown..
I have V5R4M0.

bobdylan75

ASKER

and that REPEAT is unexpected..

momi_sabag

maybe it does not support this syntax and you will have to create two triggers, one after insert and one after update

try this

CREATE TRIGGER set_name_after_insert
AFTER INSERT ON table1
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE table1 SET rightname = REPEAT(' ', 20-length(trim(new_row.name)) || new_row.name
where name = new_row.name;
END

bobdylan75

ASKER

yES BUT NOW i HAVE ONLY ON SYNTAX ERROR:
It says that || is not valid.
and if I use + hi give my a runtime error on inserting.

bobdylan75

ASKER

sorry: only one syntax error

momi_sabag

sorry, left out a )

try

CREATE TRIGGER set_name_after_insert
AFTER INSERT ON table1
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE table1 SET rightname = REPEAT(' ', 20-length(trim(new_row.name))) || trim(new_row.name)
where name = new_row.name;
END

bobdylan75

ASKER

OK IT WORKS IN THIS WAY:
CREATE TRIGGER SETNAME
AFTER INSERT ON TABLE1
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE TABLE1 T1 SET T1.RIGHTNAME = CONCAT ( REPEAT('X',
20-length(trim(new_row.NAME)) ) , TRIM(NEW_ROW.NAME) )
where T1.NAME = new_row.NAME;
END

bobdylan75

ASKER

But Momi,
if now I want to create the update trigger one?

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bobdylan75

ASKER

thank you very much!!!