SQL SErver Trigger not evaluating as Insert or Update properly -

-

i want have 1 trigger handle updates , inserts. of sql actions in trigger both. exception fields i'm using record date , username insert , update. have, updates of fields used track update , insert not firing right. if insert new record, createdby, createdon, lasteditedby, lasteditedon populated, lasteditedon 1 second after createdon (which dont want happen). when update record, lasteditedby & lasteditedon changes (which correct). i'm including full trigger reference:

set ansi_nulls on; go set quoted_identifier on; go -- ================================================================================= -- author:  paul j. scipione -- create date: 2/15/2012 -- update date: 6/5/2012 -- description: concatenate several fields set formatted unitdescription, --              total span & loop footages, set appropriate acctcode, & track --              user inserts -- ================================================================================= if object_id('processcable', 'tr') not null   drop trigger processcable go create trigger processcable on cable after insert, update begin set nocount on;  -- if trigger_nestlevel() > 1 return  if ((select trigger_nestlevel()) > 1 )   return else   begin   -- record user , date of insert or update   if exists (select * deleted)     update cable set lasteditedon = getdate(), lasteditedby = replace(user_name(), 'grtinet\', '')   else if not exists (select * deleted)     update cable set createdon = getdate(), createdby = replace(user_name(), 'grtinet\', '')    -- reset suffix if applicable   update cable set suffix = null suffix = 'n/a'    -- create unitdescription value   update cable set unitdescription =     isnull (type, '') +     isnull (convert (nvarchar (10), size), '') +     '-' +     isnull (convert (nvarchar (10), gauge), '') +     case       when extratrench not null , extratrench > 0         case           when suffix null 'te' + '(' + convert (nvarchar (10), extratrench) + ')'           else 'te' + '(' + convert (nvarchar (10), extratrench) + ')' + suffix         end       else isnull (suffix, '')     end    -- convert accidental negative numbers entered   update cable set length = abs(length)    -- sum length loopfootage totalfootage   update cable set totalfootage = isnull(length, 0) + isnull(loopfootage, 0)    -- set proper acctcode based on type   update cable set acctcode =     case       when type in ('sea', 'cw', 'cj') '32.2421.2'       when type in ('bfc', 'bj', 'seb') '32.2423.2'       when type in ('tip','uf') '32.2422.2'       when type = 'unknown' or type null 'unknown'     end   acctcode null or acctcode = ' '  end  end go

a few things jump out @ me when @ trigger:

  • you doing several additional updates rather single update (performance-wise, single update better).
  • your update statements unconstrained (there no join inserted/deleted tables limit number of records perform these additional updates on).
  • most of logic feels should in application layer rather in database; or, perhaps in cases implemented differently.

some quick examples:

  • suffix of "n/a" should removed before inserted.
  • cable length absolute value should done before inserted (with check constraint verify bad data cannot inserted).
  • totalfootage should computed column correct.
  • the type/acctcode relationship seems should column value in foreign key reference.

but ultimately, think reason seeing unexpected dates because of unconstrained updates. without addressing of other concerns brought above, statement sets audit fields should more this:

update cable set lasteditedon = getdate(), lasteditedby = replace(user_name(), 'grtinet\', '') cable     join deleted on cable.primarykeycolumn = deleted.primarykeycolumn  update cable set createdon = getdate(), createdby = replace(user_name(), 'grtinet\', '') cable     join inserted on cable.primarykeycolumn = inserted.primarykeycolumn     left join deleted on cable.primarykeycolumn = deleted.primarykeycolumn deleted.primarykeycolumn null

Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more