simple trigger

• paul.starr

  SSCrazy

  Points: 2505

  More actions

  February 19, 2009 at 2:22 pm

  #947160

  can you give examples of :

  3. Get the last assigned customer prefix

  4. Insert (as a set) the new customers, along with the correct customer prefix based on #2, #3 and a row_number() offset.

  5. If you still need to, update the "entity sequence" with the number of new customers plus #2.

• Bruce W Cassidy

  SSChampion

  Points: 12668

  More actions

  February 19, 2009 at 2:52 pm

  #947176

  paul.starr (2/19/2009)

  ---

  can you give examples of :

  3. Get the last assigned customer prefix

  4. Insert (as a set) the new customers, along with the correct customer prefix based on #2, #3 and a row_number() offset.

  5. If you still need to, update the "entity sequence" with the number of new customers plus #2.

  [font="Verdana"]

  I can give you some code as an example, sure.

  if object_id('dbo.Customer') is not null

  drop table dbo.Customer;

  create table dbo.Customer(

  CustomerID int identity not null primary key,

  CustomerPrefix varchar(3) not null unique,

  CustomerName varchar(100) not null unique

  );

  insert into dbo.Customer(

  CustomerPrefix,

  CustomerName

  )

  select 'AAA', 'A as in Aardvark' union all

  select 'AAB', 'AB, CD goldfish';

  go

  if object_id('dbo.EntitySequence') is not null

  drop table dbo.EntitySequence;

  create table dbo.EntitySequence(

  EntitySequenceID int identity not null primary key,

  EntityName nvarchar(128) not null unique,

  CurrentSequence int not null

  );

  insert into dbo.EntitySequence(

  EntityName,

  CurrentSequence

  )

  select 'dbo.Customer', 2;

  go

  --

  -- creating a table variable here just to hold some examples of

  -- new customers. You can replace this with whatever code you

  -- actually use to get your new customers

  --

  declare @newCustomerExample table(

  CustomerName varchar(100) not null primary key

  );

  insert into @newCustomerExample(

  CustomerName

  )

  select 'MNO Golfish' union all

  select 'CDBD Eyes';

  declare @newCustomerRows int,

  @lastCustomerPrefix varchar(3),

  @customerPrefixBase int,

  @aValue int,

  @letters int;

  set @aValue = ascii('A');

  set @letters = ascii('Z') - @aValue;

  --

  -- create a transaction to ensure nothing else comes along

  -- and makes changes to customers while we are doing this

  begin transaction BulkCustomers

  --

  -- look up the last assigned customer prefix

  --

  select top 1

  @lastCustomerPrefix = CustomerPrefix

  from dbo.Customer with (tablock holdlock)

  order by

  CustomerID desc;

  --

  -- calculate a numeric version of the customer prefix, so we

  -- can calculate the correct value during the insert simply

  -- by adding two numbers together, then converting the result

  -- back to a string

  --

  set @customerPrefixBase =

  (ascii(substring(@lastCustomerPrefix, 1, 1)) - @aValue) * @letters * @letters +

  (ascii(substring(@lastCustomerPrefix, 2, 1)) - @aValue) * @letters +

  (ascii(substring(@lastCustomerPrefix, 3, 1)) - @aValue);

  --

  -- now create the new customers along with the prefix

  --

  with

  NewCustomer as (

  select CustomerName,

  @customerPrefixBase + (row_number() over (order by CustomerName)) as CustomerPrefixBase

  from @newCustomerExample

  )

  insert into dbo.Customer(

  CustomerName,

  CustomerPrefix

  )

  select CustomerName,

  char(@aValue + (CustomerPrefixBase / @letters / @letters)) +

  char(@aValue + (CustomerPrefixBase / @letters) % @letters) +

  char(@aValue + (CustomerPrefixBase % @letters))

  from NewCustomer;

  set @newCustomerRows = @@rowcount;

  --

  -- now move the entity sequence number

  --

  update dbo.EntitySequence

  set CurrentSequence = CurrentSequence + @newCustomerRows

  where EntityName = 'dbo.Customer';

  --

  -- remember to commit to release the locks

  --

  commit transaction;

  --

  -- let's see the results

  --

  select *

  from dbo.Customer;

  select *

  from dbo.EntitySequence;

  [/font]