db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From pb2208 <pete.bea...@gmail.com>
Subject Help with Triggers
Date Mon, 07 Jun 2010 13:19:47 GMT

I am trying to learn the proper way to implement triggers. I have read
numerous entries on the web that were supposed to work with Derby. I dought
that, since they have different code strings and different logic.

I am looking at two scenarios.
The first scenario invloves using a column trigger to sum two columns (A and
B) and place the result in a thrid column (C) within the same table
(mytable). I have built the following code:

AFTER  UPDATE  INSERT  of  ColumnA, ColumnB
ON  MyTable
UPDATE  MyTable  SET  ColumnC = ColumnA + ColumnB
WHERE  id = key

My questions on this scenario are:
1. Is this a correct trigger for the scenario if described?
2. Is it necessary to include the WHERE clause or will the trigger default
to the current record?
3. Must I use the Update clause or is there a more direct way?

The second scenario involves a column(A) that contains a decimal(7,6) value.
I need to set another column(B) to a decimal(1) value based on where the
value in A falls within in a range of values. For example: if A =11 and
there are 3 ranges (0-10, 11-20, 21-30), B would equal "1".

I do not know of a way to do this with SQL, so I would probaly need to do to
this through a stored Procedure or Function referencing some code in my
application. However, I am totaly unclear what type of TRIGGER would be
required, how the column (B) would be updated. I have seen some references
to a read only set of code that can not update the table. I assume this
means it can not issue any SQL commands. 

Can you provide some guidance?
View this message in context: http://old.nabble.com/Help-with-Triggers-tp28805126p28805126.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

View raw message