Trigger that calculates

[dabdo555]

I have a database that consists of two tables, table A and table B. These two tables are joined in a view.

There are a couple fields in table A that I need to calculate values for everytime an entry is updated. The reason im using a trigger is b/c these calculated fields get there values from a formula that consists of fields from both tables.

Not only that but I need to decipher what kind of formula to use by querying the database and finding out if 1 of 3 conditions is true. If so, use that specific formula. Below is some pysdo code...

update trigger
if condition1 then
x = b+c+d+e/r
y = x+2
endif
if condition2 then
x = m+c+d+e/r
y = x+4
endif
if condition3 then
x = g+c+d+e/r
y = x+5
endif

 

[eramgarden]

what do u mean by x=b+c+d+e/r....are those columns in a table, are u updating x??

 

[dabdo555]

those represent fields in the tables. Im sorry i wasnt very thorough

 

[eramgarden]

I think u need to read about triggers, look at "deleted" and "inserted" tables in triggers because you're requirements arent clear..

for example, you can something like this:

Update <tablename> set x=b+c+d+e/r
where condition1

...and so on but then u need to use the deleted table/inserted table to get your values...

 

[dabdo555]

will that update statement only update the entry thats being changed or will it go through the whole table and apply it to all the entries? Im guessing it will just manipulate the one entry

 

[eramgarden]

If you use &quot;inserted&quot;/&quot;deleted&quot; tables, then whatever entry is changed, is updated.

For example, if your table is like this:

ID x b c d e r
11 2 3 4 5 6 7

and in the trigger, u do this:

update <tablename> set x=x=b+c+d+e/r
from inserted i , <tablename>
where i.id=<tablename>.id -- makes sure u update the row that was actually updated.

Inserted table is the virtual table that holds the values what were change..the current row. Deleted table is the virtual table that hold the old data.

Go to

http://www.sqlmag.com

, they have lots of examples and tips on triggers.