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?
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