renganaickam
Programmer
hello everybody!!
i need help on pL/pgsql precisely about procedural language...the database(on which i'm working at my placement : a DVD shop in paris ) has two main tables called bases and codes..
the table bases has main 5 columns(i don't mention the other columns here):
ind (serial) --index unique
codebarre varchar(30) -- produit id or bar code
media varchar(20) -- type of media : VHS,DVD,CD
titre varchar(50) --title
nom -varchar(30) --name
and the table codes has 8 columns :
ind (serial) -- index unique
code_principal varchar(30)
code_secondaire1 varchar(30) default NULL
code_secondaire2 varchar(30) default NULL
code_secondaire3 varchar(30)default NULL
code_secondaire4 varchar(30) default NULL
code_secondaire5 varchar((30)default NULL
code_secondaire6 varchar(30) default NULL
code_secondaire7 varchar(30) default NULL
the table bases contains all the information about the products(like codebarre,media,titre,nom,etc.. sold by the DVD shop where i'm working...For your information a same product might possess few different bar code or product id..hence, the use of the table codes to stocke this codes..if a product has at least 2 bar code,we insert the column codebarre in the table bases and code_principal in the
table codes with a same code bar chosen arbitraryly and the second code bar inserted into code_secodaire1 et so on..
i want that every time we insert a product into bases all these aboves steps are launched automatically by pgsql server...
to do so,i've created a trigger on function gempak1bis() before insert on table bases..
i use two functions called check3(varchar) returns boolean
and renvoi_null(varchar) returns an integer
Both of 2 my functions work perfectly but not my trigger..my trigger, it works but it a insert the the products with an secondary bar code in the table bases besides updating the tables codes..instead of just updating the table codes with corresponding code_secondaire...
Can anyone please tell me how could i get avoid this insert???
please anyone, who has enough free time..be kind enough to look at my trigger function below:
CREATE FUNCTION gempak1bis() RETURNS OPAQUE AS'
DECLARE
test boolean;
test1 integer;
codebarre1 varchar(30);
codebarre2 varchar(30);
media1 varchar(20);
titre1 varchar(70);
nom1 varchar(40);
BEGIN
test = check3(NEW.codebarre);
/*check3 is a function which returns a boolean, true if the barre code entered is already present in the table codes*/
SELECT INTO codebarre1 codebarre FROM base2 WHERE (codebarre = NEW.codebarre
AND media = NEW.media AND titre = NEW.titre AND nom = NEW.nom);
SELECT INTO media1 media FROM base2 WHERE (media = NEW.media AND titre=
NEW.titre AND nom=NEW.nom AND codebarre != NEW.codebarre);
SELECT INTO titre1 titre FROM base2 WHERE (media = NEW.media AND titre =
NEW.titre AND nom=NEW.nom AND codebarre != NEW.codebarre);
SELECT INTO nom1 nom FROM base2 WHERE (nom = NEW.nom AND media=NEW.media AND
titre = NEW.titre AND codebarre != NEW.codebarre);
SELECT INTO codebarre2 codebarre FROM base2 WHERE (codebarre !=
NEW.codebarre AND media= NEW.media AND titre = NEW.titre AND nom = NEW.nom);
test1 = renvoi_null(codebarre2);
/* renvoi_null is a function which return an integer which correspond to the first code_secondaire* ( * =1,2,3,4,5,6,7) NULL */
/* start of the main part of the function */
IF (codebarre1 IS NOT NULL) THEN
RAISE EXCEPTION ''Produit est déjà présent dans la table base2!!!!!'';
/* this indicates that the product already exists in table bases */
ELSE IF( (media1 IS NOT NULL)AND (titre1 IS NOT NULL) AND (nom1 IS NOT NULL))
THEN
IF( test = TRUE) THEN
RAISE EXCEPTION ''Le codebarre du produit existe déjà dans la table codes'';
/* this message indicates that the secondary code of the product entered existed already in the table codes, so we don't do insert into bases */
ELSE IF (test1 = 1) THEN
UPDATE codes SET code_secondaire1 = NEW.codebarre WHERE code_principal= codebarre2;
ELSE IF (test1 = 2) THEN
UPDATE codes SET code_secondaire2 = NEW.codebarre WHERE
code_principal= codebarre2;
ELSE IF (test1 =3) THEN
UPDATE codes SET code_secondaire3 =NEW.codebarre WHERE
code_principal= codebarre2;
ELSE IF (test1 =4) THEN
UPDATE codes SET code_secondaire4 =NEW.codebarre WHERE
code_principal= codebarre2;
ELSE IF (test1 = 5) THEN
UPDATE codes SET code_secondaire5 =NEW.codebarre
WHERE code_principal= codebarre2;
ELSE IF (test1 = 6) THEN
UPDATE codes SET code_secondaire6 =NEW.codebarre WHERE
code_principal= codebarre2;
ELSE IF (test = 7) THEN
UPDATE codes SET code_secondaire7=NEW.codebarre
WHERE code_principal= codebarre2;
ELSE IF(test = 0) THEN
INSERT INTO codes(code_principal,code_secondaire1) values(codebarre2,NEW.codebarre);END IF; END IF; END IF;END IF;END IF;END IF;END IF;END IF;
END IF;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
/* end of the trigger function */