请问,有一个meter表中有一个m_id字段(全数字),每当增加记录时,触发器要自动去掉该记录m_id字段左边的0,请问怎样实现?以下是我未完成代码,急用.ALTER TRIGGER [dbo].[abcde] ON [dbo].[Meter]
FOR INSERT
AS
declare @m_id nvarchar(12) BEGIN
select @m_id=m_id from inserted
--@m_id的位数不大于6位,需要将@m_id左边的0去掉,然后重新赋值给meter表中的m_id,请问怎样实现?
END
FOR INSERT
AS
declare @m_id nvarchar(12) BEGIN
select @m_id=m_id from inserted
--@m_id的位数不大于6位,需要将@m_id左边的0去掉,然后重新赋值给meter表中的m_id,请问怎样实现?
END
FOR INSERT
AS
declare @m_id nvarchar(12)
select @m_id=len(m_id) from inserted
if @m_id<=6 return
else
begin
select @m_id=ltrim(cast(m_id as bigint)) from inserted
select @m_id
end
INSTEAD OF INSERT
ASinsert [dbo].[Meter](m_id,...) --字段自己填全了
select stuff(m_id,1,PATINDEX ('%[^0]%',m_id)-1,''),... -- 一样要填全
from inserted go不要用变量
declare @s nvarchar(6)set @s='00123'select @s=right(@s,len(@s)-patindex('%[^0]%',@s)+1)select @s------
123(1 行受影响)
FOR INSERT
AS
BEGIN
update tb
set m_id=right(m_id,len(m_id)-patindex('%[^0]%',m_id)+1)
from tb a,inserted i
where a.主键=i.主键
END
select right(@m_id,(len(@m_id)-charindex("0",@m_id))) from inserted
create table Meters(
m_id int,
m_name varchar(20) unique,-- 唯一,否则触发器更新会导致修改多个值
m_note varchar(20)
)--创建触发器
create trigger trg_Meters on dbo.Meters for insert
as
begin
declare @newid varchar(10),@id varchar(10),@name varchar(20)
select @id=m_id,@name=m_name from inserted
set @newid=cast(cast(@id as int) as varchar(10))
update dbo.Meters
set m_id=@newid
where m_name = @name
end--测试insert into dbo.Meters values(
'0034','Life book','book'
)
select * from dbo.Meters