On May 17, 3:43 am, "gimme_this_gimme_t...@yahoo.com"
<gimme_this_gimme_t...@yahoo.comwrote:
I'm using DB2 8.1.
Suppose table foo has columns name and lname:
create table foo
(name as varchar(200),
lname as varchar(200));
Write a trigger that inserts the lower case value of name after an
insert:
So
insert (name) into foo values ('Hello World");
Then:
select * from foo;
Results in:
"Hello World","hello world"
Thanks
I assume you don't want an "after insert" trigger but a "before
insert" trigger. Here is an example:
CREATE TRIGGER T
NO CASCADE BEFORE INSERT ON foo
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
set lname = lcase(n.name);
[db2inst1@wb-01 ~/nya/trigger]$ db2 "insert into foo (name) values
('Hello World')"
DB20000I The SQL command completed successfully.
[db2inst1@wb-01 ~/nya/trigger]$ db2 "select * from foo"
NAME
LNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hello
World
hello world
In general it is a good idea to have a check constraint that
guarantees this:
[db2inst1@wb-01 ~/nya/trigger]$ db2 "alter table foo add constraint C
check (lname = lcase(name))"
You might also be interested in a generated always variant (no trigger
needed, and the check constraint is generated for you under the hood):
[db2inst1@wb-01 ~/nya/trigger]$ db2 "create table foo2 (name
varchar(200) not null, lname varchar(200) not null generated always as
(lcase(name)))"
DB20000I The SQL command completed successfully.
[db2inst1@wb-01 ~/nya/trigger]$ db2 "insert into foo2 (name) values
('Hello World')"
DB20000I The SQL command completed successfully.
[db2inst1@wb-01 ~/nya/trigger]$ db2 "select * from foo2"
NAME
LNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hello
World
hello
world
1 record(s) selected.
HTH
/Lennart