I am getting a error :
A database connection does not exist.
when I try to run my new trigger.

DB2 personal edition

trigger code;

create trigger newtrig
after insert on on_hand
for each row
begin
update on_hand set qty = qty + 1 ;
end;

Any idea's / thanks
BJ

Are you trying to create the trigger? You must connect to the datbase before you can create any objects in the database.

I am already connected and using a database.
Do I have to reconnect just before running
a trigger? and if so how ?

PS- I am running the trigger in sql script I hope that right!
Thanks very much
BJ

Since the trigger has several ";" embedded in the code, you must a different terminator than the default when you run the CLI to create the trigger. Put a "@" in place of the ";" after the last "end" statement and run the script with the following command:

db2 -td@ -vf script-name

Try the following:

drop trigger newtrig@
create trigger newtrig
after insert on on_hand
for each row
mode db2sql
update on_hand set qty = qty + 1 @

Not too sure about the logic you are trying to implement, but the above syntax is ok.

Graham Martin
http://www.ibm.com/software/data/db2/migration/

Instead of a script I went to: control center - tree
triggers - actions - create trigger.
Its much easier for a new user like me, it works fine but now
when I try to run a trigger that updates my on_hand table
using ADJUST table for the transaction data.
I get an error .
Here is my trigger:

update on_hand qty = qty - ( select qty from adjust where adjust.part = on_hand.part);
end

Thanks
BJ

update on_hand SET qty......

I have listed my trigger code below :


begin
update on_hand set qty = qty - ( adjust.qty where adjust.part = on.hand.part);
end

table structures;
table - ADJUST
id integer
part char
qty integer




table - ON_HAND
id integer
part char
qty integer

Thanks
BJ

Part in red is wrong. There got to be a SELECT and a FROM clause. Also in your earlier post you have a WHERE clause where you apears to be trying to join two tables but only listing one.

It would also help if you have listed the error message that you are receiving

update on_hand set on_hand.qty = on_hand.qty - ( Select a.qty from adjust a, on_hand o where a.id = o.id and a.part = o.part);

This is based on your table structure. Part in red I have added since it apears that ID column is a key. Modefy the where cluase based on the key in your table to avoid the duplicates.

table structures;
table - ADJUST
id integer
part char
qty integer

table - ON_HAND
id integer
part char
qty integer

Your posted code does work however it updates
every row not just the matching row ( id & part) .
In the trigger wizard I selected
(for each row) should it be( for each statement)?

By the way you were right the 'id' field is a PrimaryKey
in both tables.

extra details : I am trying to use ADJUST table as a inventory adjustment table subtracting from on_hand
to further explain my objective.

Thanks very much
BJ

You need to figure out how Cursor works. I do not do those.

I am sure you are right . I have not Tackled CURSORS
yet but may have to in order to accomplish my task in
this case.

However keep in mind that in my trigger the table ADJUST
never has more than 1 row of data in it and in fact gets
deleted by the trigger right after all updates are performed.

example:
table - ADJUST

id part qty
1 a1000 12

table on_hand

id part qty
1 a1000 100
( after trigger runs)

table on_hand
id part qty
1 a1000 88


any body have any ideas ? Thanks
BJ

I can't figure out what you have in mind writing such trigger:

create trigger newtrig
after insert on [on_hand]
for each row
begin
-- Do you wish update the same table?
update [on_hand] set qty = qty + 1 ; -- Do you wish update all the table?
end; -- May be you need change delimiter?

what I want to do is update ( adjust) the on_hand table
using the ADJUST table the ADJUST table is nothing more
than a transaction table .
example :
if qty on hand for part a1000 is 100 and I want to write
off 15 I would go to ADJUST table and enter :
part a1000 qty 15 and the trigger would process
the transaction for me.
I am sure one could program a front end form to do this
but I am not there yet.At this point a trigger seems to me
is the fast way to do this task.

I am open to any ideas / Thanks
BJ