Triggers


1)Trigger that restricts insertion or updation of account having balance less than 100.
Sol.

CREATE TABLE CUSTOMER (CNO NUMBER PRIMARY KEY NOT NULL, CNAME VARCHAR2(20),CITY VARCHAR2(20))

insert into customer values (1,’Rajesh’,’pune’)
insert into customer values (2,’Girish’,’pune’)
insert into customer values (3,’Nilesh’,’Mumbai’)
insert into customer values (4,’Sandip’,’Mumbai’)
insert into customer values (5,’Raj’,’Nashik’)

CREATE TABLE ACCOUNT (ANO NUMBER PRIMARY KEY NOT NULL,ACC_TYPE VARCHAR2(20),BALANCE NUMBER,CNO NUMBER REFERENCES CUSTOMER(CNO))

insert into account values (1,’Saving’,8000,1)
insert into account values (2,’Current’,12000,2)
insert into account values (3,’Saving’,9000,3)
insert into account values (4,’Current’,11000,4)
insert into account values (5,’Saving’,14000,5)

Create or Replace Trigger upaccount
Before
Insert or Update on account
For each row
Begin
If (:old. balance <100 || :new. balance<100) then
raise_application_error(-20002,’You can not insert or update account having balance less than 100’ );
End if;
End;
Update account set balance=5000 where ano=2;

2)Create or replace a Trigger that restricts insertion or updation of books having price less than 0.
Sol.

CREATE TABLE BOOK (BNO NUMBER PRIMARY KEY NOT NULL, BNAME VARCHAR2(20), PUBNAME VARCHAR2(20), PRICE NUMBER);

insert into book values(1,’C’,’BPB’,170)
insert into book values(2,’C++’,’BPB’,270)
insert into book values(3,’Mathematics’,’BPB’,140)
insert into book values(4,’Economics’,’Vision’,170)
insert into book values(5,’Accounting’,’Nirali’,240)

CREATE TABLE AUTHOR(ANO NUMBER PRIMARY KEY NOT NULL, ANAME VARCHAR2(20))

insert into author values(1,’Kanetkar’)
insert into author values(2,’Balgurusamy’)
insert into author values(3,’Wrox’)
insert into author values(4,’Fonde’)

CREATE TABLE BOOK_AUTHOR(BNO NUMBER REFERENCES BOOK(BNO),ANO NUMBER REFERENCES AUTHOR(ANO))

insert into book_author values(1,1)
insert into book_author values(2,1)
insert into book_author values(3,2)
insert into book_author values(4,3)

create or replace trigger updateofbook
before
insert or update on book
For each row
begin
if (:new.price<0)then
raise_application_error(-20001,’Data can not modified’);
end if;
end;

update book set price =-200 where bno=1

3)Create or replace a Trigger that restricts insertion or updation of students having totalmarks less than 0.
sol.

CREATE TABLE STUDENT(ROLLNO NUMBER PRIMARY KEY NOT NULL,NAME VARCHAR2(20),TOTALMARKS NUMBER,CLASS VARCHAR2(20))

insert into STUDENT values(1,’Jay’,380,’TY’)
insert into STUDENT values(2,’Ajay’,480,’TY’)
insert into STUDENT values(3,’Vijay’,390,’FY’)
insert into STUDENT values(4,’Sanjay’,420,’TY’)
insert into STUDENT values(5,’Manjit’,380,’SY’)

CREATE TABLE TEACHER(TNO NUMBER PRIMARY KEY NOT NULL,TNAME VARCHAR2(20))

insert into TEACHER values(1,’Nilesh’)
insert into TEACHER values(2,’Rajesh’)
insert into TEACHER values(3,’Sachin’)
insert into TEACHER values(4,’Ranjeet’)

CREATE TABLE STUDENT_TEACHER(ROLLNO NUMBER REFERENCES STUDENT(ROLLNO),TNO NUMBER REFERENCES TEACHER(TNO),SUBJECT VARCHAR2(20))

insert into STUDENT_TEACHER values(1,1,’Data Structure’)
insert into STUDENT_TEACHER values(2,2,’Data Structure’)
insert into STUDENT_TEACHER values(3,3,’Account’)
insert into STUDENT_TEACHER values(1,4,’C’)
insert into STUDENT_TEACHER values(4,1,’Data Structure’)

create or replace trigger total
before
insert or update on student
for each row
begin
if (:new.TOTALMARKS<0)then
raise_application_error(-20001,’Marks can not less than 0′);
end if;
end;

4)A trigger before update on status of project such that
Sol.

CREATE TABLE EMPLOYEE(ENO NUMBER PRIMARY KEY NOT NULL ENABLE,ENAME VARCHAR2(20),CITY VARCHAR2(20),DEPTNAME VARCHAR2(20))

insert into employee values (1,’Ajay’,’Pune’,’Computer’)
insert into employee values (2,’Vijay’,’Pune’,’Computer’)
insert into employee values (3,’Sanjay’,’Pune’,’Marketing’)
insert into employee values (4,’Jay’,’Pune’,’Production’)

CREATE TABLE PROJECT(PNO NUMBER PRIMARY KEY NOT NULL,PNAME VARCHAR2(20),STATUS VARCHAR2(20))

insert into PROJECT values (1,’Shree’,’C’)
insert into PROJECT values (2,’Jai’,’P’)
insert into PROJECT values (3,’Om’,’P’)
insert into PROJECT values (4,’Sai’,’I’)

CREATE TABLE EMPLOYEE_PROJECT(ENO NUMBER REFERENCES EMPLOYEE (ENO),PNO NUMBER REFERENCES PROJECT (PNO),NUMBEROFDAY NUMBER)

insert into EMPLOYEE_PROJECT values (1,1,45)
insert into EMPLOYEE_PROJECT values (2,1,65)
insert into EMPLOYEE_PROJECT values (3,2,25)
insert into EMPLOYEE_PROJECT values (2,3,35)
insert into EMPLOYEE_PROJECT values (4,3,75)
insert into EMPLOYEE_PROJECT values (2,4,35)
insert into EMPLOYEE_PROJECT values (4,4,75)

create or replace trigger prostatus
before update on project
for each row
begin
if (:old.status=’C’)then
raise_application_error(-20001,’Status can not be changed’);
end if;
end;

calling program
update project set status=’I’ where pno=1

5)Create or replace a Trigger that restricts insertion or updation of movies released before year 2005.
Sol.

CREATE TABLE MOVIE (MVNO NUMBER PRIMARY KEY NOT NULL, MVNAME VARCHAR2(20),RELEASEYEAR NUMBER )

insert into Movie values (1,’Lawaris’,1990)
insert into Movie values (2,’Gajani’,2008)
insert into Movie values (3,’Don’,1988)
insert into Movie values (4,’Taal’,2002)
insert into Movie values (5,’Devdas’,2002)

CREATE TABLE ACTOR (ACTNO NUMBER PRIMARY KEY NOT NULL, ACTNAME VARCHAR2(20))

insert into Actor values (1,’Amitabh’)
insert into Actor values (2,’Amir’)
insert into Actor values (3,’Aishwarya’)
insert into Actor values (4,’Anil’)

CREATE TABLE MOVIE_ACTOR (MVNO NUMBER REFERENCES MOVIE(MVNO),ACTNO NUMBER REFERENCES ACTOR(ACTNO),RATE NUMBER)

insert into Movie_Actor values (1,1,200000)
insert into Movie_Actor values (2,2,2000000)
insert into Movie_Actor values (2,4,1000000)
insert into Movie_Actor values (3,1,300000)
insert into Movie_Actor values (4,3,1000000)
insert into Movie_Actor values (5,3,1000000)

create or replace trigger mv_2005
before
insert or update on movie
for each row
begin
if (:new.RELEASEYEAR<2005)then
raise_application_error(-20002,’Year Should not be less than 2005′);
end if;
end;
update movie set RELEASEYEAR=2004 where movieno=2

6)Create or Replace a trigger that restricts insertion or updation of book having price less than 0.
Sol.

CREATE TABLE DEPARTMENT (DNO NUMBER PRIMARY KEY NOT NULL, DNAME VARCHAR2(20))

insert into Department values(1,’Computer’)
insert into Department values(2,’Science’)
insert into Department values(3,’Arts’)
insert into Department values(4,’Commerce’)

CREATE TABLE BOOK (BNO NUMBER PRIMARY KEY NOT NULL, BNAME VARCHAR2(20), PUBNAME VARCHAR2(20), PRICE NUMBER, DNO NUMBER REFERENCES DEPARTMENT(DNO))

insert into book values(1,’C’,’BPB’,170,1)
insert into book values(2,’C++’,’BPB’,270,1)
insert into book values(3,’Mathematics’,’BPB’,140,2)
insert into book values(4,’Economics’,’Vision’,170,3)
insert into book values(5,’Accounting’,’Nirali’,240,4)

create or replace trigger updateofbook
before
insert or update on book
For each row
begin
if (:new.price<0)then
raise_application_error(-20001,’Data can not modified’);
end if;
end;
update book set price =-200 where bno=1

6)Trigger that restricts insertion or updation of table such that number_of_days should not be less than zero.
Sol.

CREATE TABLE EMPLOYEE(ENO NUMBER PRIMARY KEY NOT NULL ENABLE,ENAME VARCHAR2(20),CITY VARCHAR2(20),DEPTNAME VARCHAR2(20))

insert into employee values (1,’Ajay’,’Pune’,’Computer’)
insert into employee values (2,’Vijay’,’Pune’,’Computer’)
insert into employee values (3,’Sanjay’,’Pune’,’Marketing’)
insert into employee values (4,’Jay’,’Pune’,’Production’)

CREATE TABLE PROJECT(PNO NUMBER PRIMARY KEY NOT NULL,PNAME VARCHAR2(20),STATUS VARCHAR2(20))

insert into PROJECT values (1,’Shree’,’C’)
insert into PROJECT values (2,’Jai’,’P’)
insert into PROJECT values (3,’Om’,’P’)
insert into PROJECT values (4,’Sai’,’I’)

CREATE TABLE EMPLOYEE_PROJECT(ENO NUMBER REFERENCES EMPLOYEE (ENO),PNO NUMBER REFERENCES PROJECT (PNO),NUMBEROFDAY NUMBER)

insert into EMPLOYEE_PROJECT values (1,1,45)
insert into EMPLOYEE_PROJECT values (2,1,65)
insert into EMPLOYEE_PROJECT values (3,2,25)
insert into EMPLOYEE_PROJECT values (2,3,35)
insert into EMPLOYEE_PROJECT values (4,3,75)
insert into EMPLOYEE_PROJECT values (2,4,35)
insert into EMPLOYEE_PROJECT values (4,4,75)

create or replace trigger up
before
insert or update on employee_project
for each row
begin
if (:new.numberofday<0) then
raise_application_error(-20001,’Days can not be less than 0′);
end if;
end;

update employee_project set numberofday=-20 where pno=1

7)Create or replace a Trigger that restricts insertion or updation of students having totalmarks greater than 500.
Sol.

CREATE TABLE STUDENT(ROLLNO NUMBER PRIMARY KEY NOT NULL,NAME VARCHAR2(20),TOTALMARKS NUMBER,CLASS VARCHAR2(20))

insert into STUDENT values(1,’Jay’,380,’TY’)
insert into STUDENT values(2,’Ajay’,480,’TY’)
insert into STUDENT values(3,’Vijay’,390,’FY’)
insert into STUDENT values(4,’Sanjay’,420,’TY’)
insert into STUDENT values(5,’Manjit’,380,’SY’)

CREATE TABLE TEACHER(TNO NUMBER PRIMARY KEY NOT NULL,TNAME VARCHAR2(20))

insert into TEACHER values(1,’Nilesh’)
insert into TEACHER values(2,’Rajesh’)
insert into TEACHER values(3,’Sachin’)
insert into TEACHER values(4,’Ranjeet’)

CREATE TABLE STUDENT_TEACHER(ROLLNO NUMBER REFERENCES STUDENT(ROLLNO),TNO NUMBER REFERENCES TEACHER(TNO),SUBJECT VARCHAR2(20))

insert into STUDENT_TEACHER values(1,1,’Data Structure’)
insert into STUDENT_TEACHER values(2,2,’Data Structure’)
insert into STUDENT_TEACHER values(3,3,’Account’)
insert into STUDENT_TEACHER values(1,4,’C’)
insert into STUDENT_TEACHER values(4,1,’Data Structure’)

create or replace trigger total
before
insert or update on student
for each row
begin
if (:new.TOTALMARKS>500)then
raise_application_error(-20001,’Marks can not greater than 500′);
end if;
end;

8)Trigger for updation of an Employee table that restricts new salary should not less than old salary.
Sol.

Create or Replace Trigger updateemp
Before
Update on employee
For each row
Begin
If (:new.salary <:old.salary) then
raise_application_error(-20002,’Salary Can not be less than old salary’);
End if;
End;
Update employee set salary=1000 where empno =2;

9)Trigger that restricts insertion or updation of movies released.

sol.

CREATE TABLE MOVIE (MVNO NUMBER PRIMARY KEY NOT NULL, MVNAME VARCHAR2(20),RELEASEYEAR NUMBER)

insert into Movie values (1,’Lawaris’,1990)
insert into Movie values (2,’Gajani’,2008)
insert into Movie values (3,’Don’,1988)
insert into Movie values (4,’Taal’,2002)
insert into Movie values (5,’Devdas’,2002)

CREATE TABLE ACTOR (ACTNO NUMBER PRIMARY KEY NOT NULL, ACTNAME VARCHAR2(20))

insert into Actor values (1,’Amitabh’)
insert into Actor values (2,’Amir’)
insert into Actor values (3,’Aishwarya’)
insert into Actor values (4,’Anil’)

CREATE TABLE MOVIE_ACTOR (MVNO NUMBER REFERENCES MOVIE(MVNO),ACTNO NUMBER REFERENCES ACTOR(ACTNO),RATE NUMBER)

insert into Movie_Actor values (1,1,200000)
insert into Movie_Actor values (2,2,2000000)
insert into Movie_Actor values (2,4,1000000)
insert into Movie_Actor values (3,1,300000)
insert into Movie_Actor values (4,3,1000000)
insert into Movie_Actor values (5,3,1000000)

Create or Replace Trigger movie2002
Before
Insert or Update on movie
For each row
Begin
If (:new. releaseyear <2000 then
raise_application_error(-20002,’Year Should not be less than 2002);
End if;
End;
Update movie set releaseyear=1990 where movieno=2;

One thought on “Triggers

  1. vishal says:

    nise yaar tere bajse hume rd/ds ka study bahot aasan huva, ab mujhe ye nahi pata ki tera yehsan kaise chukau please meri help karo. g.e.reply fast. “vishal padekar”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s