使用触发器插入后更新多个记录 [英] Updating multiple records after insert with a trigger

问题描述

嗨


基本上我需要在用户将多个记录插入数据库后才能使用此触发器。

  CREATE   TRIGGER  IntelligentThinkingDev 
  ON  AuditResults AFTER  INSERT  
  AS  
  BEGIN  
  DECLARE   @ OPTIONID   INT  
  DECLARE   @ ISoptional   INT  
  DECLARE   @ AuditResul   INT  
  SELECT   @ OPTIONID  = OPTIONID  FRO M  INSERTED 
  SELECT   @ ISoptional  = ISoptional  FROM  INSERTED 
  SET   @ AuditResult  =  CASE   WHEN   @ optionID  =  0  和  @ ISoptional  =  1  那么 
 更新 AuditResults  SET  optionid =  null   WHERE  ResultID =（ SELECT  ResultID  FROM  INSERTED）
  END  

我得到的不正确'UPDATE'附近的语法



提前谢谢

解决方案

多条记录无法触发。每次插入记录时，都会运行触发器。如果你插入10条记录，触发器将运行10次 - 每次插入的记录插入一次。

尝试这个...如果你想更新的选项当前插入的数据。

 创建  TRIGGER  ThisDatabase 
  ON  OtherTable AFTER  INSERT  
  AS  
  BEGIN  
  DECLARE   @ OPTIONID   INT  
  DECLARE   @ISoptional   INT  
  SET   @ OPTIONID  =  SELECT  OPTIONID  FROM  INSERT ED 
  SET   @ ISoptional  =  SELECT  ISoptional  FROM  INSERTED 
  CASE   WHEN   @ optionID  =  0  和  @ ISoptional  =  1  那么 
  BEGIN  
 更新 OtherTable  SET  optionid =  null   WHERE  YourPrimaryKey =（ SELECT  YourPrimaryKey  FROM  INSERTED）
  END  
  END  

Hi
Well basically I need this trigger to work after a user inserts multiple records into the database.

CREATE TRIGGER IntelligentThinkingDev
   ON AuditResults AFTER INSERT
AS 
BEGIN
	DECLARE @OPTIONID INT
	DECLARE @ISoptional INT
	DECLARE @AuditResul INT
	SELECT @OPTIONID =  OPTIONID FROM INSERTED
	SELECT @ISoptional =  ISoptional FROM INSERTED
	SET @AuditResult = CASE WHEN @optionID = 0 and @ISoptional = 1 THEN
	UPDATE AuditResults SET optionid = null WHERE ResultID = (SELECT ResultID FROM INSERTED)	
END

I am getting just the incorrect syntax near 'UPDATE'

Thank you in advance

解决方案

No trigger works on "multiple records". Each time a record is inserted, the trigger will be run. If you insert 10 records, the trigger will run 10 times - once for each inserted record as it is being inserted.

Try this one.. if you are trying to update the optionid of the currently inserted data.

CREATE TRIGGER ThisDatabase
   ON OtherTable AFTER INSERT
AS 
BEGIN
	DECLARE @OPTIONID INT
	DECLARE @ISoptional INT
	SET @OPTIONID = SELECT OPTIONID FROM INSERTED
	SET @ISoptional = SELECT ISoptional FROM INSERTED
	CASE WHEN @optionID = 0 and @ISoptional = 1 THEN
	BEGIN
		UPDATE OtherTable SET optionid = null WHERE YourPrimaryKey = (SELECT YourPrimaryKey FROM INSERTED)
	END
END

这篇关于使用触发器插入后更新多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！