CODEWITHSUNDEEP
triggersoracle11gsqlplus
Mohammed Ali Kachbouri
Mohammed Ali Kachbouri

Reputation: 43

Oracle Trigger, initialize a variable

when i'm executing this script in SQLPLUS, i had an error that told me to declare "ENSI" (line 11) and to declare "ENIT" (line 12),but i just need to initialize my variable to those String any idea why?

create or replace trigger ETUDIANT_TRIGGUER
Before update on ETUDIANT
for each row 
declare
  c number;
  id_edu ETUDIANT.id_etu%TYPE := :new.id_etu;
  NOM ETUDIANT.NOM%TYPE := :new.NOM;
  adresse ETUDIANT.adresse%TYPE := :new.adresse;
  cursus ETUDIANT.cursus%TYPE := :new.cursus;
  UNIVERSITE ETUDIANT.UNIVERSITE%TYPE := :new.UNIVERSITE;
  SITE_ENSI ETUDIANT.UNIVERSITE%TYPE := "ENSI";
  SITE_ENIT ETUDIANT.UNIVERSITE%TYPE := "ENIT";
begin
if :new.ID_ETU is null then
raise_application_error(-20001,'ID_ETU est null');    
end if;
select count(*) into c from ETUDIANT_ensi@site_ensi_link where ID_ETU= :new.ID_ETU;
if c > 0 then
raise_application_error(-20002, 'ID_ETU existe déjà dans le site _ensi');
end if;
select count(*) into c from ETUDIANT_enit@site_enit_link where ID_ETU= :new.ID_ETU;
if c > 0 then
raise_application_error(-20002, 'ID_ETU existe déjà dans le site _enit');
end if;  
if UPPER(UNIVERSITE)=SITE_ENSI then
insert into ETUDIANT_ensi@site_ensi_link values(id_edu,NOM,adresse,cursus,NB_EMPRUNTS,UNIVERSITE);
end if;    
if UPPER(UNIVERSITE)=SITE_ENIT then
insert into ETUDIANT_enit@site_enit_link values(id_edu,NOM,adresse,cursus,NB_EMPRUNTS,UNIVERSITE);
end if;  
end;
/

Upvotes: 0

Views: 2638

Answers (1)

Mureinik
Mureinik

Reputation: 311393

String literals in [pl]SQL are enclosed by single quotes, not double quotes. So, you should replace the following two lines:

SITE_ENSI ETUDIANT.UNIVERSITE%TYPE := "ENSI";
SITE_ENIT ETUDIANT.UNIVERSITE%TYPE := "ENIT";

With these two:

SITE_ENSI ETUDIANT.UNIVERSITE%TYPE := 'ENSI';
SITE_ENIT ETUDIANT.UNIVERSITE%TYPE := 'ENIT';

Upvotes: 3

Related Questions