c# - Any other optimal way to update the parent table's columns when a child table is updated? -

i have few table structure below:

create table person (     personid int primary key,     name nvarchar(255),     lastupdatedby int,     lastupdateddate datetime );  create table info (     infoid int primary key,     personid int,     info nvarchar(255),     lastupdatedby int,     lastupdateddate datetime );  create table setting (     settingid int primary key,     personid int,     setting nvarchar(255),     lastupdatedby int,     lastupdateddate datetime ); 

i face new procedure follow if there updates on info or setting table, need relevant updates person table on columns lastupdatedby , lastupdateddate.

what first come mind create sql trigger automatically update person table when info or setting table does. take quick glance through few articles stating sql trigger should avoided it's expensive process when creating it,

while people recommends change in application code. example,

using (var db = new dbcontext()) {     var result = db.info.singleordefault(x => x.infoid == infoid);     if (result != null)     {         result.info = "some new value";         result.lastupdatedby = userid;         result.lastupdateddate = datetime.utcnow;         db.savechanges();     } } 

need change , become this.

using (var db = new dbcontext()) {     var result = db.info.singleordefault(x => x.infoid == infoid);     if (result != null)     {         result.info = "some new value";         result.lastupdatedby = userid;         result.lastupdateddate = datetime.utcnow;          var person = db.person.singleordefault(x => x.personid == result.personid);         if (person != null)         {             person.lastupdatedby = result.lastupdatedby;             person.lastupdateddate = result.lastupdateddate;         }         db.savechanges();     } } 

in reality, application code massive, lot of code modification need made.

assume there 30+ tables, , each of them contain @ least 100k of records. if creating of triggers possible, following:

create trigger triggername on dbo.info     after insert, update     begin          set nocount on;         update  dbo.person         set     lastupdatedby = inserted.lastupdatedby ,                 lastupdateddate = inserted.lastupdateddate            inserted           dbo.person.personid = inserted.personid     end  go  

is sql trigger should avoided in scenario? please explain based on answer if can. alternative solution welcome, performance first.

trigger optimal (from performance perspective) here; it's running update statement on bunch of rows front end code. don't see why think there performance penalty. trigger code should more though:

create trigger triggername on dbo.info after insert, update begin      set nocount on;     update  dbo.person     set     lastupdatedby = inserted.lastupdatedby ,             lastupdateddate = inserted.lastupdateddate        dbo.person              inner join              inserted             on dbo.person.personid = inserted.personid end  go  

there other ways, such making stored procedure updates tables in transaction, or updating front end data access layer (if front end has lot update, implies structured wrong: 1 place should have responsibility writing table. if front end code has update statements peppered through it, well.. that's bad design) dedicated class maintains these 2 tables properly..

right i'd trigger easiest way out of problem.. aren't liked, though not because of performance, because start add confusing consequences.. imagine c# developer limited database experience, didn't know trigger was, , you're complaining "every time update 1 table, these other 27 tables change magic! what's going on? going crazy or what?" - triggers break rules "keep data updating code in 1 place" , why people engineer systems specific parts have specific jobs, don't them

wiki