Assignment
7 Trigger : SET C
[NRC@localhost
~]$ su - postgres;
Password:
-bash-4.1$ psql
psql (8.4.5)
Type
"help" for help.
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);
CREATE TABLE
postgres=# select
* from newspaper;
name | language | publisher | cost
------+----------+-----------+------
(0 rows)
postgres=# select
* from cities;
pincode | city | state
---------+------+-------
(0 rows)
postgres=# select
* from n_c;
name | pincode
------+---------
(0 rows)
postgres=# drop
table n_c;
DROP 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=# select
* from n_c;
name | pincode | daily_req
------+---------+-----------
(0 rows)
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)
postgres=#
postgres=# \q
-bash-4.1$ psql
psql (8.4.5)
Type
"help" for help.
postgres=#
---------------------------------------------------------------------------------------------
1)
create or replace
function t11()returns trigger as '
begin
if
length(new.pincode)<6 length="" new.pincode="" or="">6 then6>
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)
paper4=#
-----------------------------------------------------------------------------------------------
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
ex of cursor not found
ReplyDelete