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 then18>
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)
No comments:
Post a Comment