CODEWITHSUNDEEP
plsql
Apprentice R
Apprentice R

Reputation: 11

PL/SQL not functioning

Im currently trying to implement a row trigger that fires when the new Employee number inserted into the table is not continuous.

"Continuous" in a relationship to the Employee number means the first record inserted will have the Employee number 1, the second record will have the employee number 2, and each next position must have a number greater by one that a number of the previous position.

I have successfully created the trigger, however when I inserted a new record that have an Employee number that is not continuous, my trigger is not fired.

Im unsure where I went wrong and hope I can get some explanation and corrections on my code. 

CREATE OR REPLACE TRIGGER CONTENUM

AFTER INSERT ON TRKEMPLOYEE
FOR EACH ROW 

DECLARE 
continuous_value EXCEPTION;
PRAGMA exception_init(continuous_value, -20111);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN 

IF (:NEW.E# > :OLD.E# + 1) THEN 
    RAISE_APPLICATION_ERROR (-20111,'The value of  Employee number must be continuous');

END IF;

END CONTENUM;
/

Here is the format of my sample TRKEMPLOYEE table 

 CREATE TABLE TRKEMPLOYEE(
    E#              NUMBER(12)      NOT NULL,
    NAME            VARCHAR(50)     NOT NULL,
    DOB             DATE                    ,
    ADDRESS         VARCHAR(300)    NOT NULL,
    HIREDATE        DATE            NOT NULL,
    CONSTRAINT TRKEMPLOYEE_PKEY PRIMARY KEY(E#) );

Here is my insert statement. Currently in my table TRKEMPLOYEE there is only 15 rows thus with my insert statement, the trigger should fire but it is not happening.

INSERT INTO TRKEMPLOYEE VALUES( 17, 'David', NULL, 'GB',sysdate );

Thank you.

Upvotes: 0

Views: 51

Answers (1)

Ergi Nushi
Ergi Nushi

Reputation: 863

First of all you are checking AFTER INSERT ON TRKEMPLOYEE which will be executed after the row is inserted.

Secondly, you cannot check :OLD.E# since you are not updating and you are not using a old value.

Also you should drop the trigger at all and use SEQUENCES and let Oracle take care of the auto-increment values every time you add a new employee.

If you want to continue with your current logic, fixes that can be applied:

  • Change AFTER INSERT ON TRKEMPLOYEE to BEFORE INSERT ON TRKEMPLOYEE

  • Logic should be changed as below:

    CREATE OR REPLACE TRIGGER contenum BEFORE
    INSERT ON trkemployee
    FOR EACH ROW
DECLARE
    continuous_value EXCEPTION;
    PRAGMA exception_init ( continuous_value, -20111 );
    PRAGMA autonomous_transaction;
    max_e# INTEGER;
BEGIN
    SELECT
        nvl(MAX(e#), 0)
    INTO max_e#
    FROM
        trkemployee;

    IF ( :new.e# > max_e# + 1 ) THEN
        raise_application_error(-20111, 'The value of  Employee number must be continuous');
    END IF;
END contenum;
/

I do not recommend this solution because it will start to become slower as your table starts to grow.

Upvotes: 1

Related Questions