Hi Mamatha,
I believe that you also need an INSERT trigger in addition to the UPDATE
trigger. The following should work. I had to make a couple changes to
your script:
o The triggers should insert into tidlggls, not tidlggls1
o I changed the type of tidlggls.create_date to DATE and the type of
tidlggls.time_stamp to TIMESTAMP.
Hope this helps,
Rick
connect 'jdbc:derby:memory:db;create=true';
CREATE TABLE tidlrblt
(
blt_number INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START
WITH 1, INCREMENT BY 1),
blt VARCHAR(4000) NOT NULL,
size INTEGER NOT NULL,
min_max_size INTEGER NOT NULL,
CONSTRAINT blt_pk PRIMARY KEY (blt_number)
);
CREATE TABLE tidlggls
(
blt_number INTEGER DEFAULT 0 NOT NULL,
min_max_size INTEGER DEFAULT 0 NOT NULL,
create_date date,
glossary_status CHAR (2) NOT NULL,
application_ver CHAR (8) NOT NULL,
time_stamp timestamp
);
CREATE TRIGGER gls_blt_trg
AFTER UPDATE ON tidlrblt
REFERENCING OLD AS UPDATEDROW
FOR EACH ROW
INSERT INTO tidlggls(blt_number,create_date, glossary_status,
time_stamp,min_max_size,application_ver )
VALUES (UPDATEDROW.blt_number, CURRENT_DATE, '00' , CURRENT_TIMESTAMP,
UPDATEDROW.min_max_size,'7.0.1');
CREATE TRIGGER gls_blt_trg_inst
AFTER INSERT ON tidlrblt
REFERENCING NEW AS INSERTEDROW
FOR EACH ROW
INSERT INTO tidlggls(blt_number,create_date, glossary_status,
time_stamp,min_max_size,application_ver )
VALUES (INSERTEDROW.blt_number, CURRENT_DATE, '00' , CURRENT_TIMESTAMP,
INSERTEDROW.min_max_size,'7.0.1');
insert into tidlrblt( blt, size, min_max_size ) values ( 'foo', 3, 3 );
select * from tidlggls;
Mamatha Kodigehalli Venkatesh wrote:
Hello ,
Here are my 2 tables tidlrblt and tidlggls.
I want to insert an record into tidlggls table as soon as an record is
inserted into tidlrblt table using a trigger.
But currently the trigger gls_blt_trg is able to insert into tidlggls
table only when an existing record is updated in tidlrblt table.
CREATE TABLE tidlrblt
(
blt_number INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START
WITH 1, INCREMENT BY 1),
blt VARCHAR(4000) NOT NULL,
size INTEGER NOT NULL,
min_max_size INTEGER NOT NULL,
CONSTRAINT blt_pk PRIMARY KEY (blt_number)
);
CREATE TABLE tidlggls
(
blt_number INTEGER DEFAULT 0 NOT NULL,
min_max_size INTEGER DEFAULT 0 NOT NULL,
create_date CHAR (8) NOT NULL,
glossary_status CHAR (2) NOT NULL,
application_ver CHAR (8) NOT NULL,
time_stamp CHAR (26) NOT NULL
);
CREATE TRIGGER gls_blt_trg
AFTER UPDATE ON tidlrblt
REFERENCING OLD AS UPDATEDROW
FOR EACH ROW
INSERT INTO tidlggls1(blt_number,create_date, glossary_status,
time_stamp,min_max_size,application_ver )
VALUES (UPDATEDROW.blt_number, CURRENT_DATE, '00' , CURRENT_TIMESTAMP,
UPDATEDROW.min_max_size,'7.0.1');
Thanks
Mamatha
