在编写触发器时如何引用另一个表中的另一列？(How to reference another column in another table when writing a trigger?)

我有一个表EmpSalary ，它有一个列salaryPaid ，员工的当前工资和一个表Emp ，它有一个列baseSalary ，可用于该员工工作的最低工资。我想为EmpSalary表编写一个触发器，进行一些计算，以确保员工在某个百分比范围内获得报酬（ baseSalary该员工的工作岗位的baseSalary 70％）。我在纸上进行了计算，但是当我在EmpSalary表上编写触发器时，我不确定如何引用Emp表？

CREATE TRIGGER Check_Salary BEFORE INSERT OR UPDATE ON EmpSalary FOR EACH ROW DECLARE v_salary; v_baseSalary; ... BEGIN v_salary := old.salaryPaid v_baseSalary := Emp.baseSalary ... END; /

任何见解都会很棒！试着去参加一个大考试。

I have a table EmpSalary which has a column salaryPaid, the current salary of the employee and a table Emp which has a column baseSalary, the lowest salary available for that employee's job. I want to write a trigger for the EmpSalary table that does some calculation to ensure the employee is being paid within a certain percent range(not 70% more than baseSalary of that employee's job). I have the calculations worked out on paper but I'm not sure how to reference the Emp table when I'm writing a trigger on the EmpSalary table?

CREATE TRIGGER Check_Salary BEFORE INSERT OR UPDATE ON EmpSalary FOR EACH ROW DECLARE v_salary; v_baseSalary; ... BEGIN v_salary := old.salaryPaid v_baseSalary := Emp.baseSalary ... END; /

any insight would be great! Trying to study for a big exam.

最满意答案

只要您从中选择数据的表不是定义触发器的表，您就可以在触发器中执行SELECT。在这种情况下，您可以从EMP中选择数据：

CREATE OR REPLACE TRIGGER Check_Salary BEFORE INSERT OR UPDATE ON EmpSalary FOR EACH ROW DECLARE v_salary NUMBER; v_baseSalary NUMBER; ... BEGIN v_salary := old.salaryPaid; SELECT BASESALARY INTO v_baseSalary FROM EMP WHERE EMP.some_key_column = :old.some_key_column; ... END;

祝你好运。

You can perform a SELECT in a trigger, as long as the table you're SELECTing data from isn't the one on which the trigger is defined. In this case you can SELECT the data from EMP:

Best of luck.

更多推荐