Create trigger that does the same as unique static constraint PL/SQL-CodePudding

As a disclaimer, this question is only for my curiosity and for practicing triggers and compound triggers particularly.

I've been trying to replace the UNIQUE constraint with a trigger in order to understand triggers more, but I haven't been successful so far, mainly because of the global variables that I'm not so comfortable with inside the compound triggers. what I'm trying to do with a trigger :

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

here's what I tried so far (t for type, g for global):

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;
/

I have no clue how to move forward with this trigger and would like suggestions and explanations if possible about how to use globale variables to store data and how to use them on a row level.

CodePudding user response：

Here's one option.

Sample table:

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

Table created.

Trigger:

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>

As you can see, it doesn't contain before statement nor after each row parts; if they aren't used, you don't have to put them into the trigger.

Let's try it:

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.

Re-insert the first combination (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'

That failed (as it should). Let's update existing rows:

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'

Kind of works.

CodePudding user response：

You can count whether a paired value for those columns already exists with a statement level trigger such as

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;
/

which will check if an attempt made to insert the same value pairs more than once, and if so, it will hurl with an error message.

Demo