SQL trigger DATEPART is not declared

Question

I am trying to create a trigger that prompts an error message if the current time is between 10pm and 6am. This is the code I wrote:

CREATE OR REPLACE TRIGGER horarioModificar 
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
   horaActual NUMBER:= DATEPART(hour, SYSDATETIME());
BEGIN 
    IF  horaActual < 6 OR horaActual > 22 THEN
        raise_application_error(-20250,'No se puede realizar ningún cambio entre las 22:00 y las 6:00');
    END IF;
END;
/

I got an error saying that DATEPART needs to be declared (error code PLS-00201). Does anybody know what's wrong with my code?

Answer 1

The error message you are getting (PLS-00201) indicates that you are running Oracle.

Here is a new version of the trigger code that would run on Oracle:

create or replace trigger horariomodificar 
before update on employees 
for each row 
begin 
    if not extract(hour from systimestamp) between 6 and 22 
        then raise_application_error(
            -20250,
            'no se puede realizar ningún cambio entre las 22:00 y las 6:00'
        ); 
    end if; 
end; 
/

Notable points:

• datepart() does not exist in Oracle (this is a SQL Server function); you can use extract(hour from systimestamp) to get the current hour

• you don't really need to use a variable, so I removed that

• this code actually prevents changes between 23h and 6h, not between 22h and 6h; otherwise, you want: if not extract(hour from systimestamp) not between 6 and and 21