jknj72
asked on
Oracle trigger
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(LO
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
UPDATE OMNI_COMMOM_LOOKUP SET LOOKUP_VALUE_TEXT=UPPER(LO
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
ASKER
I ran what you sent me I got the following error.
create or replace trigger ANET.TRG_OMNI_COMMOM_LOOKU
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?
create or replace trigger ANET.TRG_OMNI_COMMOM_LOOKU
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?
>>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.
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.
ASKER
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
Anyway, I got this error now.
Compilation (1: 26): ORA-04084: cannot change NEW values for this trigger type
ASKER
This too when using TOAD tocreate trigger....
Error at line 3
ORA-04082: NEW or OLD references not allowed in table level triggers
Error at line 3
ORA-04082: NEW or OLD references not allowed in table level triggers
>>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.
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.
ASKER
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?
>>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.
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.
ASKER
Also, Where is the Where Clause that I need in the Trigger Update?
ASKER
ok Thanks. I just need to set the Where clause and Im good to go!!
>>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.
What 'where' clause? A trigger fires 'for each row' it is passed. This is determined by the DML statement issued against the base table.
ASKER
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
WHERE LOOKUP_KEY LIKE 'TRANSIT_TYPE_%';
This table is used for multiple apps and I only want this to run for these KEYS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok that worked great, thanks for the help
ASKER
thanks
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;
/