sqloracle-databasedatedatabase-triggerdatepart

SQL trigger DATEPART is not declared


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?


Solution

  • 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: