Livesql Trigger IF statement-CodePudding

I need to assume each employee has a cap of 100 hours per project. My task is to develop a trigger to track overtime hours when employee exceed the cap for the project? I need help with the trigger statement

create table EMP_PROJ( empNo number(4,0) NOT NULL, projNo number(4,0) NOT NULL, hoursWorked number(4,2) NOT NULL, constraint EMP_PROJ_empNo_projNo_PK primary key (empNo, projNo));

CREATE TABLE EMP_PROJ_OVERTIME( EMPNO NUMBER(4,0) NOT NULL, PROJNO NUMBER(4,0) NOT NULL, HOUROT NUMBER(22) NOT NULL, CONSTRAINT EMP_PROJ_OVERTIME_EMPNO_PROJNO_PK PRIMARY KEY (EMPNO, PROJNO));

This is what I have so far.

CREATE OR REPLACE TRIGGER OVERTIME_HOURS
AFTER INSERT OR UPDATE OF hoursWorked ON EMP_PROJ
declare 
hoursWorked number(22);
BEGIN
        IF(hoursWorked > 100) 
        THEN
        INSERT INTO emp_proj_overtime(empNo, projNo, hourOt)
        SELECT empNo, projNo, hoursWorked - 100
        FROM EMP_PROJ;
        END IF;
END;

The problem with this trigger is it didn't send the hours over a 100 to the overtime table.

CodePudding user response：

You don't select in a trigger; just use :new pseudorecord's values.

Sample tables:

SQL> CREATE TABLE emp_proj (
  2      empno       NUMBER(4, 0) NOT NULL,
  3      projno      NUMBER(4, 0) NOT NULL,
  4      hoursworked NUMBER(6, 2) NOT NULL,  -- can't be NUMBER(4, 2) as you can't insert values => 100
  5      CONSTRAINT emp_proj_empno_projno_pk PRIMARY KEY ( empno,
  6                                                        projno )
  7  );

Table created.

SQL> CREATE TABLE emp_proj_overtime (
  2      empno  NUMBER(4, 0) NOT NULL,
  3      projno NUMBER(4, 0) NOT NULL,
  4      hourot NUMBER(22) NOT NULL,
  5      CONSTRAINT emp_proj_overtime_empno_projno_pk PRIMARY KEY ( empno,
  6                                                                 projno )
  7  );

Table created.

Trigger: there can be only one row for each [empno, projno] combination due to primary key constraint which is defined as a composite key.

SQL> CREATE OR REPLACE TRIGGER overtime_hours AFTER
  2      INSERT OR UPDATE OF hoursworked ON emp_proj
  3      FOR EACH ROW
  4      WHEN ( new.hoursworked > 100 )
  5  BEGIN
  6      INSERT INTO emp_proj_overtime (
  7          empno,
  8          projno,
  9          hourot
 10      ) VALUES (
 11          :new.empno,
 12          :new.projno,
 13          :new.hoursworked - 100
 14      );
 15
 16  END;
 17  /

Trigger created.

Testing (overtime hours came during update of values in existing row):

SQL> insert into emp_proj (empno, projno, hoursworked) values (1, 100, 75);

1 row created.

SQL> select * From emp_proj;

     EMPNO     PROJNO HOURSWORKED
---------- ---------- -----------
         1        100          75

SQL> select * From emp_proj_overtime;

no rows selected

SQL> update emp_proj set hoursworked = 102 where empno = 1 and projno = 100;

1 row updated.

SQL> select * From emp_proj;

     EMPNO     PROJNO HOURSWORKED
---------- ---------- -----------
         1        100         102

SQL> select * From emp_proj_overtime;

     EMPNO     PROJNO     HOUROT
---------- ---------- ----------
         1        100          2

Some more testing (overtime hours inserted initially):

SQL> insert into emp_proj (empno, projno, hoursworked) values (2, 995, 113);

1 row created.

SQL> select * From emp_proj;

     EMPNO     PROJNO HOURSWORKED
---------- ---------- -----------
         1        100         102
         2        995         113

SQL> select * From emp_proj_overtime;

     EMPNO     PROJNO     HOUROT
---------- ---------- ----------
         1        100          2
         2        995         13

SQL>