Home » SQL & PL/SQL » SQL & PL/SQL » update the char column to upper after insert or update to the same column (ORACLE DATABASE & DEVELOPER 10g , WINDOWS7)
update the char column to upper after insert or update to the same column [message #418063] Wed, 12 August 2009 17:08 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I have Persons table:

http://img35.imageshack.us/img35/1642/21732847.png

and I need to assure that the NAME column is always in UPPER case , so I did as it illustrated in this link:
http://asktom.oracle.com/tkyte/Mutate/

I did that :-
==================================

CREATE OR REPLACE TRIGGER parent_bi
BEFORE INSERT OR UPDATE
ON persons
BEGIN
state_pkg.newrows := state_pkg.empty;
END;
/


CREATE OR REPLACE TRIGGER parent_aifer
AFTER INSERT OR UPDATE
ON PERSONS
FOR EACH ROW
BEGIN
state_pkg.newrows (state_pkg.newrows.COUNT + 1) := :NEW.ROWID;
END;
/


CREATE OR REPLACE TRIGGER HR.PARENT_AI
AFTER INSERT OR UPDATE
ON HR.PERSONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
FOR i IN 1 .. state_pkg.newrows.COUNT
LOOP
UPDATE persons
SET NAME = UPPER (NAME)
WHERE persons.ROWID = state_pkg.newrows (i);
END LOOP;
END;
/


======================================

but nothing happen , is as the same as these triggers not exist.

So, What's went wrong ??!!!!
and how I workaround to do this statement :-
UPDATE persons SET NAME = UPPER (NAME) ;

every time I update or insert into Name Column?
Re: update the char column to upper after insert or update to the same column [message #418065 is a reply to message #418063] Wed, 12 August 2009 17:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
CREATE TABLE suppliers
( 	supplier_id 	numeric(4), 	
	supplier_name 	varchar2(50), 	
	CONSTRAINT check_supplier_name
	CHECK (supplier_name = upper(supplier_name))
);
Re: update the char column to upper after insert or update to the same column [message #418066 is a reply to message #418065] Wed, 12 August 2009 18:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Or simply one single trigger:

CREATE OR REPLACE TRIGGER HR.PARENT_AI
 before INSERT OR UPDATE ON HR.PERSONS
 FOR EACH ROW
BEGIN
 :NEW.NAME = UPPER (:NEW.NAME)
END;
/


Which will do what you want for all new/updated records.

You will have to update existing records by hand once with.

UPDATE persons SET NAME = UPPER (NAME);
Re: update the char column to upper after insert or update to the same column [message #418067 is a reply to message #418063] Wed, 12 August 2009 19:10 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
previous post had some syntax errors
CREATE OR REPLACE TRIGGER HR.PARENT_AI
 before INSERT OR UPDATE ON HR.PERSONS
 FOR EACH ROW
BEGIN
 :NEW.NAME := UPPER (:NEW.NAME);
END;
/
Re: update the char column to upper after insert or update to the same column [message #418456 is a reply to message #418063] Sat, 15 August 2009 13:42 Go to previous message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

thanks a lot for all of you.
Previous Topic: trim function to all char columns unix , oracle 10g (Merged)
Next Topic: date question
Goto Forum:
  


Current Time: Sat Dec 03 22:23:28 CST 2016

Total time taken to generate the page: 0.06058 seconds