问题描述
我正在创建一个带有公司父表的客户表.已经指示(懊恼)我将为客户表创建一个主键,该主键是公司 ID 的组合,该公司 ID 是客户表中现有的 varchar(4) 列,例如客户.公司
I am creating a customer table with a parent table that is company.It has been dictated(chagrin) that I shall create a primary key for the customer table that is a combination of the company id which is an existing varchar(4) column in the customer table, e.g. customer.company
varchar(9) 主键的其余部分应是一个零填充计数器,随着该公司内的客户数量递增.
The rest of the varchar(9) primary key shall be a zero padded counter incrementing through the number of customers within that company.
例如其中公司 = MSFT,这是 MSFT 记录的第一次插入:PK 应为 MSFT00001在随后的插入中,PK 将是 MSFT00001、MSFT00002 等.那么当 company = INTL 并且插入它的第一条记录时,第一条记录将是 INTL00001
E.g. where company = MSFT and this is a first insert of an MSFT record: the PK shall be MSFT00001on subsequent inserts the PK would be MSFT00001, MSFT00002 etc.Then when company = INTL and its first record is inserted, the first record would be INTL00001
我从我从其他 stackoverflow 响应中创建的一个而不是触发器和一个 udf 开始.
I began with an instead of trigger and a udf that I created from other stackoverflow responses.
ALTER FUNCTION [dbo].[GetNextID]
(
@in varchar(9)
)
RETURNS varchar(9) AS
BEGIN
DECLARE @prefix varchar(9);
DECLARE @res varchar(9);
DECLARE @pad varchar(9);
DECLARE @num int;
DECLARE @start int;
if LEN(@in)<9
begin
set @in = Left(@in + replicate('0',9) , 9)
end
SET @start = PATINDEX('%[0-9]%',@in);
SET @prefix = LEFT(@in, @start - 1 );
declare @tmp int;
set @tmp = len(@in)
declare @tmpvarchar varchar(9);
set @tmpvarchar = RIGHT( @in, LEN(@in) - @start + 1 )
SET @num = CAST( RIGHT( @in, LEN(@in) - @start + 1 ) AS int ) + 1
SET @pad = REPLICATE( '0', 9 - LEN(@prefix) - CEILING(LOG(@num)/LOG(10)) );
SET @res = @prefix + @pad + CAST( @num AS varchar);
RETURN @res
END
我将如何编写我的而不是触发器来插入值并增加此主键.或者我应该放弃它并开始割草业务?
How would I write my instead of trigger to insert the values and increment this primary key. Or should I give it up and start a lawnmowing business?
抱歉,tmpvarchar 变量 SQL 服务器在没有它的情况下给了我奇怪的结果.
Sorry for that tmpvarchar variable SQL server was giving me strange results without it.
推荐答案
虽然我同意反对者的看法,但恕我直言,接受无法改变的"原则往往会降低整体压力水平.尝试以下方法.
Whilst I agree with the naysayers, the principle of "accepting that which cannot be changed" tends to lower the overall stress level, IMHO. Try the following approach.
缺点
- 仅限单行插入.您不会对新客户表进行任何批量插入,因为每次要插入行时都需要执行存储过程.
- 一定数量的密钥生成表争用,因此可能会阻塞.
不过,从好的方面来说,这种方法没有任何与之相关的竞争条件,而且它并不太严重,真正冒犯了我的敏感性.所以...
On the up side, though, this approach doesn't have any race conditions associated with it, and it isn't too egregious a hack to really and truly offend my sensibilities. So...
首先,从密钥生成表开始.它将包含每个公司的 1 行,其中包含您的公司标识符和一个整数计数器,每次执行插入时我们都会增加该计数器.
First, start with a key generation table. It will contain 1 row for each company, containing your company identifier and an integer counter that we'll be bumping up each time an insert is performed.
create table dbo.CustomerNumberGenerator
(
company varchar(8) not null ,
curr_value int not null default(1) ,
constraint CustomerNumberGenerator_PK primary key clustered ( company ) ,
)
其次,您将需要一个这样的存储过程(实际上,您可能希望将此逻辑集成到负责插入客户记录的存储过程中.稍后会详细介绍).此存储过程接受公司标识符(例如MSFT")作为其唯一参数.此存储过程执行以下操作:
Second, you'll need a stored procedure like this (in fact, you might want to integrate this logic into the stored procedure responsible for inserting the customer record. More on that in a bit). This stored procedure accepts a company identifier (e.g. 'MSFT') as its sole argument. This stored procedure does the following:
- 将公司 ID 转换为规范形式(例如大写并修剪前导/尾随空格).
- 如果该行不存在,则将其插入到键生成表中(原子操作).
- 在单个原子操作(更新语句)中,获取指定公司的计数器的当前值,然后递增.
- 然后以指定方式生成客户编号,并通过 1 行/1 列
SELECT
语句返回给调用方.
- Puts the company id into canonical form (e.g. uppercase and trimmed of leading/trailing whitespace).
- Inserts the row into the key generation table if it doesn't already exist (atomic operation).
- In a single, atomic operation (update statement), the current value of the counter for the specified company is fetched and then incremented.
- The customer number is then generated in the specified way and returned to the caller via a 1-row/1-column
SELECT
statement.
给你:
create procedure dbo.GetNewCustomerNumber
@company varchar(8)
as
set nocount on
set ansi_nulls on
set concat_null_yields_null on
set xact_abort on
declare
@customer_number varchar(32)
--
-- put the supplied key in canonical form
--
set @company = ltrim(rtrim(upper(@company)))
--
-- if the name isn't already defined in the table, define it.
--
insert dbo.CustomerNumberGenerator ( company )
select id = @company
where not exists ( select *
from dbo.CustomerNumberGenerator
where company = @company
)
--
-- now, an interlocked update to get the current value and increment the table
--
update CustomerNumberGenerator
set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) ,
curr_value = curr_value + 1
where company = @company
--
-- return the new unique value to the caller
--
select customer_number = @customer_number
return 0
go
您可能希望将其集成到将行插入到客户表中的存储过程中的原因是,它可以将所有数据整合到一个事务中;否则,当插入失败时,您的客户数量可能/将会出现缺口,土地被回滚.
The reason you might want to integrate this into the stored procedure that inserts a row into the customer table is that it makes globbing it all together into a single transaction; without that, your customer numbers may/will get gaps when an insert fails land gets rolled back.
这篇关于sql server:根据计数器和另一列值生成主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!