Seeking simple after insert example

[gimme_this...@yahoo.com]

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

[Lennart]

On May 17, 3:43 am, "gimme_this_gimme_t...@yahoo.com"

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

[Dave Hughes]

gimme_this...@yahoo.com wrote:

Any particular reason you don't want to use a generated column?

CREATE TABLE FOO (
NAME VARCHAR(200),
LNAME VARCHAR(200) GENERATED ALWAYS AS (LOWER(NAME))
);

Cheers,

Dave.