Assignment 5 : Triggers.
A trigger defines a function which occurs before or after, an action on a table. A trigger is implemented through PL/pgSQL, C or any other functional language that PostgreSQL can use to define a function.
A trigger is a PL/pgSQL block that is associated with a table, stored in a database and executed in response to a specific data manipulation event. Triggers can be executed or fired in response to the following events.
a. A row is inserted into table
b. A row in a table is updated.
c. A row in a table is deleted.
Syntax for defining a database trigger :-
Create Trigger trigger–name
{ Before | After} {event [ or event …]} ON table–name
for each { Row | statement}
execute procedure function name( arguments) ;
A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger.
Special variables created automatically, on call to a trigger function, are as follows :
NEW :-Holds the new database row for INSERT/UPDATE operations in row-level triggers.
OLD:- Holds the old database row for UPDATE/DELETE operations in row-level triggers.
Assignment 5 : Triggers.
SET A
Using Item_supplier Database
Item(itemno integer, Itemname varchar(20),quantity integer) Supplier(SupplierNo,Supplier name,address,city) Item_sup(item_no integer ,Supplier_no integer,Rate Money)
Item and supplier are related with many to many relationship .Rate is descriptive attribute.
a. Write a trigger before update on rate field, If the difference in the old rate and new rate to be entered is more than Rs 2000/ . Raise an exception and display the corresponding message
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
b. Write a trigger before insert or update on rate field, If the rate to be entered is zero then. Raise an exception and display the message “Zero rate not allowed”.
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)
SET B
Student –Marks database
Student (rollno integer,name varchar(30),address varchar(50),class varchar(10)) Subject(Scode varchar(10),subject name varchar(20))
student and subject are related with M-M relationship with attributes marks scored.
a) Write a trigger before deleting a student record from the student table. Raise a notice and display the message “student record is being deleted”
create table student(rno int primary key,name varchar(20),city varchar(20),class varchar(10));
CREATE TABLE
create table subject(scode varchar(10) primary key, sname varchar(20));
CREATE TABLE
create table stud_sub(rno int references student(rno), scode varchar(20) references subject(scode), marks int);
CREATE TABLE
insert into student values(1,'abcd','pune','fybcs');
insert into subject values('CS101','c prog');
insert into stud_sub values(1,'CS101',50);
create function ass5C_1() returns trigger as $$
begin
if new.marks<0 then
raise exception 'Invalid Marks';
end if;
return new;
end;
$$ language plpgsql;
create trigger ass5C_1 before insert or update on stud_sub for each row execute procedure ass5C_1();
CREATE TRIGGER
create function ass5c_2() returns trigger as $$
begin
if old.city='pune' then
raise exception 'Student record can not be deleted';
end if;
return old;
end;
$$ language plpgsql;
create trigger ass5c_2 before delete on student for each row execute procedure ass5c_2();
b) Write a trigger to ensure that the marks entered for a student, with respect to a subject is never < 10 and greater than 100.
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)
SET C
News paper database
Newspaper(name varchar(20), language varchar(20),Publisher varchar(20),cost money) Cities(pincode varchar(6), city varchar(20), state varchar(20))
Newspaper & Cities M to M relationship with descriptive attribute daily_required integer
a) Calculate the length of pincode. Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. If it is more or less then it display the appropriate message.
postgres=# create database paper4
postgres-# ;
CREATE DATABASE
postgres=# create table newspaper(name varchar(20) primary key,language varchar(20),publisher varchar(20),cost money);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "newspaper_pkey" for table "newspaper"
CREATE TABLE
postgres=# create table cities(pincode varchar(6) primary key,city varchar(20),state varchar(20));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
CREATE TABLE
postgres=# create table n_c(name varchar(20) references newspaper(name) on delete cascade,pincode varchar(6) references cities(pincode) on delete cascade,daily_req int);
CREATE TABLE
postgres=# insert into newspaper values('Sakal','Marathi','Sakal','$2');
INSERT 0 1
postgres=# insert into newspaper values('Today','Marathi','Sakal','$2');
INSERT 0 1
postgres=# insert into newspaper values('XYZ','English','Abc','$5');
INSERT 0 1
postgres=# insert into cities values('411002','Pune','Maharashtra');
INSERT 0 1
postgres=# insert into cities values('422020','Nashik','Maharashtra');
INSERT 0 1
postgres=# insert into cities values('455020','XYZ','Kolkata');
INSERT 0 1
postgres=# insert into n_c values('Sakal','411002',5000);
INSERT 0 1
postgres=# insert into n_c values('Today','422020',500);
INSERT 0 1
postgres=# insert into n_c values('XYZ','455020',500);
INSERT 0 1
postgres=# select * from newspaper;
name | language | publisher | cost
-------+----------+-----------+-------
Sakal | Marathi | Sakal | $2.00
Today | Marathi | Sakal | $2.00
XYZ | English | Abc | $5.00
(3 rows)
postgres=# select * from cities;
pincode | city | state
---------+--------+-------------
411002 | Pune | Maharashtra
422020 | Nashik | Maharashtra
455020 | XYZ | Kolkata
(3 rows)
postgres=# select * from n_c;
name | pincode | daily_req
-------+---------+-----------
Sakal | 411002 | 5000
Today | 422020 | 500
XYZ | 455020 | 500
(3 rows)
create or replace function t11()returns trigger as '
begin
if length(new.pincode)<6 length="" new.pincode="" or="">6 then
raise exception ''Incorrect Pincode %'',new;
end if;
return new;
end '
language 'plpgsql';
CREATE FUNCTION
create trigger t1 before insert on cities for each row execute procedure t11();
CREATE TRIGGER
paper4=# insert into cities values('41101','Solapur','Maharashtra');
ERROR: Incorrect Pincode (41101,Solapur,Maharashtra)
paper4=# insert into cities values('411012','Solapur','Maharashtra');
INSERT 0 1
paper4=# insert into cities values('4110121','Solapur','Maharashtra');
ERROR: value too long for type character varying(6)
b) Write a trigger which will prevent deleting cities from Maharatra state.
2)
paper4=# create or replace function t12()returns trigger as '
begin
if old.state=''Maharashtra'' then
raise exception ''Cant Delete %'',old;
end if;
return old;
end '
language 'plpgsql';
CREATE FUNCTION
^
paper4=# create trigger t2 before delete on cities for each row execute procedure t12();
CREATE TRIGGER
paper4=# select * from cities;
pincode | city | state
---------+---------+-------------
411002 | Pune | Maharashtra
422020 | Nashik | Maharashtra
455020 | XYZ | Kolkata
411012 | Solapur | Maharashtra
(4 rows)
paper4=# delete from cities where state='Maharashtra';
ERROR: Cant Delete (411002,Pune,Maharashtra)
paper4=# delete from cities where state='Kolkata';
DELETE 1
SET D
Using Railway Reservation Database
a) create a trigger to validate train arrival time must be less than train departure time.
railway4=# create or replace function t1()returns trigger as '
begin
if new.a_time
raise exception ''correct time %'',new;
end if;
return new;
end '
language 'plpgsql';
CREATE FUNCTION
create trigger t2 before insert or update on train for each row execute procedure t1();
CREATE TRIGGER
railway4=# insert into train values(105,'Kanyakumari express','20:00:00','18:00:00','aaa','xxx',12,60);
INSERT 0 1
railway4=# insert into train values(106,'Kanyakumari express','12:00:00','18:00:00','aaa','xxx',12,60);
ERROR: arrival time 18:00:00 should be less than departure time
b) Write a trigger which will be activated before changing the status field in the ticket table and print a message to the user.
railway4=# create or replace function f1() returns trigger as'
begin
if old.status!=new.status then
raise exception ''Cannot change status %'',old;
end if;
return old;
end
'
language 'plpgsql';
CREATE FUNCTION
railway4=# create trigger t before insert or update on ticket for each row execute procedure f1();
CREATE TRIGGER
railway4=# update ticket set status='c' where tno=104;
ERROR: Cannot change status (104,44,5,12,5,2010-05-03,4000.00,w,)
railway4=# update ticket set status='w' where tno=104;
UPDATE 1
SET E
Using Bus Transportation database
a) Define a trigger after insert or update the record of driver if the age is between 18 and 50 give the message “valid entry” otherwise give appropriate message.
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
b) Define a trigger after delete the record of bus having capacity < 10. Display the message accordingly
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)
Movie – Actor Database
Consider the following database
Movie (m_name varchar (25), release_year integer, budget money)
Actor (a_namevarchar(30), role varchar(30), charges money, a_address varchar(30) )
Movie and Actor are related with many to many relationship. Create the above database in PostGreSQL and insert sufficient records.
a. Write a trigger which will be executed whenever an actor is deleted from the actor table, display appropriate message.
begin
begin
c. Write a trigger which will be executed whenever insertion is made to the movie table. If the budget is less than 1,00,000 do not allow the insertion. Give appropriate message.
No comments:
Post a Comment