| trigger when insert into select [message #607725] |
Tue, 11 February 2014 08:08  |
 |
preethia
Messages: 8
Registered: January 2014
Location: chennai
|
Junior Member |
|
|
when i insert a record in category table it should automatically insert in parameter table. for that i created trigger
---------------
create or replace TRIGGER TRIG_CATEG_TAB
AFTER INSERT ON "SYSTEM.CATEGORY_TAB"
FOR EACH ROW
BEGIN
INSERT INTO "SYSTEM.PARAMETER_TAB" (CATEGORY)
SELECT CATEGORY FROM "SYSTEM.CATEGORY_TAB" C WHERE C.CATEGORY=:NEW.CATEGORY;
END;
------------
when i try to insert a record i'm getting error as
INSERT INTO CATEGORY_TAB (CATEGORY) VALUES ('H')
Error report:
SQL Error: ORA-04091: table SYSTEM.CATEGORY_TAB is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TRIG_CATEGORY_TAB", line 2
ORA-04088: error during execution of trigger 'SYSTEM.TRIG_CATEGORY_TAB'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
|
|
|
|
| Re: trigger when insert into select [message #607726 is a reply to message #607725] |
Tue, 11 February 2014 08:16  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
1) both SYS & SYSTEM schema are reserved for EXCLUSIVE use by Oracle.
2) NEVER make of modify any objects in SYS or SYSTEM schemas without explicit direction from Oracle Corp.
3) do not issue SQL against table upon which the trigger is based (avoids the mutating trigger error)
[Updated on: Tue, 11 February 2014 08:31] Report message to a moderator |
|
|
|
| Re: trigger when insert into select [message #607729 is a reply to message #607726] |
Tue, 11 February 2014 08:27 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're inserting a value into a table
You're then selecting that value from the table where that value = the value you've just inserted.
Or to put it another way - you've already got the value you need to insert, you're using it in the where clause of the select.
So the select is completely pointless isn't it?
And as Blackswan says, never, ever create objects in sys.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
