34,576
社区成员
发帖
与我相关
我的任务
分享
--先给你一个字符串分割函数
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
go
--如你要分解的字符串是'AAA/ID3,BBB/ID4,CCC/ID3 '
select * from dbo.f_splitstr('AAA/ID3,BBB/ID4,CCC/ID3',',')
/*
F1
----------------------------------------------------------------------------------------------------
AAA/ID3
BBB/ID4
CCC/ID3
(所影响的行数为 3 行)
*/
go
create trigger trig_a_insert on a
for insert,update
as
declare @str varchar(100),@str1 varchar(100)
select @str=(select date from inserted),@str1=(select date from deleted)
--根据更新前的date,更新状态为1
update 表c set 状态=1 from (select f1 from dbo.f_splitstr(@str1,',')) d,b
where left(d.f1,charindex('/',d.f1)-1)=b.name and b.id=c.id
--然后在根据更新的列【date】,将C表的【AID】更新:
update 表c set aid=right(d.f1,len(d.f1)-charindex('/id',d.f1)-2) from (select f1 from dbo.f_splitstr(@str,',')) d,b
where left(d.f1,charindex('/',d.f1)-1)=b.name and b.id=c.id
--为什么要分成两次更新呢?而不合成一个:
--如下:
/*
update 表c set aid=right(d.f1,len(d.f1)-charindex('/id',d.f1)-2),状态=1 from (select f1 from dbo.f_splitstr(@str,',')) d,b
where left(d.f1,charindex('/',d.f1)-1)=b.name and b.id=c.id
*/
--先给你一个字符串分割函数
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
go
--如你要分解的字符串是'AAA/ID3,BBB/ID4,CCC/ID3 '
select * from dbo.f_splitstr('AAA/ID3,BBB/ID4,CCC/ID3',',')
/*
F1
----------------------------------------------------------------------------------------------------
AAA/ID3
BBB/ID4
CCC/ID3
(所影响的行数为 3 行)
*/
go
create trigger trig_a_insert on a
for insert,update
as
declare @str varchar(100),@str1 varchar(100)
select @str=(select date from inserted),@str1=(select date from deleted)
--根据更新前的date,更新状态为1
update 表c set 状态=1 from (select f1 from dbo.f_splitstr(@str1,',')) d,b
where left(d.f1,charindex('/',d.f1)-1)=b.name and b.id=c.id
--然后在根据更新的列【date】,将C表的【AID】更新:
update 表c set aid=right(d.f1,len(d.f1)-charindex('/id',d.f1)-2) from (select f1 from dbo.f_splitstr(@str,',')) d,b
where left(d.f1,charindex('/',d.f1)-1)=b.name and b.id=c.id
--为什么要分成两次更新呢?而不合成一个:
--如下:
/*
update 表c set aid=right(d.f1,len(d.f1)-charindex('/id',d.f1)-2),状态=1 from (select f1 from dbo.f_splitstr(@str,',')) d,b
where left(d.f1,charindex('/',d.f1)-1)=b.name and b.id=c.id
*/
create trigger trig_insert_a on a
for insert
as
update C表 set aid=你要更新的值 from inserted a where a.id=c.id