SQL INSERT触发器出现问题

Question

我正在寻求一些关于这个触发器的帮助，我正在努力工作。

基本上这就是我想要做的。我们有DMS软件，它可以写入数据库，对于特定的INSERT值，我希望触发。

这是一个将被处理的INSERT语句的示例。

INSERT INTO DOCSADM.ACTIVITYLOG (CR_IN_USE,ACTIVITY_DESC,BILLED_ON,BILLABLE,PAGES,KEYSTROKES,
TYPE_TIME,ELAPSED_TIME,TYPIST,AUTHOR,START_DATE,ACTIVITY_TYPE,REF_DOCUMENT,REF_LIBRARY,APPLICATION,VERSION_LABEL,DOCNUMBER,SYSTEM_ID) 
VALUES ('','DOCSFusion','1753-01-01','',0,0,0,0,1920,1920,'2020-08-26T10:17:56',**115**,0,-1,1173,'',75,3252)

但我只希望当我们在INSERT语句中看到粗体部分的值115(Activity_type值(时触发。

对于所有其他不是115的值，我什么都不想做。

这就是我目前所拥有的：

CREATE TRIGGER BW_TRIGGER
ON  DOCSADM.ACTIVITYLOG
AFTER INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Declare some variable and set it as a value of 115.
--Example:
DECLARE @AlogType int = (SELECT I.ACTIVITY_TYPE FROM DOCSADM.ACTIVITYLOG A, INSERTED I) --This is the value you are looking for regarding the DM client/Matter actitivty type.
DECLARE @AlogDesc varchar(32) = (Select i.ACTIVITY_DESC from docsadm.ACTIVITYLOG A, INSERTED I) 
--Next, you should have a fork or path in your trigger to determine how it proceeds.
--Path 1: The @AlogType value matches the inserted value so you want to process the rest of the trigger.  Example path – “ProcessTrigger:”
--Path 2: The @AlogType value does NOT match the inserted value, you want to exit the trigger.  Example Path – “ExitTrigger:”

IF @AlogType <> 115 
GOTO TriggerExit;
ELSE
Begin
/*Create first temp table to collect insert values*/  --This table will have the SysID Value and the corresponding docnumber for the items you want.  
--You can add whatever other values you think you need.
CREATE TABLE #TempSet1
(
AlogsysID INT,
Docnum INT,
AlogDate Varchar(64),
AlogTypist INT,
AlogAuthor INT,
AlogDesc varchar(32),
ALOGVER varchar(10),
ALOG_MATTER INT
)

INSERT INTO #TempSet1 (AlogsysID,Docnum,AlogDate,AlogTypist,AlogAuthor, ALOG_MATTER)
--SELECT  You SELECT STATEMENT WILL GO HERE MODIFIED TO POPULATE THE TABLE WITH THE DOCNUMBERS YOU WANT!!
select top 1 System_id, docnumber, LAST_ACCESS_DATE, TYPIST, AUTHOR, MATTER from docsadm.PROFILE where EXISTS  (SELECT CLIENT.SYSTEM_ID FROM DOCSADM.CLIENT  INNER JOIN DOCSADM.MATTER ON MATTER.CLIENT_ID = CLIENT.SYSTEM_ID  
WHERE MATTER.SYSTEM_ID =@AlogDesc  OR  INH_LUP_SEC_FROM IS NULL OR INH_LUP_SEC_FROM = 0) AND MATTER=@AlogDesc

/*Set variable @SysID as the LASTKEY value -1.  This will be used to set the SysID column on the #TempSet table*/ 
--DECLARE @SysID INT = (SELECT LASTKEY FROM DOCSADM.SEQ_SYSTEMKEY) -1;
/*Set the SysID value for every row on the #TempSet1 table as the @SysID variable +1*/
--UPDATE #TempSet1
--SET @SysID = AlogsysID = @SysID + 1
--Your #TempSet should now be set with ALL of the System_IDs and Docnumbers necessary for your insert!!!!—
--Verify this by doing a select against the #TempSet1 Table
SELECT * FROM #TempSet1;
--Next you need to set the SystemID to the correct value for future processing.  To do this, we need to get a total count from the #TempSet table.
/*Set a variable to update the NEXTKEY value on the DOCSADM.SEQ_SYSTEMKEY table.  The NEXTKEY value is used for the SYSTEM_ID field*/
--DECLARE @SeqUpdateCount INT = (SELECT COUNT(*) FROM #TempSet1);
/*Update the LASTKEY Value on the SEQ_SYSTEMKEY table to the next available value for DM.*/
--UPDATE DOCSADM.SEQ_SYSTEMKEY SET LASTKEY = LASTKEY+@SeqUpdateCount
--If you have all the values you need in your temp table, you can now insert them into the ACTIVITYLOG table.
--INSERT INTO DOCSADM.ACTIVITY 
--(SYSTEM_ID, DOCNUMBER, START_DATE, version, EXT,)
--SELECT 
--AlogSysID,Docnum,GETUTCDATE(),BLAH, BLAH
--FROM #TableSet1

INSERT INTO DOCSADM.ACTIVITYLOG 
(SYSTEM_ID,
DOCNUMBER,
START_DATE,
TYPIST,
AUTHOR,
ACTIVITY_DESC,
VERSION_LABEL, 
ACTIVITY_TYPE)
SELECT 
AlogsysID, Docnum,AlogDate,AlogTypist, AlogAuthor, ALOG_MATTER, '',115
FROM #TempSet1;
--Now you need to Drop the Temp Table
DROP TABLE #TempSet1
--Go to the other half of your path above to exit the trigger.
END

TriggerExit:

END
Go

但是当我尝试在这个表上运行任何INSERT语句时，我会收到这个错误消息。activity_type的值是否为115并不重要

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我知道问题出在触发器的这一部分：

INSERT INTO #TempSet1 (AlogsysID,Docnum,AlogDate,AlogTypist,AlogAuthor, ALOG_MATTER)
--SELECT  You SELECT STATEMENT WILL GO HERE MODIFIED TO POPULATE THE TABLE WITH THE DOCNUMBERS YOU WANT!!
SELECT TOP 1 
System_id
, docnumber
, LAST_ACCESS_DATE
, TYPIST
, AUTHOR
, MATTER 
FROM docsadm.PROFILE 
WHERE EXISTS  (SELECT CLIENT.SYSTEM_ID 
FROM DOCSADM.CLIENT  
INNER JOIN DOCSADM.MATTER 
ON MATTER.CLIENT_ID = CLIENT.SYSTEM_ID  
WHERE MATTER.SYSTEM_ID =@AlogDesc  
OR  INH_LUP_SEC_FROM IS NULL 
OR INH_LUP_SEC_FROM = 0)
AND MATTER=@AlogDesc

是SELECT语句导致它失败。我知道这个语句会带回多行，但我只需要其中一行的值，这样我就可以将这个值用于INSERT。我想拥有"；选择顶部1"；会为我做这件事，但它并没有像我认为的那样起作用。我错过了什么？

Answer 1

如果非要我猜的话，我会说你的问题在这里：

DECLARE @AlogType int = (SELECT I.ACTIVITY_TYPE FROM DOCSADM.ACTIVITYLOG A, INSERTED I) --This is the value you are looking for regarding the DM client/Matter actitivty type.
DECLARE @AlogDesc varchar(32) = (Select i.ACTIVITY_DESC from docsadm.ACTIVITYLOG A, INSERTED I) 

ACTIVITYLOG和INSERTED是如何结合在上面的？而没有它将是CCD_ 9的位置。为什么要将ACTIVITYLOG拖到其中，只需使用INSERTED即可。此外，请尝试停止使用隐式联接(我可以在脚本后面看到，您使用了正确的、更详细的联接语法(

尝试：

DECLARE @AlogType int = (SELECT I.ACTIVITY_TYPE FROM INSERTED I) --This is the value you are looking for regarding the DM client/Matter actitivty type.
DECLARE @AlogDesc varchar(32) = (Select i.ACTIVITY_DESC from INSERTED I) 

请注意，这只适用于单个插入件。当您进行批量插入时，INSERTED是一个包含多行的表，您将再次遇到问题。