Cannot insert data in trigger

Question

It give me error example image at below:

Trigger code:

CREATE OR REPLACE TRIGGER InsertNewStaffs
BEFORE INSERT ON Staffs
FOR EACH ROW
ENABLE
DECLARE
v_user varchar(255);
v_date varchar(255);
v_Staffs_ID Staffs.Staffs_ID%TYPE;
v_Staffs_Name Staffs.Staffs_Name%TYPE;  
v_Staffs_Contact_Number Staffs.Staffs_Contact_Number%TYPE;
v_Staffs_Email Staffs.Staffs_Email%TYPE;
v_Orders_ID Staffs.Orders_ID%TYPE;
v_count INTEGER;
BEGIN
SELECT count(*) INTO v_count FROM Staffs
WHERE Staffs_ID = v_Staffs_ID OR
Staffs_Name = v_Staffs_Name OR
Staffs_Contact_Number = v_Staffs_Contact_Number OR
Staffs_Email = v_Staffs_Email;

IF v_count > 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'Oops, some data is already exists. Please try again...');
DBMS_OUTPUT.PUT_LINE('Oops, some data is already exists. Please try again...');

SELECT user, TO_CHAR(sysdate, 'DD/MON/YYYY HH24:MI:SS') INTO v_user, v_date FROM dual;

ELSE
INSERT INTO Staffs(Staffs_ID, Staffs_Name, Staffs_Contact_Number, Staffs_Email, Orders_ID)
VALUES(v_Staffs_ID, v_Staffs_Name, v_Staffs_Contact_Number, v_Staffs_Email, v_Orders_ID);

DBMS_OUTPUT.PUT_LINE('One Row Inserted By ' || v_user || CHR(10));
DBMS_OUTPUT.PUT_LINE('Inserted data at ' || v_date);

INSERT INTO monitorInsertStaffs(user_name, entry_date, operation)
VALUES(v_user, v_date, 'Insert');
END IF;
END;
/

My Table:

CREATE TABLE Staffs(
     Staffs_ID char(20) NOT NULL,
     Staffs_Name varchar(255) NOT NULL,
     Staffs_Contact_Number varchar(50) NOT NULL,
     Staffs_Email varchar(255) NOT NULL,
     Orders_ID char(20),
     PRIMARY KEY (Staffs_ID),
     FOREIGN KEY (Orders_ID) REFERENCES Orders(Orders_ID)
);

CREATE TABLE Orders(
     Orders_ID char(20) NOT NULL,
     Order_Date DATE NOT NULL,
     Order_Status varchar(255) NOT NULL,
     Order_Quantity int NOT NULL,
     Order_TotalAmount NUMERIC(10,2) NOT NULL,
     Order_TotalPrice NUMERIC(10,2) NOT NULL,
     PRIMARY KEY (Orders_ID),
     Pets_Products_ID char(20),
     CustomerID char(20),
     FOREIGN KEY (Pets_Products_ID) REFERENCES Pets_Products(Pets_Products_ID),
     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

I try to insert data and if the data has existed it will show RAISE_APPLICATION_ERROR(-20000, 'Oops, some data is already exists. Please try again...'); but it didn't show the message and also cannot insert data when no exists the data.

I don't know where is error code that I find.

Answer 1

The whole concept is just wrong.

• you've based trigger on a table into which you're just inserting a row (staffs)
• then you're selecting from the same table (it'll raise the mutating table error if you try to insert more than a single row)
• the where clause uses local variables that have no values
• insert into staffs cause the same trigger to fire over and over again, until Oracle concludes that that's enough and raises the error

Answer 2

Don't use a trigger. Use UNIQUE constraints:

CREATE TABLE Staffs(
     Staffs_ID char(20) NOT NULL,
     Staffs_Name varchar(255) NOT NULL,
     Staffs_Contact_Number varchar(50) NOT NULL,
     Staffs_Email varchar(255) NOT NULL,
     Orders_ID char(20),
     PRIMARY KEY (Staffs_ID),
     UNIQUE (Staffs_Name),
     UNIQUE (Staffs_Contact_Number),
     UNIQUE (Staffs_Email),
     FOREIGN KEY (Orders_ID) REFERENCES Orders(Orders_ID)
);

(However, you should also consider whether your business requirements make sense or if you can have multiple staff members called Jane Smith or if you can have two staff members who share an office with the same telephone number?)

If you want to use a logging table then use an autonomous transaction to just insert into that table:

CREATE OR REPLACE TRIGGER InsertNewStaffs
  BEFORE INSERT ON Staffs
  FOR EACH ROW
  ENABLE
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO monitorInsertStaffs(
    user_name, entry_date, operation
  ) VALUES(
    :NEW.Staffs_ID, SYSDATE, 'Insert'
  );
  
  COMMIT;
END;
/

db<>fiddle here