Solved

Oracle trigger

Posted on 2013-01-17
15
626 Views
Last Modified: 2013-01-17
I have never created a trigger before in Oracle. I need to update a column when a record gets inserted or updated to be Upper Case. This is what I want to run on Insert/Update

UPDATE OMNI_COMMOM_LOOKUP SET LOOKUP_VALUE_TEXT=UPPER(LOOKUP_VALUE_TEXT) WHERE LOOKUP_KEY LIKE 'TRANSIT_TYPE_%';

I want this done After Update/Insert but when I try to create it I get a message that says it compiles but with warnings. Im using TOAD but cant figure it out. I put the Update statement in the Body tab and click the appropriate check boxes for Insert Update and After but I still cant figure it out.

Please walk me through step by step to create this trigger

Thanks
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38787817
Untested, just typed in but try something like:

create or replace trigger OMNI_COMMOM_LOOKUP_TRIG
after insert or update on OMNI_COMMOM_LOOKUP
for each row
begin
:new.LOOKUP_KEY  := upper(:new.LOOKUP_KEY);
end;
/
0
 

Author Comment

by:jknj72
ID: 38787879
I ran what you sent me I got the following error.

create or replace trigger ANET.TRG_OMNI_COMMOM_LOOKUP
after insert or update on ANET.OMNI_COMMOM_LOOKUP
for each row
begin
:new.LOOKUP_KEY  := upper(:new.LOOKUP_KEY);
end;
Error at line 2
ORA-00942: table or view does not exist


Also, Im trying to Update the LOOKUP_VALUE_TEXT field not LOOKUP_KEY. No idea why this is happening, any idea?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38787904
>>trying to Update the LOOKUP_VALUE_TEXT field not LOOKUP_KEY

Then change the column name.

>>ORA-00942: table or view does not exist

Either the user you are logged into the database with doesn't have permissions to see ANET.OMNI_COMMOM_LOOKUP or the table really doesn't exist.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jknj72
ID: 38788155
hahah, nice answser!!! I just wanted to make sure that I was updating the correct column...

Anyway, I got this error now.

Compilation (1: 26): ORA-04084: cannot change NEW values for this trigger type
0
 

Author Comment

by:jknj72
ID: 38788158
This too when using TOAD tocreate trigger....

Error at line 3
ORA-04082: NEW or OLD references not allowed in table level triggers
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38788165
>>ORA-04082: NEW or OLD references not allowed in table level triggers

Make sure you have 'for each row'.

>> Compilation (1: 26): ORA-04084: cannot change NEW values for this trigger type

Change it to a before update/insert trigger.
0
 

Author Comment

by:jknj72
ID: 38788274
ok that worked. I just need to script this out for the DBAs. When I script other objects I always include a Synonym and Grants, do I have to worry about this with a Trigger?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38788289
>>do I have to worry about this with a Trigger?

The only think I can think of and I'm not even sure of that is if, for some odd reason, the trigger is on tables that aren't in the same schema as the trigger and that schema doesn't already have the necessary rights.
0
 

Author Comment

by:jknj72
ID: 38788297
Also, Where is the Where Clause that I need in the Trigger Update?
0
 

Author Comment

by:jknj72
ID: 38788300
ok Thanks. I just need to set the Where clause and Im good to go!!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38788311
>>Also, Where is the Where Clause that I need in the Trigger Update?

What 'where' clause?  A trigger fires 'for each row' it is passed.  This is determined by the DML statement issued against the base table.
0
 

Author Comment

by:jknj72
ID: 38788318
ok maybe im a little confused. I only want this to run if the
WHERE LOOKUP_KEY LIKE 'TRANSIT_TYPE_%';


This table is used for multiple apps and I only want this to run for these KEYS
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38788342
That where clause is part of the update statement that fires the trigger.

As you saying you only want to make LOOKUP_VALUE_TEXT upper case if LOOKUP_KEY LIKE 'TRANSIT_TYPE_%'?

If so, just add an if statement to the trigger.

...
if :new.LOOKUP_KEY LIKE 'TRANSIT_TYPE_%' then
   :new.LOOKUP_VALUE_TEXT:= upper(:new.LOOKUP_VALUE_TEXT);
end if;
...
0
 

Author Comment

by:jknj72
ID: 38788398
ok that worked great, thanks for the help
0
 

Author Closing Comment

by:jknj72
ID: 38788403
thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question