SQL & PL/SQL :: Mutating Trigger While Updating Original Table
Feb 18, 2013
I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.
Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".
But however I am still facing the same issue.
Test case:
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID VARCHAR2(1),
STATUS NUMBER(9)
);
INSERT INTO EMPLOYEE VALUES ('A',1);
INSERT INTO EMPLOYEE VALUES ('B',1);
commit;
[code]....
Also, any alternate options (rather than using 2 triggers with temp table).
View 7 Replies
ADVERTISEMENT
Jun 21, 2010
How can we overcome mutating trigger error using global temporary table.
Suppose if we use the following trigger we will get mutating trigger error.
CREATE OR REPLACE TRIGGER t1
AFTER INSERT ON emp
FOR EACH ROW
DECLARE
BEGIN
UPDATE emp SET sal=sal+100;
END;
/
View 27 Replies
View Related
Jul 4, 2013
Table A basically has 4 rows of interest, an outside event changes/inserts let's say row 1.
Row 2 and 3 have to be changed in a trigger depending on the new value of 1.(or not if the value stays the same)
Row 4 is the reference for the update and will not be changed.
My first simple AFTER INSERT or UPDATE trigger obviously failed because of the mutating table error.
View 19 Replies
View Related
Jun 13, 2012
I have created a trigger which gets executed whenever there is DML operation happens on it. I am getting the below error. ORA-04091: table RTS_SCHEMA.TBL1 is mutating, trigger/function may not see it...the tigger is on table tbl1 and the structure of the table is
CREATE TABLE TBL1
(
SLNO NUMBER NULL,
DES VARCHAR2(20 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
[code]...
View 5 Replies
View Related
Oct 20, 2011
Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table. I have the trigger and it works except it is not loading my col1/PK values. I understand I need to do a new/old value. Col1 is my PK on Table that I want to load anytime there is an update/delete/insert on the table. How do I code the old/new variable?
My
CREATE OR REPLACE TRIGGER TRIGGER_NAME
AFTER INSERT OR UPDATE OR DELETE
ON TABLE_NAME
FOR EACH ROW
DECLARE
v_col1 TABLE_NAME.COLUMN%TYPE;
BEGIN
[code]...
View 5 Replies
View Related
Sep 12, 2013
I have created the following trigger whcich will track all the column changes and insert the row in log table. here i have some doubt while substituting the cursor value.
create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);
[Code]..
are the below assignement of values will work ?
in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;
i want to take the coulmname from the cursor and assign tat to psuedo columns like :new.r.column_name .
View 8 Replies
View Related
Apr 27, 2011
EBS version is 12.1.3. we need to update the e-mail address in per_all_people_f in Oracle EBS based on a trigger that will fire against a column in Oracle Internet Directory (OID). A trigger will be built against table ct_mail, column attrvalue. When a value is inserted or updated this value will use an api on the EBS database to update per_all_people_f.
Q => Is ct_mail the correct table to use in OID for email addresses? or can you confirm a different table?
Q => If Ok to use ct_mail as the table can you see any problems with using a trigger in OID?
Q => Finally is a database link between OID and EBS database the best way for the trigger to work?
View 2 Replies
View Related
Nov 30, 2009
my simplified database schema is:
Order
OrderID
Status
OrderItems
OrderID
EAN
Amount
Store
EAN
Amount
now,I need trigger that will on updating table Order and changing status to "GoodsReceived" increase amounts in Store according to values in OrderItems.
create or replace trigger order_received_trigger
before update
on Order
for each row
begin
if (:old.status=4 and :new.status=1) then
/*
select ean, sum(amount)
from OrderItems
where OrderID=:old.OrderID
group by ean;
*/
end if;
end;
but now i dont know how to apply that select on table Store.
View 2 Replies
View Related
May 16, 2011
Best solution to avoid mutating table error?
View 11 Replies
View Related
Apr 11, 2013
Oracle version: 10g express.
case study: one class has many students, only one student has reward.
original design:
drop table student;
drop table class;
[Code]...
questions:
1. If I want insert into student values('stu2','cls1','yes'); I can put a trigger on student to check whether it has only one student in class has reward. This trigger should tell me error: I can not let 'stu2' has 'yes' on reward column. But it will lead Oracle error: ora-04091: mutating table. It seems use "after" or "instead of" trigger can solve this problem, so how to do it?
2. Another way to make sure only one student in a class has reward is change design as:
drop table student;
drop table class;
create table class(
clsid varchar2(9) primary key,
reward_stuid varchar2(9));
create table student(
stuid varchar2(9) primary key,
clsid varchar2(9)
not null references class(cid) on delete cascade);But the question is these two tables has foreign key from each other, is it a good design?.
View 7 Replies
View Related
May 14, 2012
I am trying to recreate a table after I have renamed the original.
DECLARE
v_prd_detail_seq NUMBER;
v_orig_ddl CLOB;
[Code].....
View 6 Replies
View Related
Feb 14, 2012
i am not able to rename a column for original table, once i create a new one with Create table as select i can rename the new table column, but not the original one
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 13:10:10 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> show user
USER is "WEBSYSTEMD6"
SQL> alter table cbe rename column tnum to tnum_old;
alter table cbe rename column tnum to tnum_old
*
ERROR at line 1:
ORA-00900: invalid SQL statement
SQL> create table cbe_test as select * from cbe;
Table created.
SQL> alter table cbe_test rename column tnum to tnum_old;
Table altered.
SQL>
View 12 Replies
View Related
Aug 13, 2013
I have 20 or so tables, partitioned by range, indexed etc...soon these tables will be gone and i have to recreate them with the same definitions. I have to write a procedure(or script, it's up to me) which must:
1- check if table A exist in the scheme
2- if not create the table as the original ddl.
for 1 i've used smth like
-------------------------
SELECT count(*)
INTO a
FROM user_objects
WHERE object_name='A';
[code]....
so my question is how and what is the cleanest and fastest way to do this.? it's a lot DDLs,
View 17 Replies
View Related
Mar 28, 2013
what is the the use of views and synonyms if changes made to them affects the original table contents?It is data insecurity know?
View 2 Replies
View Related
Jul 11, 2013
I have two different tables having similar structure but data is coming from different source.finally i want to update the view so that the it should affect the base table.
create table emp1 as
select empno,ename,job,deptno,sal from emp where deptno=10;
create table emp2 as
select empno,ename,job,deptno,sal from emp where deptno=20;
create view emp_view as select * from emp1 union all select * from emp2;
[code].......
View 13 Replies
View Related
Apr 16, 2013
I am learning oracle trigger, i have one query.
Can i increment the old column value in trigger.
eg: :new.cid := :old.cid+1;
is this is correct.
View 4 Replies
View Related
Feb 21, 2012
I get a "ORA-00922 missing or invalid option" after compiling the following trigger:
create or replace
trigger aifer_transfer
after insert on transfer
for each row
[code]......
I want the trigger to affect the transferring account (from_account) with minus the "amount" being transferred from it, and the account transferred to (to_account) with plus the "amount" being transferred to it. My tables looks as following:
TRANSFER
----------------------------------------- -------- ------------
ROWNR NOT NULL NUMBER(9)
PNO NOT NULL VARCHAR2(11)
FROM_ACCOUNTNO NOT NULL NUMBER(8)
TO_ACCOUNTNO NOT NULL NUMBER(8)
AMOUNT NUMBER(10,2)
DATE NOT NULL DATE
ACCOUNT Null? Type
----------------------------------------- -------- ------------
ACCOUNTNO NOT NULL NUMBER(8)
KTNR NOT NULL NUMBER(6)
REGDATE NOT NULL DATE
BALANCE NUMBER(10,2)
View 5 Replies
View Related
Jul 15, 2013
I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.
The block has 5 items as follows:
1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'
The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.
I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.
Consider this scenario...
The block has 3 records.
record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.
I check the RECORD_STATUS checkbox on record2.
(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).
I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.
I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.
(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)
How can i stop this from happening?
View 1 Replies
View Related
Jul 5, 2010
I have a non-base-table item which I want to update in the pre-update trigger of the current block.
If the content of the field exceeds 4000 characters, i get the error message
ORA-01461: can bind a LONG value only for insert into a LONG column.
The code is
update tab set long_col = :formsblock.long_col
where tab.tabpk = :formsblock.foreign_tabpk;
Workarounds would be,
1.) to delete the old dataset and insert the new one:
delete from tab
where tab.tabpk = :formsblock.tabpk;
insert into tab (tabpk, long_col) values
(:formsblock.foreign_tabpk, :formsblock.long_col);
or 2.) to change the Item from a non-database item to a database item and use the internal update of the forms-module, but both workarounds are not very satisfying.
Do you know another way to update the LONG-column within the pre-update trigger (or any other PL/SQL part of forms)?
View 2 Replies
View Related
Dec 7, 2010
I am trying to create and update query to use as a trigger for updating information in a database which is dependent upon time.
I have a severity level which needs to be decreased in value after a set amount of time. the time of the action is recorded automatically on the creation of the the record. after an hour anything at severity level 2 needs to go up to 1, after 2 hours level 3 goes up to 2 and 3 hours level 4 goes up to 3
so far this is what I have
UPDATE calllog
SET timedate = current_timestamp, severityid = severityid - 1
WHERE ((severityid = 2 and timedate >= current_timestamp + (1/24)) or
(severityid = 3 and timedate >= current_timestamp + (2/24)) or
(severityid = 4 and timedate >= current_timestamp + (3/24)))
I am not getting any errors back from this sql but it is not updating the relevent information. I am using one cell for time and date in format = DD-MON-YYYY HH24:MM:SS.
View 3 Replies
View Related
Sep 2, 2005
What is Mutating error,have u faced it, I have faced this question so many time in interviews but i have not facing this problem till now.
View 15 Replies
View Related
Oct 20, 2013
1.I have created table emp_log using structure of emp table
create table emp_log
as select * from emp
where 1=2;
2.Now I have added some new fields i.e
new_sal(updated salary) ,
upd_by (who updated),
upd_date (Salary update date)
alter table emp_log
add (new_sal number, upd_by varchar2(20),upd_date date);
3. I have made following trigger to insert old and new values (for salary,user and date) in emp_log table whenever I update sal for particular employee in emp table and at the same time i am selecting the updated value of sal into lv_sal variable which will cause mutating error.
create or replace trigger emp_trg
after update of sal on emp
for each row
declare
[code]...
4. Suppose sal of emp is 1000:-
update emp
set sal=sal+100
where empno=7369;
>select * from emp_log;
output:- sal= 1000 new_sal=1100
[code]...
Now the question starts if i commment pragma autonomous_tansaction and commit statement then it gives mutating error because at the same time i am updating and selecting value of sal from emp table.
create or replace trigger emp_trg
after update of sal on emp
for each row
declare
[code]...
Questions:-
1.Is pragma autonomous_transaction handling the mutating error and if yes then how?
2.How many ways are there to handle mutating error.
3.What is the exact definition of Mutating Error.
4.An example or query to use lv_sal variable for printing new updated sal.
5.What is Mutating Table , Is it similar to mutating error (Found this topic while searching for mutating error).
View 8 Replies
View Related
Feb 24, 2010
I am unable to understand why row level triggers cant be used in mutating tables.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
View 1 Replies
View Related
Nov 19, 2008
I am using oracle 9i, and having trouble with updating a table.
I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.
why this error occurs or why this error did not occur on my PC,
[Update sql](key is CD and SDATE)
Update TBL1
set CD = 'A',
[Code]....
View 4 Replies
View Related
Jun 27, 2011
I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:
LOAD DATA
INFILE 'table1.dat'
INFILE 'table2.dat'
APPEND
INTO TABLE table1
WHEN tid='1'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(pid "pid.nextval", p_fname, p_lname, tid)
[code]....
The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:
table1.dat
,John,Doe,1
,Joe,Smith,1
table2.dat
10,,abc,1,
11,,xyz,1,
The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.
View 1 Replies
View Related
Nov 1, 2011
I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.
DECLARE
conditionXML CLOB;
ls_xml_2 Clob;
ls_xml_3 clob;
ls_xml_4 Clob;
ls_xml_5 Clob;
ls_xml_6 clob;
ls_xml_7 Clob;
[code]...
View 1 Replies
View Related
Aug 25, 2011
pgit_policy is transaction table having producer code field.
pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.
I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.
update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')
View 4 Replies
View Related
Oct 22, 2010
how many rows certain tables have.
updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?
when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).
Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?
View 3 Replies
View Related
Mar 17, 2011
DROP TABLE TESTING CASCADE CONSTRAINTS;
CREATE TABLE TESTING
(
DATAPERIOD DATE,
EMPLID VARCHAR2(20 BYTE),
B_OS_REASON VARCHAR2(9 BYTE)
)
TABLESPACE USERS;
[Code] ........
SQL> select * from testing order by 1;
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972
In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972 XXXXXX
View 5 Replies
View Related
Jun 8, 2010
There are two tables:
create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);
im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.
here is what i've done so far:
drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);
What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist
View 4 Replies
View Related