Oracle

trigger help pls....!!!!

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Mar 2009

Posts: 27

Reputation: john_beginner is an unknown quantity at this point

Solved Threads: 0

john_beginner john_beginner is offline

Offline

Light Poster

trigger help pls....!!!!

Oct 13th, 2009

hello ,

(begginer of SQL)
i have to make one pro. in which

i have to use trigger which will fire before inserting into table

and convert the name into upper case but i don't what is the prob.

with the code pls provide ur suggetion

my table attributes are
stu (sno number(6),sname varchar(12),class varchar(12) );

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
CREATE OR REPLACE TRIGGER trg27
 
before INSERT ON stu
 
FOR each ROW
 
DECLARE 
 
	sname1 stu.sname%TYPE;
	sno1  stu.sno%TYPE;
	class1 stu.class%TYPE;
 
BEGIN
 
	sno1:=:NEW.sno;
	sname1:=:NEW.sname;
	sname1:=UPPER(sname1);
	class1:=:NEW.class;
 
	EXCEPTION 
 
		WHEN OTHERS THEN 
 
			dbms_output.put_line('Error is occured..!!');
 
	INSERT INTO stu VALUES(sno1,sname1,class1);
 
END;
create or replace trigger trg27

before insert on stu

for each row

declare 

	sname1 stu.sname%type;
	sno1  stu.sno%type;
	class1 stu.class%type;

begin

	sno1:=:new.sno;
	sname1:=:new.sname;
	sname1:=upper(sname1);
	class1:=:new.class;
						
	exception 

		when others then 

			dbms_output.put_line('Error is occured..!!');

	insert into stu values(sno1,sname1,class1);
					
end;

•

Join Date: Sep 2008

Posts: 119

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Oct 13th, 2009

Try using select into

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT :NEW.sno, UPPER(:NEW.sname), :NEW.class
INTO sno1, sname1, class1 FROM dual;
Select :NEW.sno, UPPER(:NEW.sname), :NEW.class
into sno1, sname1, class1 from dual;

Last edited by cgyrob; Oct 13th, 2009 at 6:39 pm.

•

Join Date: Mar 2009

Posts: 27

Reputation: john_beginner is an unknown quantity at this point

Solved Threads: 0

john_beginner john_beginner is offline

Offline

Light Poster

Oct 14th, 2009

•

Originally Posted by cgyrob

Try using select into
Help with Code Tags

sql Syntax (Toggle Plain Text)
SELECT :NEW.sno, UPPER(:NEW.sname), :NEW.class
INTO sno1, sname1, class1 FROM dual;
Select :NEW.sno, UPPER(:NEW.sname), :NEW.class into sno1, sname1, class1 from dual;

my query is , it's not convert the lower case into upper case

i have tried this code also but still the result is same !!!

•

Join Date: Sep 2008

Posts: 119

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Oct 14th, 2009

Really not sure why upper() is not working for you.

Maybe trying using the upper() function on the insert.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
INSERT INTO stu VALUES(sno1,UPPER(sname1),class1);
INSERT INTO stu VALUES(sno1,upper(sname1),class1);

•

Join Date: Mar 2009

Posts: 27

Reputation: john_beginner is an unknown quantity at this point

Solved Threads: 0

john_beginner john_beginner is offline

Offline

Light Poster

Oct 14th, 2009

i have try this code also but i don't know y it's not working

( i am using oracle 8i )

•

Join Date: Sep 2008

Posts: 119

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Oct 14th, 2009

try assigning to a variable then select into

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
sname1 stu.sname%TYPE;
 
sname1:= :new.sname
 
SELECT :NEW.sno, UPPER('sname1'), :NEW.class
INTO sno1, sname1, class1 FROM dual;
sname1 stu.sname%TYPE;

sname1:= :new.sname

SELECT :NEW.sno, UPPER('sname1'), :NEW.class
INTO sno1, sname1, class1 FROM dual;