Thanks for the help: Create Trigger

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