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/22 12:50:40 UTC
derby Trigger to update column
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