sql-server - 在触发器中使用来自其他表的值

Question

我是触发器的新手，所以我不太了解。但我想做的是编写一个触发器来检查员工薪水是否等于或高于该特定功能的薪水。

我的触发器现在看起来像这样:

CREATE TRIGGER Salary_check
ON Employee
AFTER INSERT,UPDATE
AS

    DECLARE @function_id INT 
    DECLARE @salaris MONEY
    DECLARE @function_salary MONEY

    @function_id = SELECT function_id from INSERTED

    @salaris =  SELECT salary
    FROM Employee
    WHERE emp_id = (SELECT emp_id from INSERTED)

    @function_salary = SELECT salary
    FROM Function
    WHERE id = @function_id

    IF @salaris < @function_salary
    BEGIN 
        RAISERROR(‘Salary of an employee should be equal or higher to the required salary of the specific function. ’);
    ROLLBACK TRANSACTION;
    END; 
GO

我想知道我的@function_salary 是否是一个有效值。因为我从功能表而不是员工表中检索它？ (function_id是引用表函数(id)的外键)

Answer 1

触发器中存在多个语法和逻辑问题。我建议您查看文档中的触发器示例。

触发器应该看起来更像这样:

CREATE TRIGGER Salary_check ON Employee AFTER INSERT, UPDATE
AS BEGIN    
    IF (EXISTS (SELECT 1
                FROM inserted i JOIN
                     function f
                     ON f.function_id = i.function_id
                WHERE i.salary < f.salary OR i.salary IS NULL
               )
       )
    BEGIN 
        RAISERROR('Salary of an employee should be equal or higher to the required salary of the specific function.');
        ROLLBACK TRANSACTION;
    END; 
END;  -- trigger
GO