创建与唯一静态约束PL/SQL相同的触发器

Question

作为免责声明，这个问题只是为了我的好奇心，尤其是为了练习触发器和复合触发器。

我一直试图用触发器替换UNIQUE约束，以便更好地理解触发器，但到目前为止我还没有成功，主要是因为我不太熟悉复合触发器中的全局变量。我想用一个触发器做什么：

ALTER TABLE Employee
ADD CONSTRAINT emp_UQ 
UNIQUE (id_emp, id_office);

以下是我迄今为止尝试的内容(t表示类型，g表示全局(：

CREATE OR REPLACE TRIGGER BIUUniqueConstraint
FOR INSERT OR UPDATE ON Employee
COMPOUND TRIGGER
TYPE tIdEmpOffice IS TABLE OF Employee.id_emp%TYPE
INDEX BY VARCHAR2(80);                          
gIdEmpOffice          tIdEmpOffice;
TYPE tId_emp IS TABLE OF Employee.id_emp%TYPE;
gId_emp               tId_emp; 
TYPE tId_office IS TABLE OF Employee.id_office%TYPE;
gId_office            tId_office;
BEFORE STATEMENT IS
BEGIN
SELECT                  e.id_emp, e.id_office
BULK COLLECT INTO   gId_emp, gId_office
FROM                Employee e
ORDER BY            e.id_emp;
FOR j IN 1..gId_emp.COUNT() LOOP
gIdEmpOffice(gId_emp(j)) := gId_office(j);
END LOOP;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(gIdEmpOffice(:NEW.id_emp);
END IF;
END BEFORE EACH ROW;
END BIUCompteParti;
/

我不知道如何使用这个触发器，如果可能的话，我希望得到关于如何使用全局变量存储数据以及如何在行级别上使用它们的建议和解释。

Answer 1

这里有一个选项。

样品表：

SQL> create table employee (id_emp number, id_office number);
Table created.

触发器：

SQL> create or replace trigger trg_emp_unique
2    for insert or update on employee
3  compound trigger
4      type   t_row is record (id_emp number, id_office number);
5      type   t_tab is table of t_row;
6      l_tab  t_tab := t_tab();
7      l_cnt  number;
8
9      before each row is
10      begin
11        l_tab.extend;
12        l_tab(l_tab.last).id_emp := :new.id_emp;
13        l_tab(l_tab.last).id_office := :new.id_office;
14      end before each row;
15
16      after statement is
17      begin
18        for i in l_tab.first .. l_tab.last loop
19          select count(*) into l_cnt
20            from employee
21            where id_emp = l_tab(i).id_emp
22              and id_office = l_tab(i).id_office;
23          if l_cnt > 1 then
24             raise_application_error(-20000, 'Unique constraint violated');
25          end if;
26        end loop;
27        l_tab.delete;
28      end after statement;
29  end trg_emp_unique;
30  /
Trigger created.
SQL>

正如您所看到的，它既不包含before statement，也不包含after each row部分；如果不使用它们，就不必将它们放入触发器中。

让我们试试：

SQL> insert into employee(id_emp, id_office) values (1, 1);
1 row created.
SQL> insert into employee(id_emp, id_office) values (1, 2);
1 row created.

重新插入第一个组合(1, 1(：

SQL> insert into employee(id_emp, id_office) values (1, 1);
insert into employee(id_emp, id_office) values (1, 1)
*
ERROR at line 1:
ORA-20000: Unique constraint violated
ORA-06512: at "SCOTT.TRG_EMP_UNIQUE", line 22
ORA-04088: error during execution of trigger 'SCOTT.TRG_EMP_UNIQUE'

这失败了(应该如此(。让我们更新现有行：

SQL> select * from employee;
ID_EMP  ID_OFFICE
---------- ----------
1          1
1          2
SQL> update employee set id_office = 5;
update employee set id_office = 5
*
ERROR at line 1:
ORA-20000: Unique constraint violated
ORA-06512: at "SCOTT.TRG_EMP_UNIQUE", line 22
ORA-04088: error during execution of trigger 'SCOTT.TRG_EMP_UNIQUE'

---

一种工作。

Answer 2

您可以通过语句级别触发器(如(来计算这些列的成对值是否已经存在

CREATE OR REPLACE TRIGGER trg_chk_unique_emp_dept_id
AFTER INSERT ON employee
DECLARE
val INT;
BEGIN
SELECT NVL(MAX(COUNT(*)),0)
INTO val
FROM employee
GROUP BY id_emp, id_office;
IF val > 1 THEN
RAISE_APPLICATION_ERROR(-20304,
'Each employee may be assigned to a department once at most !');
END IF;
END;
/

它将检查是否多次尝试插入相同的值对，如果是，它将抛出一条错误消息。

演示