Create a SQL trigger that calculates a field on insert?

Hi,
I would like to set up an SQL Trigger on an MS sql Database 2008. I'm sorry I'm not very familiar with T-SQL  and I'm in the process of learning. How would I go about setting up a Trigger that Dynamically calculates a Field on insert?

([WATER_LENGTH] * [WATER_WIDTH] /43560) * [WATER_DEPTH]

This is the formula I would like to use to calculate the field [VOLUME_DIMEN]

GravitaZ24Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, first, you could make that field eventually a computed field, so you don't need a trigger.

anyhow:
create trigger trg_volme_dimen
 on yourtable
for insert
as
  update t
      set volume_dimen = ([WATER_LENGTH] * [WATER_WIDTH] /43560) * [WATER_DEPTH] 
    from yourtable t
    join INSERTED i 
      on i.primary_key_field = t.primary_key_field

Open in new window


INSERTED is the virtual table referring to the rows you inserted into the "yourtable", for which the trigger is raised.
the primary_key_field is the name of your primary key field in your table..

so, you just rename the tablename, primary_key_field, and eventually the trigger name as to your needs, and that should be it :)
 
0
 
HainKurtSr. System AnalystCommented:
no need for trigger as angelIII suggested use computed column
or create a view like this

screate view as vMyTable as
select t.*, ([WATER_LENGTH] * [WATER_WIDTH] /43560) * [WATER_DEPTH]  as volume_dimen
from myTable t

and use vMyTable when you need that column...
0
 
lcohanDatabase AnalystCommented:
The best would be to directly insert the calculated value from whatever UI (business layer) code you have into a SQL.table.column. Triggers are hidden SQL code objects and may cause performance issues or other headaches in the future.
Second option would be indeed a computed column as angelIII allready suggetsed.
0
 
GravitaZ24Author Commented:
Thank you, I decided to scrap the trigger idea and use the computed column.

lcohan, the problem with the UI option is I'm using arcGIS and there isn't really an option for that
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.