ncamaa1

Untitled

Aug 7th, 2017
86
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION trigf() RETURNS trigger AS
  2. $$
  3.  
  4. DECLARE num_of_buys integer;
  5.  
  6. BEGIN
  7.  
  8.     IF (TG_OP = 'INSERT') --checks if insert or update
  9.         THEN
  10.  
  11.         num_of_buys := (select count(*) from buys where new.cid = cid AND new.rno = rno and new.iname = iname);
  12.         IF num_of_buys >= 5 --do if client bought item 5 times, no matter the quantity
  13.             THEN
  14.             IF NOT EXISTS( select * from likes where new.iname = iname AND new.cid = cid)
  15.                 THEN --do if likes table doesn't have item yet
  16.  
  17.                 INSERT INTO likes (cid,iname) values (new.cid, new.iname); -- insert the item and the related cid
  18.                 RETURN new;
  19.             ELSE
  20.                 RAISE NOTICE 'client no. % likes % product ' , new.cid , new.iname;
  21.                 RETURN NULL;
  22.             END IF;
  23.         ELSE IF EXISTS( select * from likes where new.iname = iname AND new.cid = cid )
  24.             THEN
  25.             DELETE from likes where cid = new.cid AND iname = new.iname;
  26.             RETURN new;
  27.  
  28.         ELSE IF (TG_OP = 'UPDATE') ----checks if insert or update
  29.             THEN
  30.             num_of_buys := (select count(*) from buys where new.cid = cid AND new.rno = rno AND new.iname);
  31.             IF num_of_buys >= 5
  32.                 THEN
  33.                 IF NOT EXISTS( select * from likes where old.iname = iname AND old.cid = cid)
  34.                     THEN
  35.                     INSERT INTO likes (cid,iname) values (old.cid, old.iname);
  36.                     RETURN new;
  37.                 ELSE
  38.                     DELETE FROM likes where cid = old.cid AND iname = old.iname;
  39.                     INSERT INTO likes (cid,iname) values (new.cid, new.iname);
  40.                     RETURN new;
  41.                 END IF;
  42.             ELSE
  43.                 IF EXISTS( select * from likes where old.iname = iname AND old.cid = cid )
  44.                     THEN
  45.                     DELETE from likes where cid = old.cid AND iname = old.iname;
  46.                     RETURN new;
  47.                 END IF;
  48.             END IF;
  49.         END IF;
  50.     END IF;
  51. END IF;
  52. END IF;
  53. END;
  54. $$ LANGUAGE 'plpgsql';
  55.  
  56.  
  57. CREATE TRIGGER T BEFORE INSERT OR UPDATE ON buys
  58. FOR EACH ROW EXECUTE PROCEDURE trigf();
RAW Paste Data