number primary key, title varchar2(40) not null, pubid number not null, year date not
null, price number(9,2));
Table created.
CONSTRAINTS ADDED TO TABLES:
sql> alter table book10 add constraint pub_fk foreign key(pubid) references publisher10(pubid);
Table altered.
TABLE DESCRIPTIONS:
sql> desc publisher10;
Name Null? Type
------------- ------------------------ ----------------------------
PUBID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
CITY NOT NULL VARCHAR2(20)
sql> desc book10;
Name Null? Type
------------- ---------------- ----------------------------
ID NOT NULL NUMBER
TITLE NOT NULL VARCHAR2(40)
PUBID NOT NULL NUMBER
YEAR NOT NULL DATE
PRICE NUMBER(9,2)
INSERTING VALUES INTO PUBLISHER10 TABLE:
sql> insert into publisher10 values(100, 'microsoft', 'washington');
1 row created.
sql> insert into publisher10 values(101, 'sun microsystem', 'sanfrancisco');
1 row created.
sql> insert into publisher10 values(102, 'oracle', 'britan');
1 row created.
sql> insert into publisher10 values(103, 'tatamcgraw hill', 'delhi');
1 row created.
sql> insert into publisher10 values(104, 'samba', 'chennai');
1 row created.
INSERTING VALUES INTO BOOK10 TABLE:
sql> insert into book10 values(500, 'java', 100,'20-jan-2000', 505);
1 row created.
sql> insert into book10 values(501, 'programming in c', 101,'10-aug-2005', 2350);
1 row created.
sql> insert into book10 values(504, 'dotnet', 104,'20-jan-2005', 35);
1 row created.
sql> insert into book10 values(502, 'perl', 102,'15-dec-2002', 450);
1 row created.
sql> insert into book10 values(506, 'cobal', 102,'12-sep-2007', 1525);
1 row created.
DISPLAYING TABLE CONTENTS:
sql> select * from publisher10;
PUBID NAME CITY
---------- ------------------------------ --------------------
100 microsoft washington
101 sun sun microsystem
102 oracle britan
103 tatamcgraw hill delhi
104 samba chennai
sql> select * from book10;
ID TITLE PUBID YEAR PRICE
----- ------------------------------ ---------- --------------- ----------
500 java 100 20-JUN-00 505
501 programming in c 101 10-AUG-05 2350
504 dotnet 104 20-JAN-05 35
502 perl 102 15-DEC-02 450
506 cobol 102 12-SEP-07 1525
WRITE A PL/SQL TRIGGER WHICH GETS ACTIVATED WHENEVER A NEW RECORD IS
INSERTED INTO PUBLISHER TABLE.IT SHOULD CHANGE THE PUBLISHER NAME AND CITY TO
UPPERCASE.
SQL> create or replace trigger pubins_tri
2 before insert on publisher10 for each row
3 declare
4 pid publisher10.pubid%type:=(:new.pubid);
5 pname publisher10.name%type:=upper(:new.name);
6 pcity publisher10.city%type:=upper(:new.city);
7 begin
8 dbms_output.put_line(pid);
9 dbms_output.put_line(pname);
10 dbms_output.put_line(pcity);
11 :new.pubid:=pid;
12 :new.name:=pname;
13 :new.city:=pcity;
14 end;
15 /
Trigger created.
BEFORE TRIGGER EXECUTION:
sql> select * from publisher10;
PUBID NAME CITY
---------- ------------------------------ --------------------
100 microsoft washington
101 sun sun microsystem
102 oracle britan
103 tatamcgraw hill delhi
104 samba chennai
EXECUTION:
sql> insert into publisher10 values('500', 'ahamed', 'bangalore');
1 row created.
AFTER TRIGGER EXECUTION:
sql> select * from publisher10;
PUBID NAME CITY
---------- ------------------------------ --------------------
100 microsoft washington
101 sun sun microsystem
102 oracle britan
103 tatamcgraw hill delhi
104 samba chennai
500 AHAMED BANGALORE
6 rows selected.
Comments