create trigger lookup on a
for insert,update
as
update a set spell=
case len(A.name)
when 2 then (select rtrim(spell) from spell where word =substring(a.name,1,1))
+
(select rtrim(spell) from spell where word =substring(a.name,2,1))
when 3 then (select rtrim(spell) from spell where word =substring(a.name,1,1))
+
(select rtrim(spell) from spell where word =substring(a.name,2,1))
+
(select rtrim(spell) from spell where word =substring(a.name,3,1))
end
from inserted I,A
where a.name=i.name
for insert,update
as
update a set spell=
case len(A.name)
when 2 then (select rtrim(spell) from spell where word =substring(a.name,1,1))
+
(select rtrim(spell) from spell where word =substring(a.name,2,1))
when 3 then (select rtrim(spell) from spell where word =substring(a.name,1,1))
+
(select rtrim(spell) from spell where word =substring(a.name,2,1))
+
(select rtrim(spell) from spell where word =substring(a.name,3,1))
end
from inserted I,A
where a.name=i.name
这次首先提示是:“超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)”。然后我去掉了CASE WHEN 3时,又出现上述问题:“服务器: 消息 512,级别 16,状态 1,过程 lookup,行 4
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。”