Create a SQL trigger that calculates a field on insert?

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?


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

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.

create trigger trg_volme_dimen
 on yourtable
for insert
  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 :)
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...
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.
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
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.