sql - How to set that one record is marked 1 and all other 0 -


i have table addresses columns id (pk), empid (int), address (nvarchar(100), isdefault (bit). have ensure 1 record same empid have isdefault set 1. have done following triggers. first 1 insert trigger. first it's checks if there entered value 1 in isdefault. if yes, checks if there more 1 record same empid , isdefault value set on 1. if true, sets other isdefault values same empid 0. :

create trigger [dbo].[trg_dbo_addresses_isdefault_onlyonerecord_insert]    on [dbo].[laddressesorganisations]     after insert   begin      set nocount on;      begin try          if exists (             select *             inserted             i.isdefault = 1)         begin              if (                 select count(*)                 dbo.addresses lao                 inner join inserted on i.id=lao.id                 lao.isdefault = 1                     , lao.empid = i.empid                 ) > 1              begin                  update lao                 set lao.isdefault = 0                 dbo.addresses lao                 (                     select row_number () on (partition empid order id desc) rn                     dbo.addresses lao                 ) > 1             end         end     end try      begin catch          if @@trancount > 0             rollback tran;      end catch  end

this other 1 update trigger don't have clue how write it. first same insert trigger, checking inserted value 1 value 0. if yes, if there more 1 record same empid , isdefault set 1. if yes,

how write other records set 0 same empid, while 1 being updated remain 1?

create trigger [dbo].[trg_dbo_addresses_isdefault_onlyonerecord_update]    on [dbo].[addresses]     after update   begin      set nocount on;      begin try          if exists (             select *             inserted             inner join deleted d on d.id=i.id             i.isdefault = 1                 , d.isdefault = 0)         begin             if (                 select count(*)                 dbo.addresses lao                 inner join inserted on i.id=lao.id                 lao.isdefault = 1                     , lao.empid = i.empid                 ) > 1              begin                 update lao                 set lao.isdefault = 0                 dbo.addresses lao                 inner join inserted on i.id=lao.id                 (i don't have idea put here)                     , lao.organisationid = i.organisationid             end         end     end try      begin catch          if @@trancount > 0             rollback tran;      end catch  end

i'm working on ms sql 2016.

i agree matt: if possible, avoid trigger.

anyway, think in update trigger should change to:

         if (             select count(*)             dbo.addresses lao             inner join inserted on lao.empid = i.empid             lao.isdefault = 1                                  ) > 1          begin             update lao             set lao.isdefault = 0             dbo.addresses lao             inner join inserted on lao.empid = i.empid              lao.isdefault = 1                 , lao.id <> i.id         end

morevore, can rewrite to:

if exists(select 1            dbo.addresses lao            inner join inserted on lao.empid = i.empid           lao.isdefault = 1   , lao.id <>i.id)                      begin                 update lao                 set lao.isdefault = 0                 dbo.addresses lao                 inner join inserted on lao.empid = i.empid                 lao.isdefault = 1                     , lao.id <> i.id           end

you should change insert trigger too.

updated: insert trigger. far can see (but can't tests, please complete case tests), , if id max value or if want preserve default of last id inserted, think rewrite insert trigger following: (you remove if too, if don't care doing update 0 rows too)

as  begin         set nocount on;         begin try        if exists(select 1             dbo.addresses lao            inner join inserted on lao.empid = i.empid           lao.isdefault = 1                  , lao.id <>i.id                 , i.isdefault=1)                      begin                 update lao                 set lao.isdefault = 0                 dbo.addresses lao                 inner join inserted on lao.empid = i.empid                 lao.isdefault = 1                     , lao.id <> i.id                     , i.isdefault=1           end end try begin catch     if @@trancount > 0         rollback tran; end catch

this change (and i.isdefault=1) applied update trigger too.


Comments

Post a Comment

Popular posts from this blog

angular - Ionic slides - dynamically add slides before and after -

hi i'm using ngfor create set of 3 slides while starting in middle i'm guaranteed able slide left or right on start. when slide right can simple listen reachedend , push slide array i'm looping. but have problem adding slide beginning. if same above , use e.g. array.unshift() or spread add item beginning, view think it's on position 0 , snaps view new slide. the code below work animates slide change index 1. slide = [0,1,2] //example loop slidechanged(event) { if(this.slides.isbeginning()){ this.slide = [this.slide[0]-1, ...this.slide]; this.slides.update(); this.slides.slideto(1) } } <ion-slides [initialslide]="1" (ionslidedidchange)="slidechanged($event)"> <ion-slide *ngfor="let item of slide"> <h1>slide {{item}}</h1> </ion-slide> </ion-slides> any appreciated! you can using ionslidenextend , ionslideprevend events slides . please...
Read more

Add a dynamic header in angular 2 http provider -

i have http interceptor built via tutorial https://scotch.io/@kashyapmukkamala/using-http-interceptor-with-angular2 the problem need have dynamic header changes automatically once changes 'mode' in app. service able tell interceptor change header, or have interceptor pull header (which string)from service each request. able throwing away patch method, call http.patch('new header string') set header string. call in service controls 'mode' setting. wondering if there way interceptor pull recent value service each request or if can add method http provider via interceptor? i've tried call service in interceptor each request returns undefined. because creating new instance of service or copying value before service fired , loaded/changed data. i don't want pass header string every http provider call because defeats purpose of having interceptor, id able set once changes or have provider fetch service before makes request. tl;dr : need add autom...
Read more

minify - Minimizing css files -

i want know how css files can minimized. currently, using external sites css minifier compress css files. however, after removing whitespaces, comments, etc, file sizes reduced 10-15%. how frameworks bootstrap manage make minimized versions kilobytes. as example, css is: body { background-color: #ff0; } the reduction goes 28 bytes 22 bytes other css files have lot more characters still smaller this. actually minifying code means it's remove thing code white space characters new line characters comments block delimiters it reduces amount of code has transferred on web , speed site load fastly.but unreadable format.
Read more