Triggers

SQL> create table stdtab(sno number,sname varchar(10),dept varchar(10)); Table created. SQL>insert into stdtab values(&sno,’&sname’,’&dept’); Enter the sno:1 Enter the sname:kalai Enter the dept:cse 1 row created. SQL>\ Enter the sno:2 Enter the sname:baran Enter the dept:mech 1 row created. SQL>\ Enter the sno:3 Enter the sname:kiran Enter the dept:cse 1 row created. SQL> select * from stdtab; SNO -----1 2 3

SNAME -----------kalai baran kiran

DEPT ---------cse mech cse

3 rows selected. SQL> create table cse115(sno number,sname varchar(10),dept varchar(10)); Table created.

SQL> set serverout on SQL> edit Wrote file afiedt.buf 1* create table cse115(sno number,sname varchar(10),dept varchar(10)) SQL> declare 2 reg number; 3 d1 varchar(10); 4 d2 varchar(10); 5 cursor cr is select * from stdtab; 6 begin 7 open cr; 8 loop 9 fetch cr into reg,d1,d2; 10 exit when cr% notfound; 11 insert into cse115 values(reg,d1,d2); 12 dbms_output.put_line(reg||d1||d2); 13 end loop; 14 dbms_output.put_line(’row’||cr% rowcount); 15 close cr; 16 end; 17 / 1 kalai cse 2 baran mech 3 kiran cse row3 PL/SQL procedure successfully completed. SQL> select * from cse115; SNO SNAME DEPT -------- ---------------1 kalai cse 2 baran mech 3 kiran cse 3 rows selected.

SQL> create table v115(no number,name varchar(10),old number(5),new number(5),total number(5),amt number(5)); Table created. SQL> insert into v115 values(&no,'&name',&old,&new,&total,&amt); Enter value for no: 1 Enter value for name: vvv Enter value for old: 45 Enter value for new:55 Enter value for total: 0 Enter value for amt: 0 old 1: insert into v115 values(&no,'&name',&old,&new,&total,&amt) new 1: insert into v115 values(1,'vvv',200,300,0,0) 1 row created. SQL> / Enter value for no: 2 Enter value for name: kkk Enter value for old: 35 Enter value for new: 46 Enter value for total: 0 Enter value for amt: 0 old 1: insert into v115 values(&no,'&name',&old,&new,&total,&amt) new 1: insert into v115 values(2,'kkk',345,700,0,0) 1 row created. SQL> / Enter value for no: 3 Enter value for name: ggg Enter value for old: 25 Enter value for new: 26 Enter value for total: 0 Enter value for amt: 0 old 1: insert into v115 values(&no,'&name',&old,&new,&total,&amt) new 1: insert into v115 values(3,'ggg',500,800,0,0) 1 row created.

SQL> select * from v115; NO NAME OLD NEW ------- ---------- --------- ------1 vvv 45 55 2 kkk 35 46 3 ggg 25 26

TOTAL --------0 0 0

AMT -------0 0 0

SQL> declare 2 amt int; 3 cursor c is select * from v115; 4 f v115% rowtype; 5 begin 6 for f in c 7 loop 8 f.total:=f.new-f.old; 9 if((f.total=0)or(f.total<50))then 10 f.amt:=100; 11 else if((f.total=50)or(f.total<100))then 12 f.amt:=f.total*0.75; 13 else if((f.total=100)or(f.total<150))then 14 f.amt:=f.total*1.50; 15 else 16 f.amt:=f.total*2.50; 17 end if; 18 end if; 19 end if; 20 update v115 set total=f.total,amt=f.amt where no=f.no; 21 end loop; 22 end; 23 / PL/SQL procedure successfully completed. SQL> select * from v115; NO NAME ----- --------1 vvv 2 kkk 3 ggg

OLD ------45 35 25

NEW TOTAL ------- ---------55 10 46 11 26 1

AMT ------100 100 10

PROCEDURE: SQL> create table proc(itemno number(10),stock number(10),price number(10)); Table created. SQL> insert into proc values(&itemno,&stock,&price); Enter value for itemno: 1 Enter value for stock: 200 Enter value for price: 400 old 1: insert into proc values(&itemno,&stock,&price) new 1: insert into proc values(1,200,400) 1 row created. SQL> / Enter value for itemno: 2 Enter value for stock: 355 Enter value for price: 700 old 1: insert into proc values(&itemno,&stock,&price) new 1: insert into proc values(2,355,700) 1 row created. SQL> / Enter value for itemno: 3 Enter value for stock: 275 Enter value for price: 900 old 1: insert into proc values(&itemno,&stock,&price) new 1: insert into proc values(3,275,900) 1 row created. SQL> select * from proc; ITEMNO STOCK PRICE ---------------------------1 200 400 2 355 700 3 275 900 SQL> create procedure proc1(no in number)is 2 value number; 3 begin

4 select stock*price into value from proc where itemno=no; 5 dbms_output.put_line('output is'||value); 6 end; 7 / Procedure created. SQL> set serveroutput on; SQL> declare 2 item1 number(5); 3 begin 4 item1:=&no; 5 dbms_output.put_line('output is'||item1); 6 proc1(item1); 7 end; 8 / Enter value for no: 1 old 4: item1:=&no; new 4: item1:=1; output is 1 output is 80000 PL/SQL procedure successfully completed. FUNCTIONS: SQL> create table func(name varchar(10),rollno number(10)); Table created. SQL> insert into func values('&name',&rollno); Enter value for name: vvv Enter value for rollno: 12 old 1: insert into func values('&name',&rollno) new 1: insert into func values('vvv',12) 1 row created SQL> / Enter value for name: kkk Enter value for rollno: 14 old 1: insert into func values('&name',&rollno) new 1: insert into func values('kkk',14)

1 row created. SQL> / Enter value for name: ggg Enter value for rollno: 16 old 1: insert into func values('&name',&rollno) new 1: insert into func values('ggg',16) 1 row created. SQL> select * from func; NAME ---------vvv kkk ggg

ROLLNO ---------12 14 16

SQL> create function func1(num in number)return 2 character as nam varchar(8); 3 begin 4 select name into nam from func where rollno=num; 5 return nam; 6 end; 7 / Function created. SQL> set serveroutput on; SQL> declare 2 i number(5); 3 begin 4 i:=&i; 5 dbms_output.put_line('output is'||func1(i)); 6 end; 7 / Enter value for i: 12 old 4: i:=&i; new 4: i:=12; output ivvvs PL/SQL procedure successfully completed

SQL> create table vin(name varchar(10),dept varchar(10)); Table created. SQL> insert into vin values('&name','&dept'); Enter value for name: vvv Enter value for dept: cse old 1: insert into vin values('&name','&dept') new 1: insert into vin values('vvv','cse') 1 row created. SQL> / Enter value for name: kkk Enter value for dept: eee old 1: insert into vin values('&name','&dept') new 1: insert into vin values('kkk','eee') 1 row created. SQL> / Enter value for name: ggg Enter value for dept: cse old 1: insert into vin values('&name','&dept') new 1: insert into vin values('ggg','cse') 1 row created. SQL> select * from vin; NAME ---------vvv kkk ggg

DEPT ---------cse eee cse

SQL> create table vin1(dept varchar(10),count number); Table created. SQL> insert into vin1 values('&dept',&count); Enter value for dept: cse Enter value for count: 2 old 1: insert into vin1 values('&dept',&count) new 1: insert into vin1 values('cse',1)

1 row created. SQL> / Enter value for dept: eee Enter value for count: 1 old 1: insert into vin1 values('&dept',&count) new 1: insert into vin1 values('eee',2) SQL>select * from vin1; DEPT ------cse eee

COUNT ---------2 1

SQL> create trigger vin2 after insert on vin1 for each row 2 begin 3 update vin1 set count=count+1 where dept=:new.dept; 4 end; 5 / Trigger created. SQL> insert into vin values('ppp','eee'); 1 row created. SQL> update vin1 set count=2 where dept='eee'; 1 row updated. SQL> select * from vin1; DEPT --------cse eee

COUNT --------2 2

SQL> create trigger www after delete on vin for each row 2 begin 3 update vin1 set count=count-1 where dept=:old.dept; 4 end; 5 /

Trigger created. SQL> delete from vin where dept='eee'; 2 rows deleted. SQL> select * from vin; NAME DEPT ---------- -------vvv cse kkk cse SQL> select * from vin1; DEPT -------cse eee

COUNT ---------2 0

SQL> edit Wrote file afiedt.buf 1 create trigger ghh1 before update on vin1 for each row 2 declare 3 n number; 4 begin 5 n:=:new.count; 6 if(n<0) 7 then 8 raise_application_error(-20001,'no should not be negative'); 9 end if; 10* end; SQL> / Trigger created. SQL> update vin1 set count=-1 where dept='eee'; update vin1 set count=-1 where dept='eee' * ERROR at line 1: ORA-20001: No should not be positive ORA-06512:at “SCOTT.GHH1”,line 7 ORA-04088:error during execution of trigger ‘SCOTT.ghh1’