如何自动增加列中的值

问题描述

您好我有一个名为ElectricityDailyMeterReadings的表，其中有一个名为MachineID的列。

我正在尝试输入机器ID为M001，M002，M003 ..等等。通常我使用的是整数值对于我的主键和我使用简单的增量方法进行自动增量



例如，

Hello i have a table called ElectricityDailyMeterReadings which has a column called MachineID..
I am trying to enter machine ID as M001, M002, M003 ..etc.. Normally i have used integer values for my primary keys and i used simple incremental methods for auto increments

example,

UPDATE mytable
  SET logins = logins + 1
  WHERE id = 12

但是因为这是一个字符串我想知道如何写它..谢谢

But since this is a string i want to know how to write it.. Thank You

推荐答案

create table mytable(
id varchar(6) primary key,
name varchar(30),
someint int identity(0,1)
);

为此创建一个触发器它看起来像这样

for this create a trigger which looks something like this

create trigger [dbo].[dummy]
on [dbo].[mytable]
for insert
as
declare @id varchar(6);
declare @someint int;
select @id=id from inserted;
select @someint=someint from inserted;
if(@someint<10)
begin
update mytable
set id='M'+'00'+convert(varchar,@someint) where id=@id
end
else
if(@someint>=10 and @someint<100)
begin
update mytable
set id='M'+'0'+convert(varchar,@someint) where id=@id
end
else
if(@someint>=100 and @someint<1000)
begin
update mytable
set id='M'+convert(varchar,@someint) where id=@id
end
else-- Since the transaction exceeds the limit so rollback the transation
rollback

我认为这必须解决你的问题uestion

I think this must solve your question

create table mytable(
id varchar(6) primary key,
name varchar(30),
someint int identity(0,1)
);

现在为此我创建一个这样的触发器

now for this i create a trigger like this

create trigger [dbo].[dummy1]
on [dbo].[mytable]
for insert
as
declare @id varchar(6);
declare @someint int;
select @id=id from inserted;
select @someint=someint from inserted;
update mytable
set id='M'+right('000'+convert(varchar,@someint),3) where id=@id

此工作正常

This works fine

这篇关于如何自动增加列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！