Or by making the trigger autonomous... But it has some consequences - 1) rollback of the main transaction (in this scenario insert into emp_t) will not apply on changes committed in the trigger body 2) Deadlock may occur (won't happen in this case) if trigger transaction tries to apply some changes on the records blocked by the triggering transaction.
Awsome explanation bro..very helpful...Pls make a video on advanced topics like pipeline and deterministic function, nocopy hint, pragma serially reusable, pragma restrict reference, partition, explain plan and performance tuning
Sure, Pragma serially reusable i have already posted, rest of the topics i will add itto upcoming lists. You can watch the serially reusable video here th-cam.com/video/LwW0k7AN4Bk/w-d-xo.html
Hi sir thank you for sharing valueble content frequently,please keep sharing its very helpful us thank you somuch sir,please make some performance tuning concepts step by step asap,and what is parse and dense in collections?
Sure, Please stay tuned for more videos, dense collection will have all the datas continuously in the nested table., incase any element is deleted in between then its sparse.
@@SivaAcademy thank you sir..i am writing for next video sir,pls make performance tuning videos asap sir..now a days interviewers aking that frequently..how to analyse,identify the query,check the cause like that..how to reduce cost of the query and make a video debugging concept
for is used to iterate one by one value forall is used as part of bulkbinding to load all the values from collection into table in single context switch. For more details, please look into this video th-cam.com/video/qH47MCoWoNU/w-d-xo.html
Hi! I have a table named empp and columns (id, name, salary, old_salary, new_salary). I created a trigger in such a way that if I update the salary, :old.salary, :new.salary gets inserted into the same table in the columns old_salary, new_salary. But I get mutating trigger error. can you please help me to solve it? below is my program, create or replace trigger sal before delete or insert or update on empp for each row
begin insert into empp(old_salary, new_salary) values(:old.salary, :new.salary); end; /
begin update empp set salary = salary + 1000; end; / and I get this error ORA-04091: table HR.EMPP is mutating, trigger/function may not see it ORA-06512: at "HR.SAL", line 3 ORA-04088: error during execution of trigger 'HR.SAL'
Sir , BY using PRAGMA AUTONOMOUS_TRANSACTION + TCL Command we can resolve mutating error. CREATE OR REPLACE TRIGGER TRG_MUTATION BEFORE UPDATE OF SAL ON EMP FOR EACH ROW DECLARE LV_MAX_SAL NUMBER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN SELECT MAX(SAL) INTO LV_MAX_SAL FROM EMP; IF :NEW.SAL
The best teacher ever. Your explanation is awesome thank you sir
Not sure whether I deserve this comment, however thank you so much 🙏🙏💐💐
Or by making the trigger autonomous... But it has some consequences - 1) rollback of the main transaction (in this scenario insert into emp_t) will not apply on changes committed in the trigger body 2) Deadlock may occur (won't happen in this case) if trigger transaction tries to apply some changes on the records blocked by the triggering transaction.
you are a very good teacher Salam Teacher you had a new student that respect you like a teacher.
My pleasure bro, thank you 🙏🙏🙏💐💐
So nice May you live log with good health with ur family
Thank you 🙏🙏🙏
🙏🙏Exactly what I was searching for . 👍👍. extremely helpful 😍😍
My pleasure 😊
Very good explanation of mutating trigger error.. thanks for sharing the video.
My pleasure, welcome
🙏🏼🙏🏼 True blessing and learning.
🙏🙏🙏Thank you
NICE ji.Thanks for your help to understand the mutating error and compund trigger
Welcome 🙏 karthik
thank you very much sir, for all the videos.
by putting pragma autonomous transaction in our row level trigger, we can avoid mutating error also.
Welcome bro
Do not fall into the trap of believing that autonomous transactions have removed
mutating triggers as an issue!
If u do so it would break the data integrity
nice value explanation its very help thank u shiva
Welcome 🙏🙏🙏
Awsome explanation bro..very helpful...Pls make a video on advanced topics like pipeline and deterministic function, nocopy hint, pragma serially reusable, pragma restrict reference, partition, explain plan and performance tuning
Sure, Pragma serially reusable i have already posted, rest of the topics i will add itto upcoming lists. You can watch the serially reusable video here
th-cam.com/video/LwW0k7AN4Bk/w-d-xo.html
Sir ,When u updating 150000 the log inserted as failure but it inserted in the emp table
🙏🙏Well very good and 👍👍practical 😍😍approach . nice video
Thanks a lot
Very useful video,Thanks Lot
You are welcome
instead of using a package variable can we use a binding variable? is that possible?
Very useful,thanks for sharing!
Thank you
Hi sir thank you for sharing valueble content frequently,please keep sharing its very helpful us thank you somuch sir,please make some performance tuning concepts step by step asap,and what is parse and dense in collections?
Sure, Please stay tuned for more videos,
dense collection will have all the datas continuously in the nested table., incase any element is deleted in between then its sparse.
@@SivaAcademy thank you sir..i am writing for next video sir,pls make performance tuning videos asap sir..now a days interviewers aking that frequently..how to analyse,identify the query,check the cause like that..how to reduce cost of the query and make a video debugging concept
Sure, please stay tuned for performing tuning videos
What is different between forall and for clause plsql
What is different between forall and bulk bind
for is used to iterate one by one value
forall is used as part of bulkbinding to load all the values from collection into table in single context switch.
For more details, please look into this video
th-cam.com/video/qH47MCoWoNU/w-d-xo.html
Hi! I have a table named empp and columns (id, name, salary, old_salary, new_salary). I created a trigger in such a way that if I update the salary, :old.salary, :new.salary gets inserted into the same table in the columns old_salary, new_salary. But I get mutating trigger error. can you please help me to solve it? below is my program,
create or replace trigger sal
before delete or insert or update on empp
for each row
begin
insert into empp(old_salary, new_salary) values(:old.salary, :new.salary);
end;
/
begin
update empp
set salary = salary + 1000;
end;
/
and I get this error
ORA-04091: table HR.EMPP is mutating, trigger/function may not see it
ORA-06512: at "HR.SAL", line 3
ORA-04088: error during execution of trigger 'HR.SAL'
thank you very much!!!!!!!
Welcome 🙏
Could tell me please why we use pragma autonomous transaction . And where we use in plsql . Please
sure, will post a separate video on autonomous transaction
Siva Academy thanks
How to commit that log record intrigger
use autonomous transaction
Hi sir,
Can you please explain reguexp related topic plz
Sure, please stay tuned.
Have giving trainings also
Yes, please drop mail to siva.k.academy@gmail.com
medarametlasrikanth2@gmail.com
@@SivaAcademy thanks sir
Pls send for and forall clause in plsql
Sir , BY using PRAGMA AUTONOMOUS_TRANSACTION + TCL Command we can resolve mutating error.
CREATE OR REPLACE TRIGGER TRG_MUTATION
BEFORE UPDATE OF SAL ON EMP
FOR EACH ROW
DECLARE
LV_MAX_SAL NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT MAX(SAL) INTO LV_MAX_SAL FROM EMP;
IF :NEW.SAL
Pls send answer s
Detailed answer i will post in separate video, however this video will answer most of your doubts
th-cam.com/video/qH47MCoWoNU/w-d-xo.html
Very useful video,Thanks Lot
You are welcome