SQL Server - 插入触发器后 - 更新同一表中的另一列

31 人关注

我已经有了这个数据库触发器。

CREATE TRIGGER setDescToUpper
ON part_numbers
 AFTER INSERT,UPDATE
DECLARE @PnumPkid int, @PDesc nvarchar(128)
SET @PnumPkid = (SELECT pnum_pkid FROM inserted)
SET @PDesc = (SELECT UPPER(part_description) FROM inserted)
UPDATE part_numbers set part_description_upper = @PDesc WHERE pnum_pkid=@PnumPkid
这是个坏主意吗？这是为了更新同一个表中的一个列。我希望它在插入和更新时都能触发。 
它可以工作，我只是担心会出现循环情况。更新，在触发器内，触发器被触发，而且一次又一次。这种情况会发生吗？
请不要对大写字母的事情吹毛求疵。疯狂的情况。


         2
         
         个评论


           
            Martin Smith
           
           ：


           
            你的触发器对于多行插入/更新会失败。但是如果你需要存储这个，为什么
            
             part_description_upper
            
            不是一个持久化的计算列呢？(我假设
            
             pnum_pkid
            
            是
            
             part_numbers
            
            的PK？)


           
            @marc_s 这是否会出现错误
            
             Can't update part_numbers ... because it is already used by the statement which invoked this trigger
            
            ?


         sql-server


         sql-server-2008


         triggers


        
         
         
          Lance Perry
         
        
        
         发布于
         
         2011-03-17


        7
        
        个回答


          已采纳


         0
         
         人赞同


          
           这取决于当前在DB上设置的触发器的递归级别。
          
          
           如果你这样做。
          
          SP_CONFIGURE 'nested_triggers',0
RECONFIGURE
或者这样。
ALTER DATABASE db_name
SET RECURSIVE_TRIGGERS OFF
上面那个触发器就不会再被调用，你就安全了（除非你陷入某种死锁；这有可能，但也许我错了）。
尽管如此，我不认为这是一个好主意。一个更好的选择是使用INSTEAD OF触发器。这样你就可以避免在DB上执行第一次（手动）更新。只有在触发器中定义的那个才会被执行。
一个INSTEAD OF INSERT触发器是这样的。
CREATE TRIGGER setDescToUpper ON part_numbers
INSTEAD OF INSERT
BEGIN
    INSERT INTO part_numbers (
        colA,
        colB,
        part_description
    ) SELECT
        colA,
        colB,
        UPPER(part_description)
    ) FROM
        INSERTED
这将自动用这个语句 "替换 "原来的INSERT语句，并对part_description 字段进行明确的UPPER调用。
INSTEAD OF UPDATE 触发器也是类似的（我不建议你创建单一的触发器，把它们分开）。
另外，这也解决了@Martin的评论：它适用于多行插入/更新（你的例子不适用）。


           
            
             
              
               作为一个（我认为）好的习惯，我总是在触发器中发出一个带有触发器名称的打印语句......只是为了在从分析器中运行查询时提供指导。


           
            
             
              
               这是否能处理目标表中的自动递增的列？


           
            
             
              
               @BlackKnight:当然，像往常一样：只要不在
               
                INSERT
               
               语句中引用任何
               
                IDENTITY
               
               列。


           
            
             
              
               @tEsTA 让我更好地解释。假设我想根据一个IDENTITY列来设置另一个列。我猜想IDENTITY列在这时不会被设置。我还能用这个技巧吗，还是需要一个AFTER INSERT？


           
            
             
              
               @BlackKnight 希望我不会太晚（1年的时间也不算太长:P）。但我同意在这种情况下，使用
               
                AFTER INSERT
               
               触发器更容易。为了使用
               
                INSTEAD OF INSERT
               
               ，你必须在触发器中使用两个语句：一个将
               
                INSERT
               
               记录（并通过使用
               
                OUTPUT子句
               
               恢复身份值，另一个
               
                UPDATE
               
               。


          
           
            
             
              另一个选择是将更新语句包含在一个IF语句中，并调用
              
               TRIGGER_NESTLEVEL()
              
              来限制更新的第二次运行。
             
             CREATE TRIGGER Table_A_Update ON Table_A AFTER UPDATE 
IF ((SELECT TRIGGER_NESTLEVEL()) < 2)
BEGIN
    UPDATE a
    SET Date_Column = GETDATE()
    FROM Table_A a
    JOIN inserted i ON a.ID = i.ID
当触发器最初运行时，TRIGGER_NESTLEVEL被设置为1，所以更新语句将被执行。该更新语句将反过来触发相同的触发器，只是这一次TRIGGER_NESTLEVEL被设置为2，更新语句将不会被执行。
你也可以先检查TRIGGER_NESTLEVEL，如果它大于1，就调用RETURN退出触发器。
IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;


           
            
             
              
               
                
                 非常感谢。由于安全原因，许多支持网络的SQL数据库都不允许使用SP_CONFIGURE。


           
            
             
              
               
                
                 需要注意的是，如果事件是由另一个触发器产生的，这个触发器将不会运行。  例如，如果表A被更新，并且一个触发器更新了表B，在那里我们有
                 
                  IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;
                 
                 ，那么表B上的触发器将返回，因为巢穴级别太大。


          
           
            
             
              
               
                使用一个计算列来代替。使用计算列几乎总是比使用触发器更好的主意。
               
               
                请看下面使用UPPER函数的计算列的例子。
               
               create table #temp (test varchar (10), test2 AS upper(test))
insert #temp (test)
values ('test')
select * from #temp
我不想听起来像一张破唱片或其他什么，但这是非常重要的。不要写一个在多条记录插入/更新/删除时不能正常工作的触发器。这是一个非常糟糕的做法，因为这些情况迟早会发生，而你的触发器会导致数据完整性问题，因为它不会失败，准确地说，它只会在其中一条记录上运行程序。这可能会持续很长时间，直到有人发现这个混乱的局面，到那时往往就不可能正确修复数据了。


           
            
             
              
               
                
                 
                  计算列是一个很好的选择。但是你还应该补充一点，为了避免缓慢的选择，我们还可以i）
                  
                   创建
                   
                    持久化
                   
                  
                  的计算列和ii）
                  
                   在计算列上创建索引
                  
                  。


           
            
             
              
               
                
                 
                  是的，但是......给自己写个小测试，比较一下传统列和持久化计算（都有索引），你会选择传统列的......


          
           
            
             
              
               
                
                 
                 
                  Paul Kearney - pk
                 
                
                
                 发布于
                 
                 2013-09-04


          
           
            
             
              
               
                
                 是的，它将递归调用你的触发器，除非你关闭递归触发器的设置。
                
                ALTER DATABASE db_name SET RECURSIVE_TRIGGERS OFF 
MSDN在http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx的递归触发器标题下对这种行为有一个很好的解释。


          
           
            
             
              
               
                
                 
                  是的......有一个额外的步骤来更新一个你可以在最初插入时设置值的表，这可能是一个额外的、可避免的过程。
你是否可以访问原始的插入语句，在那里你可以使用UPPER(part_description)值直接将part_description插入到part_description_upper列？
                 
                 
                  经过思考，你可能没有权限，因为你可能已经做了，所以也应该给出一些选择......
                 
                 
                  1) 取决于对这个part_description_upper列的需求，如果只是为了 "查看"，那么可以只使用返回的part_description值和 "ToUpper() "它（取决于编程语言）。
                 
                 
                  2) 如果想避免 "实时 "处理，可以只创建一个sql作业，在低流量期间每天检查一次你的值，并对任何目前没有设置的列更新为上位的part_description值。
                 
                 
                  3）使用你的触发器（并注意其他人提到的递归）......


          
           
            
             
              
               
                
                 
                  
                  
                   Visual Micro
                  
                 
                 
                  发布于
                  
                  2013-09-04


          
           
            
             
              
               
                
                 
                  在无事可做时退出触发器可能更安全。检查嵌套层次或通过关闭RECURSIVE来改变数据库，都容易产生问题。
                 
                 
                  Ms sql提供了一个简单的方法，在一个触发器中，可以看到特定的列是否被更新。使用UPDATE()方法来查看某些列是否被更新，如UPDATE(part_description_upper)。
                 
                 IF UPDATE(part_description_upper)
  return


          
           
            
             
              
               
                
                 
                  
                   
                   
                    user2746639
                   
                  
                  
                   发布于
                   
                   2013-09-04


          
           
            
             
              
               
                
                 
                  create or replace 
TRIGGER triggername BEFORE INSERT  ON 
table FOR EACH ROW 
BEGIN
Write any select condition if you want to get the data from other tables
:NEW.COLUMNA:= UPPER(COLUMNA); 
--:NEW.COUMNa:= NULL;