create trigger, error : PLS-00302: component 'DATETIME' must be declared ! pls help

Check this out:

create or replace trigger CHECK_CONFLICT_SCR
before insert on SCREENING_TIME
for each row
DECLARE
fromD Date;
toD Date;
duree number;
t Timestamp;
h number;
m number;

BEGIN
select BeginValidity,  EndValidity into fromD,toD
from  CINEMA_FILM
where CINEMA_FILM.FilmID = :new.FilmID;

select Duration, CAST (DateTime as Timestamp) into duree,t  
from FILM, OCCURENCE O
where FILM.FilmID = :new.FilmID and O.OccurenceID= :new.OccurenceID;

declare cursor temp
is
select CAST (V.DateTime as Timestamp) DateTime, F.Duration Duration from VIEW_FILM_SCHEDULE V, FILM F where F.FilmID = V.FilmID and V.RoomID = :new.RoomID and V.DayOfWeek = :new.DayOfWeek and ((V.BeginValidity - fromD)>=0 or (V.EndValidity-toD)<=0);
begin
FOR scr in temp
LOOP
m:=CAST((substr((t-scr.DateTime),instr((t-scr.DateTime),' ')+4,2)) as number);
h:= CAST(substr((t-scr.DateTime),instr((t-scr.DateTime),' ')+1,2) as number);
if ( h < 0 ) then
begin
   if (-h*60-m< duree) then
        raise_application_error(-20240,'Conflict screening time');
  end if;        
end;
elsif ((h*60+m)<scr.Duration)then
      raise_application_error(-20240,'Conflict screening time');
end if;      
END LOOP;
END;
END;

Metanil

seems to be the same correction as my suggestion? only without any explanation :-)

hi, angelIII, i do as u suggest and the trigger created successfully.
But i try to insert to SCREENING_TIME and encounter another error  :

ORA-06512: at "QA.C_CONFLICT_SCR", line 10
ORA-04088: error during execution of trigger 'QA.C_CONFLICT_SCR'

U have any suggest? Thanks!

QA.C_CONFLICT_SCR or QA.CHECK_CONFLICT_SCR ??

Please give some detail how you execute (values while inserting), table structure of SCREENING_TIME etc..

Metanil

check dba_errors if there is some compilation errors.

select * from dba_erros where owner='QA' and type='TRIGGER'

Metanil

These are tables and view i use in the trigger :

create table FILM  (
   FilmID               INTEGER                              not null,
   Cast                 VARCHAR(100),
   Genre                VARCHAR(50),
   Duration             INTEGER,
   Distributor          VARCHAR(20),
   Country              VARCHAR(20),
   Synopsis             VARCHAR2(500),
   ParticipantID        INTEGER,
   BeginValidity        DATE,
   EndValidity          DATE,
   Director            VARCHAR2(30),
   Language            VARCHAR2(30),
   Music            VARCHAR2(30),
   ReleaseDay            DATE,
   constraint PK_FILM primary key (FilmID),
 
);

create table CINEMA_FILM  (
   CinemaID             INTEGER                              not null,
   FilmID               INTEGER                              not null,  
   BeginValidity        DATE                             not null,
   EndValidity          DATE,
   constraint PK_CINEMA_FILM primary key (CinemaID, FilmID),
   constraint FK_CINEMA_F_CINEMAFIL_CINEMA foreign key (CinemaID)
         references CINEMA (CinemaID),
   constraint FK_CINEMA_F_CINEMAFIL_FILM foreign key (FilmID)
         references FILM (FilmID)
);
create table OCCURENCE  (
   OccurenceID          INTEGER                              not null,
   EventID              INTEGER,
   ParticipantID        INTEGER,
   PlaceID              INTEGER,
   Datetime             DATE,
   constraint PK_OCCURENCE primary key (OccurenceID),
   constraint FK_OCCURENC_OCCURENCE_EVENT foreign key (EventID)
         references EVENT (EventID),
   constraint FK_OCCURENC_OCCURENCE_PLACE foreign key (PlaceID)
         references PLACE (PlaceID),
   constraint FK_OCCURENC_OCCURENCE_PARTICIP foreign key (ParticipantID)
         references PARTICIPANT (ParticipantID)
);

create table SCREENING_TIME  (
   ScreeningTimeID      INTEGER                              not null,
   RoomID               INTEGER,
   CinemaID             INTEGER,
   FilmID               INTEGER,
   SpecialDate          DATE,
   OccurenceID          INTEGER,
   DayOfWeek            SMALLINT,
   constraint PK_SCREENING_TIME primary key (ScreeningTimeID),
   constraint FK_SCREENIN_SCREENING_ROOM foreign key (RoomID)
         references ROOM (RoomID),
   constraint FK_SCREENIN_SCREENING_CINEMA_F foreign key (CinemaID, FilmID)
         references CINEMA_FILM (CinemaID, FilmID),
   constraint FK_SCREENIN_SCREENING_OCCURENC foreign key (OccurenceID)
         references OCCURENCE (OccurenceID)
);

CREATE VIEW VIEW_FILM_SCHEDULE AS
SELECT CF.CinemaID, CF.FilmID, ParticipantName as FilmName, CF.BeginValidity, CF.EndValidity, Datetime, DayOfWeek, SpecialDate,SCR.RoomID, PlaceName as RoomName
FROM CINEMA_FILM CF, SCREENING_TIME SCR, OCCURENCE O, PLACE P, ROOM R, PARTICIPANT P, FILM F
WHERE CF.FilmID = SCR.FilmID and CF.CinemaID = SCR.CinemaID and SCR.OccurenceID = O.OccurenceID and SCR.RoomID = R.RoomID and P.PlaceID = R.PlaceID and P.ParticipantID = F.ParticipantID and SCR.FilmID  = F.FilmID;

As i insert, there's no row in CINEMA_FILM , and in VIEW_FILM_SCHEDULE.

Always check if data is available when selecting. try like this:

select count(*) into variable1 from dual where exists ( select BeginValidity,  EndValidity into fromD,toD
from  CINEMA_FILM
where CINEMA_FILM.FilmID = :new.FilmID);

if variable1 = 0 then
--- exit trigger with raising error
else
--do your task
end if;


Metanil

If you tell us what line 10 is that would narrow it down.
You can catch the NO_DATA_FOUND exception like ...

begin
   select ...
exception
  when NO_DATA_FOUND then null;

end;