db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: derby trigger --- after insert
Date Thu, 05 Aug 2010 12:35:34 GMT
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
>
>  
>


Mime
View raw message