You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Mamatha Kodigehalli Venkatesh <Ma...@ness.com> on 2010/06/18 11:53:20 UTC

Trigger to update column of one table based on insert to an another table

 

Hello,

 

Derby version = db-derby-10.5.1.1

 

I need to pick up the value of myTable.size as soon as the record is inserted and need to update the targetable.size column 

Where myTable .myTableID = targetable.MyTableID using a Trigger.

 

Please help me out on this.

 

CREATE TABLE myTable (

myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

Name     VARCHAR (50),

size INTEGER NOT NULL);

 

INSERT INTO myTable (name,size) VALUES ('Jon231232', 9);

INSERT INTO myTable (name,size) VALUES ('Mamatha', 7);

INSERT INTO myTable (name,size) VALUES ('Star', 4);

 

 

CREATE TABLE targetTable (

targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

myTableID INTEGER NULL,

size INTEGER);

 

Thanks

Mamatha

Re: Create Trigger .. Please help

Posted by Morten Mikkelsen <mo...@gmail.com>.
Hi.
I have no clue how to help you, I just wanted to let you know that all
of your messages are well received.
Hope you sort things out.
/Morten

On Wed, Jun 23, 2010 at 6:59 AM, Mamatha Kodigehalli Venkatesh
<Ma...@ness.com> wrote:
> Hello,
>
>
>
> Derby version = db-derby-10.5.1.1
>
>
>
> I need to pick up the value of myTable.size as soon as the record is
> inserted and need to update the targetable.size column
>
> Where myTable .myTableID = targetable.MyTableID using a Trigger.
>
>
>
> Please help me out on this.
>
>
>
> CREATE TABLE myTable (
>
> myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
> INCREMENT BY 1),
>
> Name     VARCHAR (50),
>
> size INTEGER NOT NULL);
>
>
>
> INSERT INTO myTable (name,size) VALUES ('Jon231232', 9);
>
> INSERT INTO myTable (name,size) VALUES ('Mamatha', 7);
>
> INSERT INTO myTable (name,size) VALUES ('Star', 4);
>
>
>
>
>
> CREATE TABLE targetTable (
>
> targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
> INCREMENT BY 1),
>
> myTableID INTEGER NULL,
>
> size INTEGER);
>
>
>
> Thanks
>
> Mamatha



-- 
/Morten

Thanks for the help: Create Trigger

Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
Thanks Rick... It is working.
I may need more help on creating complex triggers.
To begin with this was helpful.


-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@oracle.com] 
Sent: Wednesday, June 23, 2010 6:19 PM
To: Derby Discussion
Subject: Re: Create Trigger .. Please help

Hi Mamatha,

I'm not sure that I understand what the trigger is supposed to do, but 
here is a sample script which may do something close. Along the way I 
rephrased myTable.size as a generated column because your example 
suggested it was being used that way:

create table myTable
(
   myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 
1, INCREMENT BY 1),
   Name     VARCHAR (50) NOT NULL,
   size INTEGER NOT NULL generated always as ( length( name ) )
);
create table targetTable
(
  targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START 
WITH 1, INCREMENT BY 1),
  myTableID INTEGER,
  size INTEGER
);
create trigger copySize
  after insert on myTable
  referencing new as newRow
  for each row
    insert into targetTable( myTableID, size ) values ( 
newRow.myTableID, newRow.size )
;

insert into myTable (name) values ('Jon231232');
insert into myTable (name) values ('Mamatha');
insert into myTable (name) values ('Star');

select * from myTable;
select * from targetTable;

Hope this helps,
-Rick

Mamatha Kodigehalli Venkatesh wrote:
>
> Hello,
>
>  
>
> Derby version = db-derby-10.5.1.1
>
>  
>
> I need to pick up the value of myTable.size as soon as the record is 
> inserted and need to update the targetable.size column
>
> Where myTable .myTableID = targetable.MyTableID using a *Trigger*.
>
>  
>
> Please help me out on this.
>
>  
>
> CREATE TABLE myTable (
>
> myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, 
> INCREMENT BY 1),
>
> Name     VARCHAR (50),
>
> size INTEGER NOT NULL);
>
>  
>
> INSERT INTO myTable (name,size) VALUES ('Jon231232', 9);
>
> INSERT INTO myTable (name,size) VALUES ('Mamatha', 7);
>
> INSERT INTO myTable (name,size) VALUES ('Star', 4);
>
>  
>
>  
>
> CREATE TABLE targetTable (
>
> targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START 
> WITH 1, INCREMENT BY 1),
>
> myTableID INTEGER NULL,
>
> size INTEGER);
>
>  
>
> Thanks
>
> Mamatha
>

Re: Create Trigger .. Please help

Posted by Rick Hillegas <ri...@oracle.com>.
Hi Mamatha,

I'm not sure that I understand what the trigger is supposed to do, but 
here is a sample script which may do something close. Along the way I 
rephrased myTable.size as a generated column because your example 
suggested it was being used that way:

create table myTable
(
   myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 
1, INCREMENT BY 1),
   Name     VARCHAR (50) NOT NULL,
   size INTEGER NOT NULL generated always as ( length( name ) )
);
create table targetTable
(
  targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START 
WITH 1, INCREMENT BY 1),
  myTableID INTEGER,
  size INTEGER
);
create trigger copySize
  after insert on myTable
  referencing new as newRow
  for each row
    insert into targetTable( myTableID, size ) values ( 
newRow.myTableID, newRow.size )
;

insert into myTable (name) values ('Jon231232');
insert into myTable (name) values ('Mamatha');
insert into myTable (name) values ('Star');

select * from myTable;
select * from targetTable;

Hope this helps,
-Rick

Mamatha Kodigehalli Venkatesh wrote:
>
> Hello,
>
>  
>
> Derby version = db-derby-10.5.1.1
>
>  
>
> I need to pick up the value of myTable.size as soon as the record is 
> inserted and need to update the targetable.size column
>
> Where myTable .myTableID = targetable.MyTableID using a *Trigger*.
>
>  
>
> Please help me out on this.
>
>  
>
> CREATE TABLE myTable (
>
> myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, 
> INCREMENT BY 1),
>
> Name     VARCHAR (50),
>
> size INTEGER NOT NULL);
>
>  
>
> INSERT INTO myTable (name,size) VALUES ('Jon231232', 9);
>
> INSERT INTO myTable (name,size) VALUES ('Mamatha', 7);
>
> INSERT INTO myTable (name,size) VALUES ('Star', 4);
>
>  
>
>  
>
> CREATE TABLE targetTable (
>
> targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START 
> WITH 1, INCREMENT BY 1),
>
> myTableID INTEGER NULL,
>
> size INTEGER);
>
>  
>
> Thanks
>
> Mamatha
>

Create Trigger .. Please help

Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
Hello,

 

Derby version = db-derby-10.5.1.1

 

I need to pick up the value of myTable.size as soon as the record is inserted and need to update the targetable.size column 

Where myTable .myTableID = targetable.MyTableID using a Trigger.

 

Please help me out on this.

 

CREATE TABLE myTable (

myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

Name     VARCHAR (50),

size INTEGER NOT NULL);

 

INSERT INTO myTable (name,size) VALUES ('Jon231232', 9);

INSERT INTO myTable (name,size) VALUES ('Mamatha', 7);

INSERT INTO myTable (name,size) VALUES ('Star', 4);

 

 

CREATE TABLE targetTable (

targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

myTableID INTEGER NULL,

size INTEGER);

 

Thanks

Mamatha

Create Trigger .. Please help

Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
Hello,

 

Derby version = db-derby-10.5.1.1

 

I need to pick up the value of myTable.size as soon as the record is inserted and need to update the targetable.size column 

Where myTable .myTableID = targetable.MyTableID using a Trigger.

 

Please help me out on this.

 

CREATE TABLE myTable (

myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

Name     VARCHAR (50),

size INTEGER NOT NULL);

 

INSERT INTO myTable (name,size) VALUES ('Jon231232', 9);

INSERT INTO myTable (name,size) VALUES ('Mamatha', 7);

INSERT INTO myTable (name,size) VALUES ('Star', 4);

 

 

CREATE TABLE targetTable (

targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

myTableID INTEGER NULL,

size INTEGER);

 

Thanks

Mamatha

derby Trigger to update column

Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
Hello,

 

Derby version = db-derby-10.5.1.1

 

I need to pick up the value of myTable.size as soon as the record is inserted and need to update the targetable.size column 

Where myTable .myTableID = targetable.MyTableID using a Trigger.

 

Please help me out on this.

 

CREATE TABLE myTable (

myTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

Name     VARCHAR (50),

size INTEGER NOT NULL);

 

INSERT INTO myTable (name,size) VALUES ('Jon231232', 9);

INSERT INTO myTable (name,size) VALUES ('Mamatha', 7);

INSERT INTO myTable (name,size) VALUES ('Star', 4);

 

 

CREATE TABLE targetTable (

targetTableID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

myTableID INTEGER NULL,

size INTEGER);

 

Thanks

Mamatha