SQL Server、BIG挑战、更新触发器或过程(任何有效的)

Question

我有两个表

员工分配
EmpId | LocationNo |其他相关

和

月度交易
EmpID|Paycode|InputAmount|和其他相关

我想要一个触发器（或任何有效的），这样，如果：

1。 EmpAssignment.LocationNo插入或更新某个EmpID的值104或105，然后MonthlyTransaction.Paycode和MonthlyTtransaction.InputAmount分别插入值35和12。

2.插入某个EmpID的EmpAssignment.LocationNO，用值204或205更新，然后插入MonthlyTransaction.Paycode和MonthlyTtransaction.InputAmount分别用值36和15。

现在，更巧妙的部分，MonthlyTransaction.EmpID是一个外键，这意味着每个EmpIDmpID行。

如果上面没有Paycode和InputAmount的特定EmpID行，则触发器应插入其他更新。

到目前为止：

CREATE TRIGGER tr_ProvincialAllowance
ON [dbo].[Empassignment]
FOR INSERT, UPDATE
AS
UPDATE M
SET Paycode = 35, InputAmnt = 12
FROM MonthlyTransaction AS M
INNER JOIN inserted E
ON E.EmpId = M.EmpId AND E.LocationNo = 104

上述触发器的问题之一是，它将更新所有MonthlyTransaction。EmpID付费码和带35和12 的输入Amnt

我知道这是一个很大的要求，但请有人救我。

Answer 1

首先使用UPDATE，然后使用INSERT所有未找到的内容，类似于此（仅显示Paycode=35、InputAmnt=12和LocationNo=104）。

CREATE TRIGGER tr_ProvincialAllowance
ON [dbo].[Empassignment]
FOR INSERT, UPDATE
AS
UPDATE M
SET InputAmnt = 12
FROM MonthlyTransaction AS M
INNER JOIN inserted E
    ON E.EmpId = M.EmpId AND E.LocationNo = 104 AND Paycode = 35
INSERT M
SET EmpId = E.EmpID, InputAmnt = 12, Paycode = 35
FROM inserted E
LEFT JOIN MonthlyTransaction AS M
    ON E.EmpId = M.EmpId AND E.LocationNo = 104 AND Paycode = 35
WHERE M.EmpID  NULL

如果使用SQL 2008或更高版本，则可以使用MERGE而不是UPDATE和INSERT

Answer 2

这是您的触发器。使用CTE设置Source和Target的条件，然后简单的MERGE语句就可以完成剩下的工作

CREATE TRIGGER tr_ProvincialAllowance
ON [dbo].[Empassignment]
AFTER INSERT, UPDATE
AS
BEGIN
    WITH CTE_Target AS 
    (
        SELECT m.* 
        FROM MonthlyTransaction m
         INNER JOIN INSERTED e ON e.EmpID = m.EmpID
        WHERE m.PayCode IN (35,36)
         AND m.InputAmount IN (12,15)   
    )
    ,CTE_Source AS
    (
        SELECT i.empID
            ,i.LocationNo
            ,CASE WHEN i.LocationNo IN (104,105) THEN 35 ELSE 36 END AS PayCode
            ,CASE WHEN i.LocationNo IN (104,105) THEN 12 ELSE 15 END AS InputAmount
        FROM INSERTED i
        WHERE i.LocationNo  IN (104,105,204,205)
    )  
    MERGE CTE_Target t
    USING CTE_Source s
    ON s.EmpID = t.EmpID
    WHEN MATCHED THEN 
        UPDATE SET t.PayCode = s.PayCode, T.InputAmount = s.InputAmount
    WHEN NOT MATCHED THEN 
        INSERT (EmpID, PayCode,InputAmount) 
        VALUES (s.EmpID, s.PayCode, s.InputAmount);
END

SQLFiddle DEMO