CODD’s
RULES
Certain
ground rules have to be followed for a DBMS to be relational. Codd’s rules are
designed in such a way that when the database is ready for use it encapsulates
the relational theory to its full potential. These twelve rules are listed below.
As and when these rules come into play their effect on designing a stable
structure can be appreciated.
The
Information Rule
All
Information is explicitly and logically represented in tables as data values.
1.The
Rule Of Guaranteed Access
Every item of data must be logically
addressable with the help of a name, primary key value and column name.
The Systematic treatment of null values
The RDBMS must be able to support null
values (these values are different are different from zeros and spaces) to
present missing or inapplicable information.
The Database Description Rule
A description of database is manipulated
using logical structures with which data was defined the RDBMS. These are
accessible to users with appropriate authority and are stored in the
dictionary.
Comprehensive Data Sub Language
According
to this rule, the system must the following
- Data definition
- View definition
- Data manipulation
- Integrity constraints
- Authorization
- Transaction management operations
2. The
View Updating Rule
All views that are theoretically
updateable must also be updateable by system.
3. The
Insert and Update Rule
A single operand must hold good for all
retrieval, update delete and insert activities. This rule implies that all the
data manipulation commands must be operational on sets of rows having a
relation rather than on a single row.
4. The
Physical Independence
Rule
Application programs must remain unimpaired when any
changes are made in storage representation or access methods.
5. The
Logical Data Independence
Rule
The changes that are should affect the user’s ability
to work with the data. The change can be splitting the table into many more
tables.
6. The
Integrity Independence
Rule
The Integrity constraints should be stored in the
system catalog or in the database as a table.
7. The
Distribution Rule
The System must be able to access or manipulate the
data that is distributed in other systems.
8. The
Non-Subversion Rule
The non-subversion rule states that
different levels of the language cannot subvert or bypass the integrity rules
and constraints. To put it in simple words, if an RDBMS supports a lower level
language then it should not bypass any integrity constraints defined in the
higher level.
First Normal Form
For easier understanding of the first
normal form, consider an example, One employee has different project codes.
Hence the Projcode is said to be functionally dependent on the attribute Ecode.
Now consider an unnormalized data that is represented in the following Table.
|
Ecode
|
Dept
|
Projcode
|
Hours
|
|
101
343
987
|
Accounts
Admin
Sales
|
20
40
41
56
78
20
45
|
90
101
60
50
92
48
67
|
A
relational model does not permit or support such unnormalized tables. The data
table must be present atleast in the first normal form, which arrears as shown
in the following Table.
|
Ecode
|
Dept
|
Projcode
|
Hours
|
|
101
101
101
343
343
987
987
|
Accounts
Accounts
Accounts
Admin
Admin
Sales
Sales
|
20
40
41
56
78
20
45
|
90
101
60
50
92
48
67
|
Second Normal Form
In the previous form it was noted that there was
redundancy of data, and hence the table was decomposed without any loss of
information. The problems encountered here were:
1. Insertion: Employee details cannot be recorded
until the employee is assigned a project.
2. Updation: The employee code and department is
repeated. Hence if an employee is transferred to another department, these
changes must be reflected everywhere. Any omission will lead inconsistencies.
3.
Deletion:
If an employee
completes his project, his record will be deleted and details pertaining to
that employee lost.
From the
above Table
|
Ecode
|
Dept
|
|
101
343
987
|
Accounts
Admin
Sales
|
|
Ecode
|
Projcode
|
Hours
|
|
101
101
101
343
343
987
987
|
20
40
41
56
78
20
45
|
90
101
60
50
92
48
67
|
It should
be noted that for a table to be the second form it should also be in the first
normal form and every attribute in the record should functionally depend on the
primary key.
Third Normal Form
A table is said to be in the third
normal form if it in second normal form and every non-key attribute is
functionally dependent on just the primary key. The primary key here is Ecode.
The attribute dept-code is dependent on dept. There is an indirect dependence
on the primary key, which has to be noticed. It is illustrated in the following
table.
|
Ecode
|
Dept
|
Dept-code
|
|
101
303
400
500
600
777
|
Finance
Sales
Sales
Admin
Systems
Finance
|
909
906
906
908
901
909
|
Even here
it was noted that there were problems during insertion, updation and deletion.
The relation is thus reduced as shown in the following table.
|
Ecode
|
Dept
|
|
101
303
400
500
600
777
|
Finance
Sales
Sales
Admin
Systems
Finance
|
|
Dept
|
Dept-code
|
|
Finance
Sales
Sales
Admin
Systems
Finance
|
909
906
906
908
901
909
|
Each
non-key attribute is wholly dependent only on the primary key. Even the third
normal form did not satisfy the needs. Hence, a new form called the
Boyee-Codd Normal from was
introduced.
Boyee-Codd Normal Form
The third
normal form was not satisfactory for relations that had multiple candidate
keys, are unique for each row. Consider that another table that has few other
details, as shown in the following table.
|
Cusno
|
Cusname
|
|
100
121
280
|
Preethi
Jai kumar
Shalini
|
Here the
candidate keys are cuscode, and cusname. The attribute cuscode and cusname are
unique for each row. Consider that table that has few other details, as shown
in the following table.
|
Cuscode
|
Cusname
|
Itemcode
|
Qty
|
|
100
100
121
280
|
Preethi
Preethi
Jai Kumar
Shalini
|
19
16
29
33
|
4
2
6
1
|
The
attributes cuscode and cusname of the above two tables can be used to link
these tables. These act as a foreign key in the second table. These tables can
be reduced on order to minimize redundancy. The tables are shown below.
|
Cuncode
|
Itemcode
|
Qty
|
|
100
101
121
280
|
19
16
29
23
|
4
2
6
1
|
|
Cuncode
|
Cusname
|
|
100
121
280
|
Preethi
Jai Kumar
Shalini
|
TABLE SALESPEOPLE
SNUM SNAME CITY COMM
1001 Peel London .12
1002 Serres San
Jose .13
1004
Motika London .11
1007
Rafkin Barcelona .15
1003 Axelrod New
york .1
TABLE CUST
CNUM
CNAME CITY RATING SNUM
2001 Hoffman London 100 1001
2002 Giovanne Rome 200 1003
2003 Liu San
Jose 300 1002
2004 Grass Brelin 100 1002
2006 Clemens
London 300 1007
2007 Pereira Rome 100 1004
ORDERS
ONUM
AMT ODATE CNUM SNUM
3001 18.69 03-OCT-94 2008 1007
3003 767.19 03-OCT-94 2001 1001
3002 1900.10
03-OCT-94 2007 1004
3005 5160.45
03-OCT-94 2003 1002
3006 1098.16
04-OCT-94 2008 1007
3009 1713.23
04-OCT-94 2002 1003
3007 75.75 05-OCT-94 2004 1002
3008 4723.00 05-OCT-94 2006 1001
3010 1309.95
06-OCT-94 2004 1002
3011 9891.88
06-OCT-94 2006 1001
Problems :
1.
Display snum,sname,city and comm of all salespeople.
Select snum, sname, city,
comm
from salespeople;
2.
Display all snum without duplicates from all orders.
Select distinct snum
from orders;
3.
Display names and commissions of all salespeople in london.
Select sname,comm
from salespeople
where city = ‘London’;
4.
All customers with rating of 100.
Select cname
from cust
where rating = 100;
5.
Produce orderno, amount and date form all rows in the order table.
Select ordno, amt, odate
from orders;
6.
All customers in San Jose, who have rating more than 200.
Select cname
from cust
where rating > 200;
7.
All customers who were either located in San Jose or had a rating above
200.
Select cname
from cust
where city = ‘San Jose’ or
rating > 200;
8.
All orders for more than $1000.
Select *
from orders
where amt > 1000;
9.
Names and citires of all salespeople in london with commission above
0.10.
Select sname, city
from salepeople
where comm > 0.10 and
city = ‘London’;
10.
All customers excluding
those with rating <= 100 unless they are located in Rome.
Select cname
from cust
where rating <= 100 or
city = ‘Rome’;
11.
All salespeople either in
Barcelona or in london.
Select sname, city
from salespeople
where city in
(‘Barcelona’,’London’);
12.
All salespeople with
commission between 0.10 and 0.12. (Boundary values should be excluded)
Select sname, comm
from salespeople
where comm > 0.10 and
comm < 0.12;
13.
All customers with NULL
values in city column.
Select cname
from cust
where city is null;
14.
All orders taken on Oct 3Rd and Oct 4th 1994.
Select *
from orders
where odate in
(‘03-OCT-94’,’04-OCT-94’);
15.
All customers serviced by
peel or Motika.
Select cname
from cust, orders
where orders.cnum = cust.cnum
and
orders.snum in ( select snum
from salespeople
where sname in 'Peel','Motika'));
16.
All customers whose names
begin with a letter from A to B.
Select cname
from cust
where cname like ‘A%’ or
cname like ‘B%’;
17.
All orders except those with
0 or NULL value in amt field.
Select onum
from orders
where amt != 0 or
amt is not null;
18.
Count the number of
salespeople currently listing orders in the order table.
Select count(distinct snum)
from orders;
19.
Largest order taken by each
salesperson, datewise.
Select odate, snum, max(amt)
from orders
group by odate, snum
order by odate,snum;
20.
Largest order taken by each
salesperson with order value more than $3000.
Select odate, snum, max(amt)
from orders
where amt > 3000
group by odate, snum
order by odate,snum;
21.
Which day had the hightest
total amount ordered.
Select odate, amt, snum,
cnum
from orders
where amt = (select max(amt)
from orders)
22.
Count all orders for Oct 3rd.
Select count(*)
from orders
where odate = ‘03-OCT-94’;
23.
Count the number of
different non NULL city values in customers table.
Select count(distinct city)
from cust;
24.
Select each customer’s
smallest order.
Select cnum, min(amt)
from orders
group by cnum;
25.
First customer in
alphabetical order whose name begins with G.
Select min(cname)
from cust
where cname like ‘G%’;
26.
Get the output like “ For
dd/mm/yy there are ___ orders.
Select 'For ' ||
to_char(odate,'dd/mm/yy') || ' there are '||
count(*) || ' Orders'
from orders
group by odate;
27.
Assume that each salesperson
has a 12% commission. Produce order no., salesperson no., and amount of
salesperson’s commission for that order.
Select onum, snum, amt, amt
* 0.12
from orders
order by snum;
28.
Find highest rating in each
city. Put the output in this form. For the city (city), the highest rating is :
(rating).
Select 'For the city (' ||
city || '), the highest rating is : (' ||
max(rating) || ')'
from cust
group by city;
29.
Display the totals of orders
for each day and place the results in descending order.
Select odate, count(onum)
from orders
group by odate
order by count(onum);
30.
All combinations of
salespeople and customers who shared a city. (ie same city).
Select sname, cname
from salespeople, cust
where salespeople.city =
cust.city;
31.
Name of all customers matched
with the salespeople serving them.
Select cname, sname
from cust, salespeople
where cust.snum =
salespeople.snum;
32.
List each order number
followed by the name of the customer who made the order.
Select onum, cname
from orders, cust
where orders.cnum = cust.cnum;
33.
Names of salesperson and
customer for each order after the order number.
Select onum, sname, cname
from orders, cust,
salespeople
where orders.cnum =
cust.cnum and
orders.snum = salespeople.snum;
34.
Produce all customer
serviced by salespeople with a commission above 12%.
Select cname, sname, comm
from cust, salespeople
where comm > 0.12 and
cust.snum = salespeople.snum;
35.
Calculate the amount of the
salesperson’s commission on each order with a rating above 100.
Select sname, amt * comm
from orders, cust,
salespeople
where rating > 100 and
salespeople.snum = cust.snum and
salespeople.snum = orders.snum and
cust.cnum = orders.cnum
36.
Find all pairs of customers
having the same rating.
Select a.cname, b.cname,a.rating
from cust a, cust b
where a.rating = b.rating
and
a.cnum != b.cnum
37.
Find all pairs of customers
having the same rating, each pair coming once only.
Select a.cname,
b.cname,a.rating
from cust a, cust b
where a.rating = b.rating
and
a.cnum != b.cnum and
a.cnum < b.cnum;
38.
Policy is to assign three
salesperson to each customers. Display all such combinations.
Select cname, sname
from salespeople, cust
where sname in ( select sname
from salespeople
where rownum
<= 3)
order by cname;
39.
Display all customers
located in cities where salesman serres has customer.
Select cname
from cust
where city = ( select city
from cust, salespeople
where cust.snum = salespeople.snum
and sname = 'Serres');
Select cname
from cust
where city in ( select city
from cust, orders
where
cust.cnum = orders.cnum and
orders.snum in ( select snum
from salespeople
where sname = 'Serres'));
40.
Find all pairs of customers
served by single salesperson.
Select cname from cust
where snum in (select snum from cust
group by snum
having count(snum) > 1);
Select distinct a.cname
from cust a ,cust b
where a.snum = b.snum and
a.rowid != b.rowid;
41.
Produce all pairs of
salespeople which are living in the same city. Exclude combinations of
salespeople with themselves as well as duplicates with the order reversed.
Select a.sname, b.sname
from salespeople a,
salespeople b
where a.snum > b.snum and
a.city = b.city;
42.
Produce all pairs of orders
by given customer, names that customers and eliminates duplicates.
Select c.cname, a.onum,
b.onum
from orders a, orders b,
cust c
where a.cnum = b.cnum and
a.onum > b.onum and
c.cnum = a.cnum;
43.
Produce names and cities of
all customers with the same rating as Hoffman.
Select cname, city
from cust
where rating = (select
rating
from cust
where cname = 'Hoffman')
and cname != 'Hoffman';
44.
Extract all the orders of
Motika.
Select Onum
from orders
where snum = ( select snum
from salespeople
where sname = ‘Motika’);
45.
All orders credited to the
same salesperson who services Hoffman.
Select onum, sname, cname,
amt
from orders a, salespeople
b, cust c
where a.snum = b.snum and
a.cnum = c.cnum and
a.snum = ( select snum
from orders
where
cnum = ( select cnum
from cust
where cname = 'Hoffman'));
46.
All orders that are greater
than the average for Oct 4.
Select *
from orders
where amt > ( select
avg(amt)
from orders
where odate
= '03-OCT-94');
47.
Find average commission of
salespeople in london.
Select avg(comm)
from salespeople
where city = ‘London’;
48.
Find all orders attributed
to salespeople servicing customers in london.
Select snum, cnum
from orders
where cnum in (select cnum
from cust
where city = 'London');
49.
Extract commissions of all
salespeople servicing customers in London.
Select comm
from salespeople
where snum in (select snum
from cust
where city =
‘London’);
50.
Find all customers whose
cnum is 1000 above the snum of serres.
Select cnum, cname from cust
where cnum > ( select
snum+1000
from salespeople
where sname =
'Serres');
51.
Count the customers with
rating above San Jose’s average.
Select cnum, rating
from cust
where rating > ( select
avg(rating)
from cust
where city = 'San
Jose');
52.
Obtain all orders for the
customer named Cisnerous. (Assume you don’t know his customer no. (cnum)).
Select onum, odate
from orders
where cnum = ( select cnum
from cust
where cname =
‘Cisnerous’);
53.
Produce the names and rating
of all customers who have above average orders.
Select max(b.cname),
max(b.rating), a.cnum
from orders a, cust b
where a.cnum = b.cnum
group by a.cnum
having count(a.cnum) > (
select avg(count(cnum))
from orders
group by cnum);
54.
Find total amount in orders
for each salesperson for whom this total is greater than the amount of the
largest order in the table.
Select snum,sum(amt)
from orders
group by snum
having sum(amt) > (
select max(amt)
from orders);
55.
Find all customers with
order on 3rd Oct.
Select cname
from cust a, orders b
where a.cnum = b.cnum and
odate = ‘03-OCT-94’;
56.
Find names and numbers of
all salesperson who have more than one customer.
Select sname, snum
from salespeople
where snum in ( select snum
from cust
group by snum
having count(snum)
> 1 );
57.
Check if the correct
salesperson was credited with each sale.
Select onum, a.cnum, a.snum,
b.snum
from orders a, cust b
where a.cnum = b.cnum and
a.snum != b.snum;
58.
Find all orders with above
average amounts for their customers.
select onum, cnum, amt
from orders a
where amt > ( select avg(amt)
from orders b
where a.cnum = b.cnum
group by cnum);
59.
Find the sums of the amounts
from order table grouped by date, eliminating all those dates where the sum was
not at least 2000 above the maximum amount.
Select odate, sum(amt)
from orders a
group by odate
having sum(amt) > (
select max(amt)
from orders b
where a.odate = b.odate
group by
odate);
60.
Find names and numbers of
all customers with ratings equal to the maximum for their city.
Select a.cnum, a.cname
from cust a
where a.rating = ( select max(rating)
from cust b
where a.city = b.city);
61.
Find all salespeople who
have customers in their cities who they don’t service. ( Both way using Join
and Correlated subquery.)
Select distinct cname
from cust a, salespeople b
where a.city = b.city and
a.snum != b.snum;
Select cname
from cust
where cname in ( select
cname
from cust a, salespeople b
where a.city = b.city and
a.snum
!= b.snum );
62.
Extract cnum,cname and city
from customer table if and only if one or more of the customers in the table
are located in San Jose.
Select * from cust
where 2 < (select
count(*)
from cust
where city = 'San Jose');
63.
Find salespeople no. who
have multiple customers.
Select snum
from cust
group by snum
having count(*) > 1;
64.
Find salespeople number,
name and city who have multiple customers.
Select snum, sname, city
from salespeople
where snum in ( select snum
from cust
group by snum
having count(*)
> 1);
65.
Find salespeople who serve
only one customer.
Select snum
from cust
group by snum
having count(*) = 1;
66.
Extract rows of all
salespeople with more than one current order.
Select snum, count(snum)
from orders
group by snum
having count(snum) > 1;
67.
Find all salespeople who
have customers with a rating of 300. (use EXISTS)
Select a.snum
from salespeople a
where exists ( select b.snum
from cust b
where b.rating = 300 and
a.snum =
b.snum)
68.
Find all salespeople who
have customers with a rating of 300. (use Join).
Select a.snum
from salespeople a, cust b
where b.rating = 300 and
a.snum = b.snum;
69.
Select all salespeople with
customers located in their cities who are not assigned to them. (use EXISTS).
Select snum, sname
from salespeople
where exists ( select cnum
from cust
where salespeople.city
= cust.city and
salespeople.snum != cust.snum);
70.
Extract from customers table
every customer assigned the a salesperson who currently has at least one other
customer ( besides the customer being selected) with orders in order table.
Select a.cnum, max(c.cname)
from orders a, cust c
where a.cnum = c.cnum
group by a.cnum,a.snum
having count(*) < (
select count(*)
from orders b
where a.snum = b.snum)
order by a.cnum;
71.
Find salespeople with
customers located in their cities ( using both ANY and IN).
Select sname
from salespeople
where snum in ( select snum
from cust
where salespeople.city
= cust.city and
salespeople.snum = cust.snum);
Select sname
from salespeople
where snum = any ( select
snum
from cust
where salespeople.city =
cust.city and
salespeople.snum =
cust.snum);
72.
Find all salespeople for
whom there are customers that follow them in alphabetical order. (Using ANY and
EXISTS)
Select sname
from salespeople
where sname < any (
select cname
from cust
where
salespeople.snum = cust.snum);
Select sname
from salespeople
where exists ( select cname
from cust
where salespeople.snum
= cust.snum and
salespeople.sname < cust.cname);
73.
Select customers who have a
greater rating than any customer in rome.
Select a.cname
from cust a
where city = 'Rome' and
rating > ( select max(rating)
from cust
where city != 'Rome');
74.
Select all orders that had
amounts that were greater that atleast one of the orders from Oct 6th.
Select onum, amt
from orders
where odate != '06-oct-94'
and
amt > ( select min(amt)
from orders
where odate = '06-oct-94');
75.
Find all orders with amounts
smaller than any amount for a customer in San Jose. (Both using ANY and without
ANY)
Select onum, amt
from orders
where amt < any ( select
amt
from orders, cust
where city = 'San Jose' and
orders.cnum =
cust.cnum);
Select onum, amt
from orders
where amt < ( select
max(amt)
from orders, cust
where city = 'San Jose' and
orders.cnum = cust.cnum);
76.
Select those customers whose
ratings are higher than every customer in Paris. ( Using both ALL and NOT
EXISTS).
Select * from cust
where rating > any (select rating from cust
where city = 'Paris');
Select *
from cust a
where not exists ( select
b.rating from cust b
where b.city !=
'Paris' and
b.rating >
a.rating);
77.
Select all customers whose
ratings are equal to or greater than ANY of the Seeres.
Select cname, sname
from cust, salespeople
where rating >= any (
select rating
from cust
where snum = (select snum
from
salespeople
where sname = 'Serres'))
and sname != 'Serres'
and salespeople.snum(+) =
cust.snum;
78.
Find all salespeople who have
no customers located in their city. ( Both using ANY and ALL)
Select sname
from salespeople
where snum in ( select snum
from cust
where
salespeople.city != cust.city and
salespeople.snum = cust.snum);
Select sname
from salespeople
where snum = any ( select
snum
from cust
where salespeople.city !=
cust.city and
salespeople.snum = cust.snum);
79.
Find all orders for amounts
greater than any for the customers in London.
Select onum, amt
from orders
where amt > any ( select
amt
from orders,
cust
where city = ‘London’ and
orders.cnum = cust.cnum);
80.
Find all salespeople and
customers located in london.
Select sname, cname
from cust, salespeople
where cust.city = 'London' and
salespeople.city = 'London' and
cust.snum = salespeople.snum;
81.
For every salesperson, dates
on which highest and lowest orders were brought.
Select a.amt, a.odate,
b.amt, b.odate
from orders a, orders b
where (a.amt, b.amt) in
(select max(amt), min(amt)
from orders
group by snum);
82.
List all of the salespeople
and indicate those who don’t have customers in their cities as well as those
who do have.
Select snum, city, 'Customer
Present'
from salespeople a
where exists ( select snum
from cust
where a.snum = cust.snum and
a.city = cust.city)
UNION
select snum, city, 'Customer
Not Present'
from salespeople a
where exists ( select snum
from cust c
where a.snum = c.snum and
a.city != c.city and
c.snum not in ( select
snum
from cust
where a.snum = cust.snum and
a.city = cust.city));
83.
Append strings to the
selected fields, indicating weather or not a given salesperson was matched to a
customer in his city.
Select a.cname,
decode(a.city,b.city,'Matched','Not Matched')
from cust a, salespeople b
where a.snum = b.snum;
84.
Create a union of two
queries that shows the names, cities and ratings of all customers. Those with a
rating of 200 or greater will also have the words ‘High Rating’, while the
others will have the words ‘Low Rating’.
Select cname, cities,
rating, ‘Higher Rating’
from cust
where rating >= 200
UNION
Select cname, cities,
rating, ‘Lower Rating’
from cust
where rating < 200;
85.
Write command that produces
the name and number of each salesperson and each customer with more than one
current order. Put the result in alphabetical order.
Select 'Customer Number ' ||
cnum "Code ",count(*)
from orders
group by cnum
having count(*) > 1
UNION
select 'Salesperson Number
'||snum,count(*)
from orders
group by snum
having count(*) > 1;
86.
Form a union of three
queries. Have the first select the snums of all salespeople in San Jose, then
second the cnums of all customers in San Jose and the third the onums of all
orders on Oct. 3. Retain duplicates between the last two queries, but
eliminates and redundancies between either of them and the first.
Select 'Customer Number ' ||
cnum "Code "
from cust
where city = 'San Jose'
UNION
select 'Salesperson Number
'||snum
from salespeople
where city = 'San Jose'
UNION ALL
select 'Order Number '||
onum
from Orders
where odate = '03-OCT-94';
87.
Produce all the salesperson
in London who had at least one customer there.
Select snum, sname
from salespeople
where snum in ( select snum
from cust
where cust.snum =
salespeople.snum and
cust.city
= 'London')
and city = ‘London’;
88.
Produce all the salesperson
in London who did not have customers there.
Select snum, sname
from salespeople
where snum in ( select snum
from cust
where cust.snum = salespeople.snum and
cust.city = 'London')
and city = 'London';
89.
We want to see salespeople
matched to their customers without excluding those salesperson who were not
currently assigned to any customers. (User OUTER join and UNION)
Select sname, cname
from cust, salespeople
where cust.snum(+) =
salespeople.snum;
Select sname, cname
from cust, salespeople
where cust.snum =
salespeople.snum
UNION
select distinct sname, 'No
Customer'
from cust, salespeople
where 0 = (select count(*)
from cust
where cust.snum = salespeople.snum);
Triggers:
Triggers is
a type of stored procedure that implicitly executed when user performs DML
operation on the table.
It will not
accept any parameters
Types of
triggers:
1.
Insert
trigger
2.
Delete
trigger
3.
Update
trigger
Syntax: create trigger <trigger_name>
for/after, insert/update/delete as sql statement.
Insert
Trigger: This
trigger fires when user performs insert operation on the table. When user
insert a record into the table the temporary table called Inserted is created.
The newly
inserted record is also stored in inserted table temporarily.
àCreate trigger
for
insert
as
begin
print
‘trigger t1 fire’
end
à Create trigger t2 on dept
for
as
print
‘trigger t2 fire’
àCreate trigger t3 on dept
for insert
as
print ‘trigger t3 fired’
àInsert into dept (deptno) values
(50)
trigger t1 fired
trigger t2 fired
trigger t3 fired
Changing
the firing order of triggers:
Syntax:
sp_settrigger order @ trigger name =
‘name_of_trigger’,
@order =
‘first/last’,
@stmtype
= ‘insert/update/delete’
sp_settrigger
order @trigger name = ‘t3’,
@order =
‘first’,
@stmtype =
‘insert’
sp_settrigger
order @trigger name = ‘t1’,
@order = ‘last’,
@stmtype =
‘insert’
insert into
dept (deptno) values (60)
trigger t3 fired
trigger t2 fired
trigger t1 fired
Creating
table from existing table:
1.With
data:
àSelect * into dept 1 from dept
2. Without
data:
àSelect
* into dept 2 from dept
àCreate table dept_insert from dept
Without data
àCreate trigger insert_data
on dept
for insert
as
begin
insert into dept_insert select * from
inserted
end
àInsert into dept values (50,
‘edu’, ‘hyd’)
àSelect * from dept_insert
deptno dname loc
50 edu hyd
àselect * from dept
deptno dname loc
10 ------ -----
20 ------ -----
30 ------ -----
40 ------ -----
50 edu hyd
Delete
trigger: This
trigger fires when users performs delete operations on the table.
When user
deletes the records from table a temporary table called deleted is created due
to the trigger and deleted data is also stored temporarily in that table.
àcreate table dept_delete from dept
without data
àcreate trigger deleted_data on dept
for
as
begin
insert into dept_delete select * from
deleted
end
àdelete from dept where deptno = 10
àselect * from dept
à10
dept details will not appears
àselect * from dept_delete
deptno dname 10c
10 Accounting Newyork
Update
trigger:
This
trigger fires with update operation when update operation is performed on the
table. Two temporary tables, 1.INSERT 2.DELETED are created due to trigger the
modified data is stored in the inserted table and old data stored in deleted
table.
àtruncate table dept_insert
àtruncate table dept_delete
àcreate trigger update_data
on dept
for update
as
begin
insert into dept_insert select * from
insert
insert into dept_delete select * from
deleted
end
àupdate dept
set dname = ‘EXPORT”
loc = ‘SEC’ where deptno = 10
àselect * from dept_insert
deptno dname loc
10 EXPORT SEC
àselect * from dept_delete
deptno dname loc
10 Accounting
Newyork
Instead of Triggers: These trigger are mainly created for views.
Syntax: create trigger trigger_name on <view_name>
Instead of insert/update/delete
As
Sql statement
àcreate view v10
as
select * from emp where
deptno=10
àselect * from v10
àit
willdisplay an 10th dept employees details.
àcreate trigger v10_trg
instead of insert
as
update v10 set sal = sal +
500
àinsert into v10 (empno, ename, deprno)
values (100, ‘CHAD’, 10)
àselect * from v10
àinstead
records will not appears but salaries are modified due instead of trigger.
àcreate view dept_view
as
for insert
as
delete from dept_view
àinsert into dept_view (deptno) values (60)
àselect * from dept_view
datadeleted due to instead
of trigger
àcreate view emp_dept
as
select empno, ename, dept.
deptno, dname from emp, dept where emp.deptno = dept. deptno
select * from emp_dept
|
Empno
|
Ename
|
Deptno
|
Dname
|
|
|
|
|
|
|
|
|
|
|
Insert into emp_dept (empnon ename, deptno, dname)
values (100, ‘CHAD”,50, ‘IMPORT”)
|
Since
not possible to insert data into two tables through single view
àcreate trigger emp dept_trg
on emp_dept
for insert
as
begin
insert into
emp (empno,ename) select empno, ename from inserted.
Insert into
dept (deptno, dname)select deptnp, dname from inserted.
End
àinsert into emp_dept (empno, ename,
deptno, dname)
values
(100, ‘CHAD’, 50, ‘IMPORT’)
àselect * from emp
ànew
record will be displayed along with old records
àselect * from dept
ànew
record will be displayed along with old records.
Displaying
the code of Trigger:
Syntax:
Sp_helptext
‘trigger_name’
Sp_helptext
‘emp_dept’
Displaying
the triggers information for the table
Sp_helptrigger ‘table_name’
Sp_helptrigger ‘dept’
DCL
Commands: these
commands are used to grant or revoke the permission on database objects to
other users.
1.
grant: Used to
grant the permissions and data objects to users.
Adminstrator tools
Grant select on dept to ram
Grant insert, select on emp
to ram
Ram/ram
123 login
Select
* from dept
àdept data is displayed
insert
into dept values (50, ‘***’, ‘ddd’)
àerror since no insert permission to ram
select
* from emp
insert
into emp (empno, ename) values (400, ‘chris’)
à 1
record is inserted
update
emp set empno = 111 where ename = ‘SMITH’
àerror
since no update permission to ram
Revoke:
used to take back
the permissions given to the users
Revoke
select on dept from RAM
Revoke
select, insert an emp from RAM
Administrator
login:
Granting
permission with grant option:
Grant select on dept to ram with grant
option
Ram
login:
Select *
from dept
àdata
will be displayed
grant
select on dept to naresh
naresh
login:
select *
from dept
Administrator
login:
Revoke
select on dept from ram cascade
ànow both ram and naresh will loose the permission on
dept.
àsp_who
àit will display the status of logins.
No comments:
Post a Comment