derby trigger --- after insert

[Mamatha Kodigehalli Venkatesh <Ma...@ness.com>]

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

 

Re: derby trigger --- after insert

[Rick Hillegas <ri...@oracle.com>]

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
>
>  
>