trigger to compare a new entry with previous entries from a column in sql-CodePudding

im trying to build an app on apex oracle for a school project, but first i need some functionalities. one of them is a trigger. i want to compare a new entry with previous entries from a column from a table. if the entry is there, will display an error. here is what i got so far:

create or replace TRIGGER client_check 
    BEFORE INSERT
    ON ORDERS
    for each row
    DECLARE
    new_client number;
    BEGIN
    new_client :=0;
    select CLIENT_ID into new_client from ORDERS where CLIENT_ID = :new.CLIENT_ID;
    if new_client (condition to check if its already there)
        Raise_Application_Error(-20000, 'Client already ordered!');
    end if;
    END;

i dont know how to write it. Thank you.

CodePudding user response：

It seems you simply need a count function to count the no of orders -

create or replace TRIGGER client_check 
BEFORE INSERT
ON ORDERS
for each row
DECLARE
new_client number := 0;
BEGIN

select COUNT(CLIENT_ID)
  into new_client
  from ORDERS
 where CLIENT_ID = :new.CLIENT_ID;

if new_client > 0
    Raise_Application_Error(-20000, 'Client already ordered!');
end if;
END;