在另一个表/plsql中改变值的触发器

Question

我已经创建了两个表:Employees和Departments

CREATE TABLE EMP
( emp_id number(3) PRIMARY KEY,
dept_id Number(3) NOT NULL,
emp_name Varchar2(50) NOT NULL,
address Varchar2(100),
phone Varchar2(20) NOT NULL,
salary Number(8,2) NOT NULL,
CONSTRAINT fk_DEPT FOREIGN KEY (dept_id) REFERENCES DEPT(DEPT_ID));
CREATE TABLE DEPT
( dept_id number(3) PRIMARY KEY,
dept_name varchar2(50) NOT NULL,
emp_cnt Number(3) NOT NULL)

我需要创建触发器，在EMP表中插入或删除数据后更改DEPT.emp_cnt中的值。

这是我的尝试

create or replace trigger add_emp_to_the_dep
after insert or delete on EMP
for each row 
begin
update DEPT
set emp_cnt = :new.emp_id
where DEPT.dept_id = :new.dept_id;
if INSERTING then 
emp_cnt += 1;
else DELETING then
emp_cnt -= 1;
end if;
end;

Answer 1

语法错误;在Oracle的PL/SQL中没有emp_cnt += 1;这样的东西

试试这样:

create or replace trigger add_emp_to_the_dep
after insert or delete on emp
for each row
begin
if inserting then
update dept set
emp_cnt = emp_cnt + 1
where dept_id = :new.dept_id;
elsif deleting then
update dept set
emp_cnt = emp_cnt - 1
where dept_id = :old.dept_id;
end if;
end;
/               

Answer 2

您可以使用复合触发器来整理更改并进行最小数量的更新:

CREATE TRIGGER add_emp_to_the_dep
FOR INSERT OR UPDATE OR DELETE ON emp
COMPOUND TRIGGER
TYPE ids_type IS TABLE OF EMP.DEPT_ID%TYPE;
TYPE cnt_type IS TABLE OF PLS_INTEGER;
TYPE idx_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
ids  ids_type := ids_type();
cnts cnt_type := cnt_type();
idxs idx_type := idx_type();

PROCEDURE modify_dept_cnt (
id  EMP.DEPT_ID%TYPE,
cnt PLS_INTEGER
)
IS
BEGIN
IF id IS NULL THEN
RETURN;
END IF;
IF NOT idxs.EXISTS(id) THEN
ids.EXTEND;
cnts.EXTEND;
ids(ids.COUNT) := id;
cnts(cnts.COUNT) := cnt;
idxs(id) := ids.COUNT;
ELSE
cnts(idxs(id)) := cnts(idxs(id)) + cnt;
END IF;
END modify_dept_cnt;
AFTER EACH ROW
IS
BEGIN
modify_dept_cnt(:NEW.DEPT_ID, 1);
modify_dept_cnt(:OLD.DEPT_ID, -1);
END AFTER EACH ROW;
AFTER STATEMENT
IS
BEGIN
FORALL i IN 1 .. ids.count
UPDATE dept
SET   emp_cnt = emp_cnt + cnts(i)
WHERE dept_id = ids(i);
END AFTER STATEMENT;
END;
/

那么，如果你这样做了:

INSERT INTO emp (emp_id, dept_id, emp_name, phone, salary)
SELECT 1, 1, 'Alice', '0', 100 FROM DUAL UNION ALL
SELECT 2, 1, 'Betty', '1', 100 FROM DUAL UNION ALL
SELECT 3, 2, 'Carol', '2', 100 FROM DUAL UNION ALL
SELECT 4, 1, 'Debra', '3', 100 FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', '4', 100 FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', '5', 100 FROM DUAL;

它将整理DEPT表的所有更改和UPDATE仅3次，因为添加了3个唯一的DEPT_ID的雇员，而不是执行6次更新，每个插入的行一个。

db<此处小提琴>

Answer 3

create or replace trigger add_emp_to_the_dep
after insert or delete on emp
for each row
begin
if inserting then
update dept set
emp_cnt = emp_cnt + 1
where dept_id = :new.dept_id;
elsif deleting then
update dept set
emp_cnt = emp_cnt - 1
where dept_id = :old.dept_id;
end if;
end;