sql - If statement on update statement Instead of trigger? -

i have stored procedure calculate factors merging tables , updates in table 'tablea'. have created trigger on tablea when there update records entered new table 'tablea_new'. problem trigger have 6 months factors updated @ time months factors may not updated should entered in new table.so trigger troubled me lot.

so went if statement insert update records

my code if statement

@action set a.factor=b.net/b.cost,a.net=b.net,a.lastmodified=sysdatetime(),a.lastmodifiedby=suser_name(),a.path=b.path tablea inner join ##temp3 b on a.year=b.year , a.month=b.month , a.media=b.media  if @action='update'    begin      insert tablea_new (id,media,year,month,factor,net,updateddate,updatedby,filepath) select id,media,year,month,factor,net,lastmodified,lastmodifiedby,filepath tablea media='cnn'and year=@year , net >1 end 

can guys give me suggestions there can make modifications or need use triggers :(

you can make use of change data capture (cdc) feature in sql server 2008 r2. automatically store changed rows, irrespective of type of change (i.e. whether insert, update or delete).

to enable cdc database, use below script

use your_database_name go  exec sys.sp_cdc_enable_db 

to enable cdc table, use below script

exec sys.sp_cdc_enable_table  @source_schema = n'your_schema_name', @source_name = n'your_table_name', @role_name = null 

once cdc enabled, new table created under schema 'cdc' store changed rows, along metadata change. may directly access table required data.