CODEWITHSUNDEEP
oracle-databaseplsqldatabase-triggersysdate
Saad
Saad

Reputation: 23

compare date on PLSQL

I have to create trigger that raises an error if I try to delete from table and the date on the table is equal with sysdate.

This is my code:

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;

I tried to delete a row that contain today'sdate 25-nov-18and the trigger doesn't work. I tried to print the values of :old.datedep and dateres and they are the same:

25-nov-2018
25-nov-2018

Can someone help me? thank you

Upvotes: 0

Views: 81

Answers (2)

alvalongo
alvalongo

Reputation: 571

On Oracle columns and variables of type DATE have date component (year-month-day) plus time component (hour in 24 hours clock, minutes, seconds).
How you "see" on screen depends of development tool and local configuration of tool.
some tools if time component is midnight (00:00:00) don't display the time component.
Also your country/session region affects how the tool display dates.
The TRUNC function over a value of type DATE without the second parameter gives a value of type DATE whose component time is midnight (00:00:00).

So to compare 2 values of same day use:

Trunc(:old.datedep)=trunc(dateres)

Upvotes: 0

APC
APC

Reputation: 146319

I suspect you're not allowing for the fact that SYSDATE contains the current time as well as the current date. Perhaps your trigger will produce the desired outcome if you remove the time element like this: 

    ...
    error2 exception;
    dateres date := trunc(sysdate);
begin
    ...

"it doesn't work ,"

As @bobjarvis suggests you may need to strip the time element from datedep (depending on how you populate it):

if TRUNC(:OLD.datedep) = dateres

Try using a complete date format mask such as 'yyyy-mm-dd hh24:mi:ss' and see what you actually have.

Upvotes: 2

Related Questions