Ad:

Oracle Discussion Thread
Unsolved
Views: 1327

Oct 13th, 2009

trigger help pls....!!!!

Expand Post »

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) );

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;

Similar Threads

windows xp installation error!! pls help! in Troubleshooting Dead Machines
Help me remove Online Poker Pop up pls in Viruses, Spyware and other Nasties
hi friends pls help me out in C++
pls.help me im begging you!!! in C
pls... i need help!!! in C

john_beginner

Reputation Points: 9

Solved Threads: 0

Light Poster

Offline

42 posts
since Mar 2009

Permalink

Oct 13th, 2009

Re: trigger help pls....!!!!

Try using select into

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.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Oct 14th, 2009

Re: trigger help pls....!!!!

Click to Expand / Collapse Quote originally posted by cgyrob ...

Try using select into
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 !!!

john_beginner

Reputation Points: 9

Solved Threads: 0

Light Poster

Offline

42 posts
since Mar 2009

Permalink

Oct 14th, 2009

Re: trigger help pls....!!!!

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

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

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

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Oct 14th, 2009

Re: trigger help pls....!!!!

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

( i am using oracle 8i )

john_beginner

Reputation Points: 9

Solved Threads: 0

Light Poster

Offline

42 posts
since Mar 2009

Permalink

Oct 14th, 2009

Re: trigger help pls....!!!!

try assigning to a variable then select into

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;

Last edited by cgyrob; Oct 14th, 2009 at 11:06 am.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Oct 15th, 2009

Re: trigger help pls....!!!!

i have tried above code also but still the result is same, anyways

thanx for u r help bt i have tried some other code

n my query is solved nw !!!

john_beginner

Reputation Points: 9

Solved Threads: 0

Light Poster

Offline

42 posts
since Mar 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: subtract 2 column value

Next Thread in Oracle Forum Timeline: INSERT sql statement for DateTime

DaniWeb Message

Cancel Changes

User Name
Password