RDBMS_PRACT_7_E

Assignment 7 Trigger : SET E

1)

buss4=# select * from driver;

 dno |   dname   | licenes_no |         addr         | d_age | salary

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

   1 | darshan   |        100 | aba                  |    30 |   2000

   2 | vidya     |        200 | abb                  |    20 |   4000

   3 | vidyadhar |        300 | bbb                  |    40 |   5000

(3 rows)

buss4=# create or replace function agep() returns trigger as '

begin

if new.d_age<18 new.d_age="" or="">50 then

raise exception''Invalid Age % '',new;

end if; return new;

end '

language 'plpgsql';

CREATE FUNCTION

buss4=# create trigger t1 after insert or update on driver for each row execute procedure agep();

CREATE TRIGGER

buss4=# insert into driver values(4,'ABC',400,'xyz',15,15000);

ERROR:  Invalid Age (4,ABC,400,"xyz                 ",15,15000)

buss4=# insert into driver values(4,'ABC',400,'xyz',25,15000);

INSERT 0 1

buss4=# select * from driver;

 dno |   dname   | licenes_no |         addr         | d_age | salary

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

   1 | darshan   |        100 | aba                  |    30 |   2000

   2 | vidya     |        200 | abb                  |    20 |   4000

   3 | vidyadhar |        300 | bbb                  |    40 |   5000

   4 | ABC       |        400 | xyz                  |    25 |  15000

(4 rows)

buss4=# update driver set d_age=10 where dno=1;

ERROR:  Invalid Age (1,darshan,100,"aba                 ",10,2000)

buss4=# update driver set d_age=20 where dno=1;

UPDATE 1

buss4=# select * from driver;

 dno |   dname   | licenes_no |         addr         | d_age | salary

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

   2 | vidya     |        200 | abb                  |    20 |   4000

   3 | vidyadhar |        300 | bbb                  |    40 |   5000

   4 | ABC       |        400 | xyz                  |    25 |  15000

   1 | darshan   |        100 | aba                  |    20 |   2000

(4 rows)

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

2)

uss4=# create or replace function c1()returns trigger as '

begin

if old.capacity>10 then

raise exception ''invalid%'',old;

end if;

return old;

end '

language 'plpgsql';

CREATE FUNCTION

buss4=# create trigger t22 after delete on bus for each row execute procedure c1();

CREATE TRIGGER

buss4=# insert into bus values(55,8,'abc',101);

INSERT 0 1

buss4=# select * from bus;

 bno | capacity |   dname    | rno

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

  22 |       30 | shivshakti | 102

  33 |       50 | shiv       | 103

  44 |        9 | abc        | 101

  55 |        8 | abc        | 101

(4 rows)

buss4=# delete from bus where bno=44;

DELETE 1

buss4=# delete from bus where bno=33;

ERROR:  invalid(33,50,shiv,103)