Click here to Skip to main content
16,020,622 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have to create trigger that raise an error if I try to delete from table and the date on the table equal with sysdate.

I tried to delete a row that contain today date 25-nov-18 and the trigger doesnt work , I tried to print the values of :old.datedep and dateres and there are the same. 25-nov-2018 25-nov-2018 Can someone help me? thank you
this is my code:

What I have tried:

SQL
create or replace trigger nonViolation
before insert or delete on reservation
for each row
declare 
error exception;
error2 exception;
dateres date:=sysdate;
begin
if inserting then
if :new.dateDep<dateres then raise error;
end if;
end if;
if deleting then
if (:OLD.datedep=dateres)
then 
raise error2;
end if;
end if;
exception
when error2 then
raise_application_error(-20003,'supression impossible');
when error then 
raise_application_error(-20001,'reservation impossible');
end;
Posted
Updated 25-Nov-18 5:15am
v2
Comments
[no name] 25-Nov-18 11:37am    
Does sysdate also contains time? If yes, I think it will explain your Problem.

Try something like this: if (trunc(:OLD.datedep) = trunc(dateres))

Details see here: TRUNC (date)[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900