RDBMS_PRACT_7_A

Assignment 7 Trigger : SET A

supplier4=# select * from item;

 ino | iname | quantity

-----+------+---------

 101 | ABC   |      100

 102 | DEF   |      150

 103 | GHI   |       50

 104 | JKL   |      250

 105 | MNO   |      200

(5 rows)

supplier4=# select * from supplier;

 sno |   sname    | address  | city

-----+-----------+---------+-----

   1 | Shiva      | Kothrud  | Pune

   2 | Darshan    | Bibewadi | Pune

   3 | Vidhyadhar | Katraj   | Pune

(3 rows)

supplier4=# select * from itemsupplier

supplier4-# ;

 ino | sno |   rate   

-----+----+----------

 101 |   1 | $1,000.00

 101 |   1 | $2,000.00

 102 |   1 | $1,500.00

 103 |   2 | $2,500.00

 104 |   2 |   $500.00

 105 |   3 | $1,500.00

(6 rows)

--------------------------------------------------------------------------

1)

create or replace function a1() returns trigger as '

begin

if(new.rate-old.rate)>''$2000'' then

raise exception''Difference Should B Less Then $2000 % '',new;

end if;return new;

end '

language 'plpgsql';

CREATE FUNCTION

supplier4=# create trigger t3 before insert or update on itemsupplier for each row execute procedure a1();

CREATE TRIGGER

supplier4=# update itemsupplier set rate='$6000' where ino=104;

ERROR:  Difference Should B Less Then $2000 (104,2,"$6,000.00")

supplier4=# update itemsupplier set rate='$2000' where ino=105;

UPDATE 1

------------------------------------------------------------------------------

2)

supplier4=# create or replace function a2() returns trigger as '

begin

if(new.rate =''$0'') then

raise exception''Rate Should Not Be Zero %'',new;

end if;return new;

end '

language 'plpgsql';

CREATE FUNCTION

supplier4=# create trigger T11 before insert or update on itemsupplier for each row execute procedure a2();

CREATE TRIGGER

supplier4=# insert into itemsupplier values(101,1,'$0');

ERROR:  Rate Should Not Be Zero (101,1,$0.00)