一个触发器的优化问题求解
create   trigger   tr_t_item_update
on   t_item
for   update
as
if   exists   (select   1   from   inserted
      where   FItemClassID=4   and   len(FNumber)-len(replace(FNumber, '. ', ' ')) <4   )   and   exists(SELECT   FGRType
FROM   t_ItemClass   WHERE   (FItemClassID   =   4)   AND   (FGRType   =   0))   and   exists(SELECT   FGRType
FROM   t_ItemClass   WHERE   (FItemClassID   =   1234567)   AND   (FGRType   =   0))  
begin
      RAISERROR   ( '不允许此类添加 ',16,   1)    
      ROLLBACK   TRANSACTION
end
go

请各位老大看上面的触发器,其中的条件:
and   exists(SELECT   FGRType
FROM   t_ItemClass   WHERE   (FItemClassID   =   4)   AND   (FGRType   =   0))  
and   exists(SELECT   FGRType
FROM   t_ItemClass   WHERE   (FItemClassID   =   1234567)   AND   (FGRType   =   0))  
字段是完全相同的,只是取了二条记录而已,我感觉应该可以优化一下写法,但不知道要怎么写,请高手指点一二.
还有一个问题就是:
现在的控制流程是:
if   1   and   2   and   3  
begin
..
end
只有这三个条件同时满足时触发器起作用,只要一个条件不成立触发器就不会起作用
我现在想法是:
只有满足1   and   2   and   3   (三个条件同时满足)   或是只要   4   这个条件成立,触发器就起作用,要如何写,简单的在
if   1   and   2   and   3   or   4   --(简单的加个4吗)
begin
..
end

------解决方案--------------------
1、不必优化了
2、if 1 and 2 and 3 or 4 简单的加个4可以的，清楚点就写成if (1 and 2 and 3) or 4

------解决方案--------------------
1、Try:
if exists (select 1 from inserted
where FItemClassID=4 and len(FNumber)-len(replace(FNumber, '. ', ' ')) <4 )
and (SELECT count(distinct FItemClassID)
FROM t_ItemClass WHERE (FItemClassID = 4 or FItemClassID = 1234567) AND (FGRType = 0)) =2
begin
...
end


这个看起来好像简化了，但是效率提高还是降低就很难说

------解决方案--------------------
(SELECT count(distinct FItemClassID)
FROM t_ItemClass WHERE (FItemClassID = 4 or FItemClassID = 1234567) AND (FGRType = 0)) =2
——————————————————
我觉得还是楼主原来的写法好一些，那样看起来更清晰一些。性能也不会差的
------解决方案--------------------
create trigger tr_t_item_update
on t_item
for update
as
if exists (select 1 from inserted
where FItemClassID=4 and len(FNumber)-len(replace(FNumber, '. ', ' ')) <4 )
and (SELECT count(distinct FItemClassID)
FROM t_ItemClass WHERE (FItemClassID in(1234567,4) AND (FGRType = 0))> 1

begin
RAISERROR ( '不允许此类添加 ',16, 1)
ROLLBACK TRANSACTION
end