--触发器和自己写业务逻辑都能实现,你这个操作不复杂的 insert into t1 (studentid,studentname,schoolname,schoolguid) values(@studentid,@studentname,@schoolname,@schoolguid)
--判断t2表里是否存在 存在update 不存在 insert
IF EXISTS (SELECT * FROM T1 WHERE schoolguid=@schoolguid )
update t2 set studentid=studentid+@studentid+';',studentname=studentname+@studentname+';' WHERE schoolguid=@schoolguid
else
insert into t2……
领导要求在SQLSERVER里实现,不让写在业务逻辑里。
create table t
(
id int identity(1,1),
studentid varchar(20),
studentname varchar(20),
schoolname varchar(20),
schoolguid varchar(20)
)
create table t2
(
id int identity(1,1),
schoolname varchar(20),
schoolguid varchar(20),
studentid varchar(2000),
studentname varchar(2000),
)create trigger tri on t
for insert
as
begin
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'T2')
begin
declare @studentid varchar(20);
declare @studentname varchar(20);
declare @schoolname varchar(20);
declare @schoolguid varchar(20);
declare @count int;
select @studentid=studentid,@studentname=studentname,@schoolname=schoolname,@schoolguid=schoolguid from inserted;
if @schoolguid is not null and @schoolguid<>''
begin
select @count=COUNT(*)from t2 where schoolguid=@schoolguid;
if @count=0
insert into t2 values(@schoolname,@schoolguid,@studentid+';',@studentname+';');
else
update t2 set studentid=studentid+@studentid+';',studentname=studentname+@studentname+';' where schoolguid=@schoolguid;
end
end
end
insert into t values('1','李一','A学校','1')
t表
1 1 李一 A学校 1
2 1 李一 A学校 1
3 1 李一 A学校 1
4 1 李一 A学校 1t2表
1 A学校 1 1;1;1;1; 李一;李一;李一;李一;