一个表中,有id,logo,tp几个字段,想实现这样的效果:当向表中插入新数据的时候,如果logo的值不为空,那么tp=logo的值,如果是更新表,则先判断logo字段的值是否为空,如果不为空,再判断logo的新值是否和表中的logo的值相等,如果不想等,且表中相应tp的值不为空,那么tp的值就等于logo的新值+'|'+tp的旧值。现在插入值的时候触发器运行结果正确,更新的时候,如果tp的值为空,则也正确,但是已有表中tp的值不为空的时候,不能在它的基础上实现附加效果,同时查询分析器中update tp字段为null也执行不成功,请大家帮我看一下,这是怎么回事,谢谢
--drop trigger updateTpFromLogo
create trigger updateTpFromLogo
on qy_info for insert,update
as
begin
declare @logo nvarchar(100) --logo的值
declare @tp nvarchar(100) --图片列的值
declare @temp nvarchar(100) --临时变量,用来保存传来的logo
declare @id int --标识
--set nocount on
/*如果是插入操作*/
if exists(select 1 from inserted)
begin
select @logo=logo,@id=id from inserted
if ltrim(@logo)!=''
update qy_info set tp=@logo where id=@id
end
/*如果是更新操作*/
else if exists(select 1 from inserted a join updated on a.id=b.id)
begin
select @logo=logo,@id=id from updated
select @tp=tp,@temp=logo from qy_info where id=@id
if @temp!=@logo
begin
if ltrim(@tp)=''
update qy_info set tp=@logo where id=@id
else
update qy_info set tp=isnull(@logo,'')+'|'+@tp where id=@id
end
end
--set nocount off
end
--drop trigger updateTpFromLogo
create trigger updateTpFromLogo
on qy_info for insert,update
as
begin
declare @logo nvarchar(100) --logo的值
declare @tp nvarchar(100) --图片列的值
declare @temp nvarchar(100) --临时变量,用来保存传来的logo
declare @id int --标识
--set nocount on
/*如果是插入操作*/
if exists(select 1 from inserted)
begin
select @logo=logo,@id=id from inserted
if ltrim(@logo)!=''
update qy_info set tp=@logo where id=@id
end
/*如果是更新操作*/
else if exists(select 1 from inserted a join updated on a.id=b.id)
begin
select @logo=logo,@id=id from updated
select @tp=tp,@temp=logo from qy_info where id=@id
if @temp!=@logo
begin
if ltrim(@tp)=''
update qy_info set tp=@logo where id=@id
else
update qy_info set tp=isnull(@logo,'')+'|'+@tp where id=@id
end
end
--set nocount off
end
更新表这里也会有值
charindex('|'+@value+'|','|'+tp+'|')>0
else if exists(select 1 from inserted a join updated on a.id=b.id)
-------------------------------没有updated表,只有inserted和deleted~~~
/*如果是插入操作*/
if exists(select 1 from inserted a left join deleted b on a.id=b.id where b.id is null)/*如果是更新操作*/
if exists(select 1 from inserted a inner join deleted b on a.id=b.id)
那改成这样/*如果是插入操作*/
if not exists(select 1 from deleted)/*如果是更新操作*/
if exists(select 1 from inserted a inner join deleted b on a.id=b.id)
on qy_info for insert,update
as
begin
declare @logo_new nvarchar(100) --logo的值
declare @logo_old nvarchar(100) --logo的值
declare @tp nvarchar(100) --图片列的值
declare @id int --标识
--set nocount on
/*如果是插入操作*/
select @logo_new=i.logo,@id=i.id,@logo_old=d.logo,@tp=i.tp
from isnerted i left join deleted d on i.id=d.idif @logo_old is null
update qy_info set tp=@logo_new where id=@id
else
if @logo_new<>@logo_old
update qy_info set tp=isnull(@logo_new,'')+'|'+isnull(@tp,'') where id=@id
end
对了,请问一下,表inserted中的数据在insert和update的时候,是一样用的么?比如插入时,那个自增的ID,在insert的时候,它的值就是系统根据表的记录算出来的,而update中,那个id的值就是要修改的那个记录的ID值么?
create trigger updateTpFromLogo
on qy_info for insert,update
as
begin
declare @logo nvarchar(100) --logo的值
declare @tp nvarchar(100) --图片列的值
declare @logo_old nvarchar(100) --临时变量,用来保存旧的logo
declare @id int --标识
set nocount on
/*如果是插入操作*/
if not exists(select 1 from deleted)
begin
select @logo=logo,@id=id from inserted
if ltrim(@logo)!=''
update qy_info set tp=@logo where id=@id
end
/*如果是更新操作*/
else
begin
select @logo=logo,@id=id from inserted
select @tp=tp,@logo_old=logo from qy_info where id=@id
if @logo_old<>@logo
begin
if @tp is null
update qy_info set tp=@logo where id=@id
else
update qy_info set tp=isnull(@logo,'')+'|'+isnull(@tp,'') where id=@id
end
end
set nocount off
end
create trigger updateTpFromLogo
on qy_info for insert,update
as
begin
declare @logo nvarchar(100) --logo的值
declare @tp nvarchar(100) --图片列的值
declare @logo_old nvarchar(100) --临时变量,用来保存旧的logo
declare @id int --标识
set nocount on
/*如果是插入操作*/
if not exists(select 1 from deleted)
begin
select @logo=logo,@id=id from inserted
if ltrim(@logo)!=''
begin
alter table qy_info disable trigger updateTpFromLogo
update qy_info set tp=@logo where id=@id
alter table qy_info enable trigger updateTpFromLogo
end
end
/*如果是更新操作*/
else
begin
select @logo=logo,@id=id from inserted
select @tp=tp,@logo_old=logo from qy_info where id=@id
if @logo_old<>@logo
begin
alter table qy_info disable trigger updateTpFromLogo
if @tp is null
update qy_info set tp=@logo where id=@id
else
update qy_info set tp=isnull(@logo,'')+'|'+isnull(@tp,'') where id=@id
alter table qy_info enable trigger updateTpFromLogo
end
end
set nocount off
end
如果没有触发器,这句可以正常执行,有了触发器,执行了该操作以后,查询分析器提示一行受影响,但是select 一看,发现tp的值未发生改变。
还有,我的触发代码如果像下面那样写,就可以实现我想要的效果,但是前提是不判断新的logo是否等于旧的logo,如果我的判断条件这样写:if @logo!=@logo_olde那么就没有执行下去,即虽在提示一行受影响,但是实际数据无任何改变,下面是代码:create trigger updateTpFromLogo
on qy_info for insert,update
as
begin
declare @logo nvarchar(100) --logo的值
declare @tp nvarchar(100) --图片列的值
declare @logo_old nvarchar(100) --临时变量,用来保存旧的logo
declare @id int --标识
set nocount on
/*如果是插入操作*/
if not exists(select 1 from deleted)
begin
select @logo=logo,@id=id from inserted
if ltrim(@logo)!=''
begin
alter table qy_info disable trigger updateTpFromLogo
update qy_info set tp=@logo where id=@id
alter table qy_info enable trigger updateTpFromLogo
end
end
/*如果是更新操作*/
else
begin
select @logo=logo,@id=id from inserted
select @tp=tp,@logo_old=logo from deleted where id=@id
if @logo<>'' --这里如果改成if @logo<>@logo_old,则执行后,数据没有变化
begin
alter table qy_info disable trigger updateTpFromLogo
if @tp is null
update qy_info set tp=@logo where id=@id
else
update qy_info set tp=isnull(@logo,'')+'|'+isnull(@tp,'') where id=@id
alter table qy_info enable trigger updateTpFromLogo
end
end
set nocount off
end
AS
BEGIN
INSERT qy_info(ID,LOGO,TP)
SELECT ID,LOGO,CASE WHEN ISNULL(LOGO,'')<>'' THEN LOGO ELSE '' END
FROM INSERTED
END
少打了个,汗
我的这个触发器每次只需要处理一条记录的,不用考虑多条同时更新。
还有77 的写法也不错,不过我现在主要是更新的时候有问题,麻烦哪位帮我看一下,至于我的描述,我似乎描述的比较清楚了,如果不清楚的朋友,请建一个表:
create table qy_info
([id] [int] identity(1,1) primary key,[logo] [varchar](100) null,[tp] [varchar](500) null)
go
然后把我的触发器代码加上去,再执行插入和更新数据看一下就知道是怎么回事了。