1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| CREATE OR REPLACE TRIGGER trigger_champs BEFORE UPDATE OR INSERT ON PERSONNES
FOR EACH ROW
DECLARE
compt INTEGER;
valeur VARCHAR(10);
foto
varchar(10):='ok';
pic BLOB;
BEGIN
if (:new.diffphoto = '1') then valeur:='O'
;
else valeur:='N';
end if;
pic := photo(:new.id_personne);
compt:=mySqlrowcount(substr( :new.id_personne,1,(length(
:new.id_personne)-12)));
INSERT INTO asynchrone@synchrone (diffphoto ,date_maj,id_personne) VALUES (valeur ,NVL(:new.date_maj,
NVL(:new.dat_creation, '01/01/1999')),substr(:new.id_personne,1,(length(:new.id_personne)-12)) );
EXCEPTION WHEN DUP_VAL_ON_INDEX
THEN
UPDATE
asynchrone@synchrone SET diffphoto = valeur ,date_maj= NVL( :new.date_maj, NVL(:new.dat_creation, '01/01/1999'))
WHERE id_personne= substr( :new.id_personne,1,(length( :new.id_personne )-12)) ;
update asynchrone@synchrone set stockage_photo= pic where
id_personne =substr( :new.id_personne ,1,(length( :new.id_personne )-12));
END ; |
Partager