RDBMS_PRACT_7_B

Assignment 7 Trigger : SET B

student4=# \d

          List of relations

 Schema |  Name   | Type  |  Owner  

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

 public | s1      | view  | postgres

 public | s2      | view  | postgres

 public | s2a     | view  | postgres

 public | st      | table | postgres

 public | student | table | postgres

 public | teacher | table | postgres

(6 rows)

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

1)

student4=# create or replace function f5() returns trigger as '

student4'# begin

student4'# raise exception''Student Record Is Deleted %'',old;

student4'# return old;

student4'# end '

student4-# language 'plpgsql';

CREATE FUNCTION

student4=# create trigger t1 before delete on student for each row execute procedure f5();

CREATE TRIGGER

student4=# select * from student;

 sno |  sname  | sclass | saddrs

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

 101 | Pratik  | FY     | Pune

 102 | Harshal | FY     | Pune

 103 | Suresh  | SY     | Pune

 104 | Ramesh  | SY     | Mumbai

 105 | Jayesh  | SY     | Mumbai

 106 | Rakesh  | SY     | Mumbai

 107 | Vikesh  | TY     | Pune

 108 | Mikesh  | TY     | Pune

 109 | Mitesh  | TY     | Pune

 110 | Priyesh | TY     | Pune

(10 rows)

student4=# delete from student;

ERROR:  Student Record Is Deleted (101,Pratik,FY,Pune)

student4=#

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

2)

student4=# create or replace function f6() returns trigger as '

begin

if new.marks<10 new.marks="" or="">100 then

raise exception'' Invalid %'',new;

end if;

return new;

end '

language 'plpgsql';

CREATE FUNCTION

student4=# create trigger t4 before insert or update on ss for each row execute procedure f6();

CREATE TRIGGER

student4=# insert into ss values(105,4,91);

INSERT 0 1

student4=# insert into ss values(105,4,09);

ERROR:   Invalid (105,4,9)

student4=#