触发插入新记录之前检查部门的平均工资
可以说,员工表如下触发插入新记录之前检查部门的平均工资
EID ENAME DEPTNO SALARY
1 john 10 100
2 jau 10 300
3 cau 10 200
4 cha 20 200
5 cwea 20 500
6 dan 20 200
7 an 20 300
我要检查是否添加任何新员工,新员工的工资应比平均水平该部门的工资,这应该在触发器中完成。
所以我创建触发如下
create or replace trigger tg_emp before insert on employee for each row
declare
avgsal number;
highsalary EXCEPTION;
BEGIN
select avg(salary) into avgsal from employee where deptno = :NEW.deptno;
if :NEW.salary < avgsal
then
raise highsalary;
end if;
EXCEPTION
when highsalary then
Raise_Application_Error (-20343, 'salary is less than the avg salary in this
department');
WHEN others THEN
Raise_Application_Error (-20353, 'other error probably table mutation
error');
END;
你也知道这段代码它适用于只是个别刀片像下面
insert into employee values (8, 'jj', 10, 500);
,但如果它是一个多刀片又像
insert into employee
select seq_emp.next, 'ffgg', 10, 400 from all_tab_columns where rownum < 5;
它引发表突变错误(我知道上面的插入没有意义但是我只是将它用作一个语句中多插入的示例)。
那么我们如何使用全局临时表来解决这个问题?
我想我是能够使用1 GTT和1语句触发前和1行触发器之前触发
之前解决这个问题,如下CREATE GLOBAL TEMPORARY TABLE employee_GTT (
id NUMBER,
name VARCHAR2(20),
deptno number,
salary number
)
ON COMMIT DELETE ROWS;
语句级触发器之前
create or replace trigger emp_avg_load before insert on employee
begin
insert into dept_avg
select deptno, avg(salary), count(deptno) from employee group by deptno;
dbms_output.put_line('getting data from GTT');
end;
行级
create or replace trigger tg_emp before insert on employee for each row
declare
avgsal number;
ct number;
highsalary EXCEPTION;
BEGIN
avgsal := :new.salary;
select avgsal, count into avgsal, ct from dept_avg where deptno =
:NEW.deptno;
if :NEW.salary < avgsal
then
raise highsalary;
else
update dept_avg
set count = count +1,
avgsal = (avgsal+:NEW.salary)/(count+1)
where deptno = :NEW.deptno;
end if;
EXCEPTION
when highsalary then
Raise_Application_Error (-20343, 'salary is less than the avg salary in this
department');
WHEN others THEN
Raise_Application_Error (-21343, 'some other error');
END;
如果我弄错了,请纠正我。
首先,您使用临时表的方式是个好主意。
但是你的交易期间更新的平均工资的方式看起来错误我。事实上,取决于Oracle如何处理更新(插入顺序),您将不会得到相同的结果。
- 首先,既然您只在工资高于平均水平时才插入员工,那么平均值只能增加。
- 现在,如果最高工资是先插入的,那么平均值可能会增加太多,以便插入下一位员工。
您有困难,因为您的要求不够清楚。
我不认为这是真正的用例来改变平均工资,而你插入行。您必须在交易层面考虑它:因此,在交易发生之前,平均工资定义为。所以在插入时不需要有平均变化。让交易中的平均水平保持不变。
我会删除这部分:
else
update dept_avg
set count = count +1
, avgsal = (avgsal+:NEW.salary)/(count+1)
where deptno = :NEW.deptno;
其实GTT必须更新,因为在一个插入语句中,我可以有多个插入到同一个部门下面的例子。 插入件插入雇员 选择8, 'JJ',10,600从双 工会 这些插入平均为部门之前从双 选择9, 'JGH',10,250 10是200,第一次插入符合avgsal条件,但是现在如果我没有更新GTT,那么第二次插入也满足条件,如果GTT更新为第一次插入,那么第10次的avgsal将是300,所以第二次插入将失败。 –
我只是不会使用触发器来做你想要的。过于复杂。而要求似乎不合逻辑。你可以用PL SQL包处理这个问题。 –
你确定执行单行插入它的工作原理?您无法对使用触发器的同一张桌面进行查询。你必须使用复合触发器。无论如何,为了让您在同一张桌子上没有那么多的触发器,我建议您创建一个复合触发器。快速谷歌将帮助你在sintax。但是如果你需要帮助,只是大喊大叫。 –
是的,执行一个单行插入将会起作用,这是一个用于修改表错误的例外。我会寻找复合触发器。谢谢你让我知道。 –