触发以从表中删除记录，如果它存在

Question

我有下表和表lightee_assignment_future，具有相同的列。

CREATE TABLE dbo.employee_assignment (
    employee_id BIGINT,
    department_id BIGINT,
    job_title_id BIGINT,
    manager_id BIGINT,
    effective_start_date SMALLDATETIME,
    effective_end_date SMALLDATETIME,
    action_code VARCHAR(60)
);

我需要一个触发器，该触发器检查是否employee_id和depastion_id和job_title_id and manager_id lightsee_assignment_future /code>每当来自雇员_assignment的记录被更新时，如果是这样，则从lightsee_assignment_future将其删除。这4个ID是外国钥匙。

我想到了这样的事情：

CREATE OR ALTER TRIGGER t_v_employee_assignment_update3 on employee_assignment
AFTER UPDATE
AS
BEGIN           
    DECLARE @emp_id BIGINT = (SELECT employee_id from inserted)
    DECLARE @dep_id BIGINT = (SELECT department_id from inserted)
    DECLARE @job_id BIGINT = (SELECT job_title_id from inserted)
    DECLARE @man_id BIGINT = (SELECT manager_id from inserted)

    
    DELETE FROM employee_assignment_future
    WHERE @emp_id = employee_assignment_future.employee_id 
    AND @dep_id = employee_assignment_future.department_id
    AND @job_id = employee_assignment_future.job_title_id
    AND @man_id = employee_assignment_future.manager_id
END;

SELECT * FROM employee_assignment_future
SELECT * FROM employee_assignment

UPDATE employee_assignment
SET employee_id = 4, department_id = 2, job_title_id = 8, manager_id = 3, effective_start_date ='2019-11-13 00:00:00', effective_end_date = NULL, action_code = NULL
WHERE employee_id = 64;

但是，它并没有从employee_assignment_future中删除任何内容，也没有在雇用> lightsee_assignment中更新任何内容。

Answer 1

简单的delete + JOIN应检测到将来是否存在记录，然后可以删除。

以下删除可以是扳机的整个主体。

    DELETE F
    FROM employee_assignment_future F
    INNER JOIN Inserted I
        ON I.employee_id = F.employee_id 
        AND I.department_id = F.department_id
        AND I.job_title_id = F.job_title_id
        AND I.manager_id = F.manager_id;

注意：您不能假设插入（或已删除）只有一个行。您需要使用基于设置的操作并具有0-N行的处理。