Triggers ex : 4 DBMS Lab

Triggers

SQL> create table books1(isbn number(5)primary key,title varchar(25),author varchar(25),price number(7,2),pubyear number(4)); 

Table created. 

SQL> create table stock(isbn number(5)references books(isbn),stkqtty number(7),reorderlvl number(7),reorderqtty number(7)); 

Table created. 

SQL> create table customers(custid number(5)primary key,custname varchar(25),address varchar(30)); 

Table created. 

SQL> create table orders(orderno number(5)primary key,custid number(5)references customers(custid),orderdate date); 

Table created 

SQL> insert into books values(&isbn,'&title','&author',&price,&pubyear);

Enter value for isbn: 4251

Enter value for title: The Scarlet Pimpernal

Enter value for author: Thomas Odissey

Enter value for price: 560

Enter value for pubyear: 1995

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(4251,'The Scarlet Pimpernal','Thomas Odissey',560,1995) 

1 row created. 

SQL> /

Enter value for isbn: 17854

Enter value for title: The Tempest

Enter value for author: Shakespeare

Enter value for price: 129

Enter value for pubyear: 1886

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(17854,'The Tempest','Shakespeare',129,1886) 

1 row created. 

SQL> /

Enter value for isbn: 19546

Enter value for title: Harry Potter Series

Enter value for author: J.K.Rowling

Enter value for price: 985

Enter value for pubyear: 2004

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(19546,'Harry Potter Series','J.K.Rowling',985,2004) 

1 row created. 

SQL> /

Enter value for isbn: 7546

Enter value for title: The Arabian Nights

Enter value for author: Arab philosophers

Enter value for price: 1876

Enter value for pubyear: 1885

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(7546,'The Arabian Nights','Arab philosophers',1876,1885) 

1 row created. 

SQL> /

Enter value for isbn: 5248

Enter value for title: Julius Caesar

Enter value for author: Shakespeare

Enter value for price: 768

Enter value for pubyear: 1779

old   1: insert into books values(&isbn,'&title','&author',&price,&pubyear)

new   1: insert into books values(5248,'Julius Caesar','Shakespeare',768,1779) 

1 row created. 

SQL> insert into stock values(&isbn,&stkqtty,&reorderlvl,&reorderqtty);

Enter value for isbn: 4251

Enter value for stkqtty: 7000

Enter value for reorderlvl: 12

Enter value for reorderqtty: 150

old   1: insert into stock values(&isbn,&stkqtty,&reorderlvl,&reorderqtty)

new   1: insert into stock values(4251,7000,12,150) 

1 row created. 

SQL> /

Enter value for isbn: 17854

Enter value for stkqtty: 1200

Enter value for reorderlvl: 18

Enter value for reorderqtty: 496

old   1: insert into stock values(&isbn,&stkqtty,&reorderlvl,&reorderqtty)

new   1: insert into stock values(17854,1200,18,496) 

1 row created. 

SQL> /

Enter value for isbn: 19546

Enter value for stkqtty: 1165

Enter value for reorderlvl: 36

Enter value for reorderqtty: 815

old   1: insert into stock values(&isbn,&stkqtty,&reorderlvl,&reorderqtty)

new   1: insert into stock values(19546,1165,36,815) 

1 row created. 

SQL> /

Enter value for isbn: 7546

Enter value for stkqtty: 1350

Enter value for reorderlvl: 78

Enter value for reorderqtty: 95

old   1: insert into stock values(&isbn,&stkqtty,&reorderlvl,&reorderqtty)

new   1: insert into stock values(7546,1350,78,95) 

1 row created.
 

SQL> /

Enter value for isbn: 5248

Enter value for stkqtty: 23

Enter value for reorderlvl: 48

Enter value for reorderqtty: 14

old   1: insert into stock values(&isbn,&stkqtty,&reorderlvl,&reorderqtty)

new   1: insert into stock values(5248,23,48,14) 

1 row created. 

SQL> insert into customers values(&custid,'&custname','&address');

Enter value for custid: 11000

Enter value for custname: Sunitha

Enter value for address: 12, Nethaji Street, Madurai.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(11000,'Sunitha','12, Nethaji Street, Madurai.') 

1 row created. 

SQL> /

Enter value for custid: 11005

Enter value for custname: Ashok

Enter value for address: 154, K.R.Nagar, Coimbatore.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(11005,'Ashok','154, K.R.Nagar, Coimbatore.') 

1 row created. 

SQL> /

Enter value for custid: 11501

Enter value for custname: Ashwin

Enter value for address: 176, East street, Erode.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(11501,'Ashwin','176, East street, Erode.') 

1 row created.
 

SQL> /

Enter value for custid: 12540

Enter value for custname: Sunil

Enter value for address: 90, Rajaji Street, Chidambaram

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(12540,'Sunil','90, Rajaji Street, Chidambaram') 

1 row created. 

SQL> /

Enter value for custid: 14260

Enter value for custname: Uday

Enter value for address: 59, Gandhi Street, Tutucorin.

old   1: insert into customers values(&custid,'&custname','&address')

new   1: insert into customers values(14260,'Uday','59, Gandhi Street, Tutucorin.') 

1 row created. 

SQL> insert into orders values(&orderno,&custid,'&orderdate');

Enter value for orderno: 1701

Enter value for custid: 11000

Enter value for orderdate: 23-FEB-1992

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(1701,11000,'23-FEB-1992') 

1 row created.

SQL> /

Enter value for orderno: 1654

Enter value for custid: 11005

Enter value for orderdate: 24-MAR-94

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(1654,11005,'24-MAR-94') 

1 row created. 

SQL> /

Enter value for orderno: 17845

Enter value for custid: 11501

Enter value for orderdate: 12-APR-89

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(17845,11501,'12-APR-89') 

1 row created. 

SQL> /

Enter value for orderno: 7842

Enter value for custid: 12540

Enter value for orderdate: 9-SEP-96

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(7842,12540,'9-SEP-96') 

1 row created. 

SQL> /

Enter value for orderno: 5640

Enter value for custid: 14260

Enter value for orderdate: 6-OCT-98

old   1: insert into orders values(&orderno,&custid,'&orderdate')

new   1: insert into orders values(5640,14260,'6-OCT-98') 

1 row created.
 

SQL> select * from books; 

      ISBN TITLE                     AUTHOR                         PRICE          PUBYEAR

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

      4251 The Scarlet Pimpernal     Thomas Odissey                   560                   1995 

     17854 The Tempest               Shakespeare                      129              1886 

     19546 Harry Potter Series       J.K.Rowling                      985              2004
 

      ISBN TITLE                     AUTHOR                         PRICE          PUBYEAR

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

      7546 The Arabian Nights        Arab philosophers               1876                  1885 

      5248 Julius Caesar             Shakespeare                      768                  1779
 

SQL> select * from stock; 

      ISBN    STKQTTY REORDERLVL REORDERQTTY

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

      4251       7000         12        150

     17854       1200         18         496

     19546       1165         36         815

      7546       1350         78          95

      5248         23         48          14 

SQL> select * from customers; 

    CUSTID CUSTNAME                  ADDRESS

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

     11000 Sunitha                   12, Nethaji Street, Madurai.

     11005 Ashok                     154, K.R.Nagar, Coimbatore.

     11501 Ashwin                    176, East street, Erode.

     12540 Sunil                     90, Rajaji Street, Chidambaram

     14260 Uday                      59, Gandhi Street, Tutucorin. 

SQL> select * from orders; 

   ORDERNO     CUSTID  ORDERDATE

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

      1701     11000          23-FEB-1992

      1702      11005          24-MAR-94

     11501      11501          12-APR-89

      1705      12540          9-SEP-96

     11706      14260          6-OCT-98 

SQL> create table orderlist(orderno number(5)references orders(orderno),isbn number(5)references books(isbn),qtty number(5),totprice number(5),shipdate date); 

Table created. 

SQL> create or replace trigger trigger3

  2  before insert on orderlist for each row

  3  declare

  4  qn number(5);

  5  dat date;

  6  begin

  7  select stkqtty into qn from stock where isbn=:new.isbn;

  8  select orderdate into dat from orders where orderno=:new.orderno;

  9  if :new.qtty>qn then

10  dbms_output.put_line('ordered quantity is not available');

11  end if;

12  if months_between(:new.shipdate,dat)<0 then

13  dbms_output.put_line('ordered date is greater than shipment date');

14  end if;

15  end;

16  / 

Trigger created. 

SQL> insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate');

Enter value for orderno: 1701

Enter value for isbn: 4251

Enter value for qtty: 36100

Enter value for totprice: 6900

Enter value for shipdate: 09-APR-92

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(1701,4251,36100,6900,'09-APR-92') 

1 row created.

SQL> /

Enter value for orderno: 1654

Enter value for isbn: 17854

Enter value for qtty: 8795

Enter value for totprice: 520

Enter value for shipdate: 19-JUL-97

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(1654,17854,8795,520,'19-JUL-97') 

1 row created. 

SQL> /

Enter value for orderno: 7842

Enter value for isbn: 7546

Enter value for qtty: 4520

Enter value for totprice: 320

Enter value for shipdate: 27-SEP-68

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(7842,7546,4520,320,'27-SEP-68') 

1 row created. 

SQL> /

Enter value for orderno: 5640

Enter value for isbn: 5248

Enter value for qtty: 790

Enter value for totprice: 653

Enter value for shipdate: 12-DEC-96

old   1: insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1: insert into orderlist values(5640,5248,790,653,'12-DEC-96') 

1 row created. 

SQL> /

Enter value for orderno: 1654

Enter value for isbn: 17854

Enter value for qtty: 17000

Enter value for totprice: 2360

Enter value for shipdate: 18-MAR-94

old   1:  insert into orderlist values(&orderno,&isbn,&qtty,&totprice,'&shipdate')

new   1:  insert into orderlist values(1654,17854,17000,2360,'18-MAR-94') 

1 row created.
 

SQL> select * from orderlist; 

   ORDERNO       ISBN       QTTY   TOTPRICE SHIPDATE

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

      7842       7546       7200        620 10-FEB-98

      5640       5248        790        760 19-OCT-97

     17845      19546       9000        680 18-SEP-99

      1654      17854      17000       2360 18-MAR-94 

SQL> create or replace trigger trigger6

  2  after update on books1 for each row

  3  when(new.price>old.price)

  4  begin

  5  update orderlist set totprice=:new.price*qtty where isbn=:new.isbn;

  6  end;

  7  / 

Trigger created. 

SQL> update books1 set price=600 where isbn=7546; 

1 row updated. 

SQL> select * from books; 

      ISBN TITLE                     AUTHOR                         PRICE             PUBYEAR

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

      4251 The Scarlet Pimpernal     Thomas Odissey             560 1995 

     17854 The Tempest               Shakespeare                      129    1886 

     19546 Harry Potter Series       J.K.Rowling                      985    2004
 

      ISBN TITLE                     AUTHOR                         PRICE    PUBYEAR

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

      7546 The Arabian Nights        Arab philosophers               1876      1885 

      5248 Julius Caesar             Shakespeare                      768      1779 

SQL> Create or replace trigger trigger9

  2  After delete on orders for each row

  3  Begin

  4  Delete from orderlist where orderno=:old.orderno;

  5  End;

  6  / 

Trigger created. 

SQL> delete from orders where orderno=1701; 

1 row deleted. 

SQL> select * from orders; 

   ORDERNO     CUSTID ORDERDATE

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

      1654      11005 24-MAR-94

     17845      11501 12-APR-89

      7842      12540 09-SEP-96

      5640      14260 06-OCT-98