tag:blogger.com,1999:blog-7849367040589270673.post91054163418800929..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Get rid of mutating table trigger errors with the compound triggerSteven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger23125tag:blogger.com,1999:blog-7849367040589270673.post-15825933704119770832020-08-12T22:12:25.901-07:002020-08-12T22:12:25.901-07:00Thanks Steven for this nice article. Appreciated!!...Thanks Steven for this nice article. Appreciated!!Ashok Choubeyhttps://www.blogger.com/profile/11106628577495707020noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-47660147133609299482020-08-04T09:54:31.067-07:002020-08-04T09:54:31.067-07:00yes that make sense.... Thanks for detailed reply ...yes that make sense.... Thanks for detailed reply :)<br /><br />I came across above trigger example where table already have some duplicate values and after certain point requirement has been raised for no more duplicates on the table.....<br />so I thought that trigger is reading table before execution of dml transaction so that could be the reason I am not getting MTEs....<br />Mangeshhttps://www.blogger.com/profile/03864846746822541695noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-31931015984338945412020-08-03T11:00:22.340-07:002020-08-03T11:00:22.340-07:00Not really.
Are you wondering why you do not get...Not really. <br /><br />Are you wondering why you do not get a mutating table error since you are querying from the table in a row level insert trigger?<br /><br />That's because MTEs do not occur when doing single row inserts.<br /><br />Generally though I don't see why you would want to use logic like this. Just create a unique index and let the database do the checking for you.Steven Feuersteinhttps://oracle.com/plsqlnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-14372131955061950952020-08-01T10:25:38.710-07:002020-08-01T10:25:38.710-07:00Hi Steven,
Please check out my script and let me k...Hi Steven,<br />Please check out my script and let me know<br />https://livesql.oracle.com/apex/livesql/s/kghwvq23z0z0m7wrum6pp4m6x<br />does it make sense?<br />Mangeshhttps://www.blogger.com/profile/03864846746822541695noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-65285556631476117482020-07-31T06:38:23.788-07:002020-07-31T06:38:23.788-07:00Sounds interesting.
I will be able to give you m...Sounds interesting. <br /><br />I will be able to give you my thoughts when you provide a script on livesql.oracle.com demonstrating your idea!Steven Feuersteinhttps://oracle.com/plsqlnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-51993652469777919132020-07-30T13:27:36.327-07:002020-07-30T13:27:36.327-07:00Nicely explained Steven
Can you please add your th...Nicely explained Steven<br />Can you please add your thoughts for below<br />When we use before timing in row level trigger and try to read the table from which the trigger was fired mutating error will not occur, what do you think about it?Mangeshhttps://www.blogger.com/profile/03864846746822541695noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-58737057865436709172020-04-29T06:24:51.783-07:002020-04-29T06:24:51.783-07:00I don't immediately see how a compound trigger...I don't immediately see how a compound trigger would help with workflow logic. <br /><br />Perhaps a reader of this post will have other ideas.Steven Feuersteinhttps://oracle.com/plsqlnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-920554871966939212020-04-28T10:55:35.336-07:002020-04-28T10:55:35.336-07:00Hi Steven,
Can we implement multi level approval l...Hi Steven,<br />Can we implement multi level approval logic using compound level triggers? Like for am issue raised it needs to go to 3 approvers via email and every approver once approves, it would pass to next approvers?<br />This is an actually requirement so wondering if compound triggers would helpPragyahttps://www.blogger.com/profile/06290299987712889959noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-32994951195839404662020-01-17T02:37:03.862-08:002020-01-17T02:37:03.862-08:00Actually, one is a statement level trigger and the...Actually, one is a statement level trigger and the other is row level.Steven Feuersteinhttp://oracle.com/plsqlnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-1010667531027184552020-01-17T02:30:49.999-08:002020-01-17T02:30:49.999-08:00Hi,
How can you be sure these two triggers will f...Hi, <br />How can you be sure these two triggers will fire in desired order? Both of them are BEFORE INSERT OR UPDATE. <br />Kind regardsAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-14004223432806721122019-11-26T11:27:55.007-08:002019-11-26T11:27:55.007-08:00Hello Steven Below is my user story. posting here...Hello Steven Below is my user story. posting here as the livesql link all the notes got appended. <br /><br /><br /> I got a column on the EMP table as DEPT_NAME which has 10 employees in the same department. I am adding a new column LAST_DAY which can have either Y/N. <br /><br />1) When new record is inserted LAST_DAY value should be 'Y' . <br />2) If a matching department name exists for that employee and if the last_day for that deparment is 'Y' then new record shoudd get the LAST_DAY value as 'Y'.<br />3) If a matching department name exists for that employee and if the last_day for that deparment is 'N'then new record shoudld get the LAST_DAY value as 'N'.<br />4) If few records are 'Y' and 'N' on any depart_name then LAST_DAY should default to 'Y'.<br /><br />For Updating:<br /><br />1) updating new field LAST_DAY from 'Y' to 'N' then all the records which are in that same department should also flip to 'N'.<br />2) updating new field LAST_DAY from 'N' to 'Y' then all the records which are in that same department should also flip to 'Y'.Ronnenoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-53157193020678536572019-11-26T11:10:30.742-08:002019-11-26T11:10:30.742-08:00Thank you very much for the link Steven . I starte...Thank you very much for the link Steven . I started reading your blogs and they are just like bible for pl/sql . You are helping a lot to people like us who started this journey. <br /><br />Here is the link <br /><br />https://livesql.oracle.com/apex/livesql/s/jatca3n0hd7iop3cxiew5mzvrAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-32679616829430795882019-11-26T10:45:06.763-08:002019-11-26T10:45:06.763-08:00I hope it goes smoothly! My suggestion is to creat...I hope it goes smoothly! My suggestion is to create an unlisted script on LiveSQL.oracle.com. You can then send me the link and I can take a look.Steven Feuersteinhttps://oracle.com/plsqlnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-67206575205591746342019-11-26T10:34:41.696-08:002019-11-26T10:34:41.696-08:00Hi Steven,
I am pretty new to pl/sql world and i ...Hi Steven,<br /><br />I am pretty new to pl/sql world and i am looking for help to write a compound trigger for a user story. Can you please let me know how to proceed like where do i start posting my scenarios and the code i have written so far.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-79993394249916108492019-11-14T22:50:38.483-08:002019-11-14T22:50:38.483-08:00Very good, thanks! for the above.Very good, thanks! for the above.Saihttps://www.blogger.com/profile/12604651278630172834noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-32383200378997406312019-10-31T05:04:47.098-07:002019-10-31T05:04:47.098-07:00Very good point, Denis. Yes, I would say that if y...Very good point, Denis. Yes, I would say that if you were going to copy my code and use it in your own application for this requirement, you should make sure to adjust salaries of all other employees, as well.StevenFeuersteinhttps://www.blogger.com/profile/13931412532278395973noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-30306838054412584282019-10-31T03:31:55.849-07:002019-10-31T03:31:55.849-07:00Very good example on COMPOUND TRIGGERS! Thanks, St...Very good example on COMPOUND TRIGGERS! Thanks, Steven!<br /><br />Only should notice that the solution is maybe incomplete for the given task - it deals with the new inserts of bigger salaries, but it doesn't with new inserts of smaller ones. I mean, what if the new salary of an employee is the new MINIMAL salary? Shouldn't we adjust salaries of all other employees then for them to be not greater than 25x of the new minimal salary?Denis Savenkohttps://dsavenko.me/noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-59018839053653269112019-09-05T11:48:30.804-07:002019-09-05T11:48:30.804-07:00I am happy to help you, but this doesn't reall...I am happy to help you, but this doesn't really give me enough to work with. I suggest you work up an example and post it on LiveSQL.oracle.com. You can make it unlisted. Then post the link here or send it to me steven dot feuerstein at oracle dot com, and I will take a look.StevenFeuersteinhttps://www.blogger.com/profile/13931412532278395973noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-33569412040170777152019-09-04T22:43:37.541-07:002019-09-04T22:43:37.541-07:00None of these worked for me. My triggers task is t...None of these worked for me. My triggers task is to fill a stock when it is empty. But it fills before the stock could get the 0 value. And I have a procedure which tries to buy up all items on the stock, while it is not 0. This way your solutions cause me infinite loop.aahahttps://www.blogger.com/profile/10121825538116862925noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-8716696058715170062019-05-06T05:20:33.106-07:002019-05-06T05:20:33.106-07:00Zahar,
I am so sorry that I did not reply to thi...Zahar, <br /><br />I am so sorry that I did not reply to this earlier.<br /><br />Yes, for this requirement your code is a simpler and perfectly adequate solution!Steven Feuersteinhttps://twitter.com/sfonplsqlnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-75428578969957446812019-05-06T05:10:12.564-07:002019-05-06T05:10:12.564-07:00This approach is also fine.. This approach is also fine.. Vinish Kapoorhttps://www.foxinfotech.in/noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-67961402033932564762019-01-03T05:58:53.450-08:002019-01-03T05:58:53.450-08:00Steven,
Compound trigger is definitely the best op...Steven,<br />Compound trigger is definitely the best option for this problem. At the same time, why package based solution has to be so complex? What do you think about the following approach:<br /><br />CREATE TABLE emp AS SELECT * FROM scott.emp;<br /><br />CREATE OR REPLACE PACKAGE pkg_emp_cache AS <br /> max_sal emp.sal%TYPE; <br />END;<br />/<br /><br />CREATE OR REPLACE TRIGGER trg_bef_ins_upd_emp <br />BEFORE INSERT OR UPDATE ON emp <br />BEGIN <br /> SELECT 25*MIN(sal) INTO pkg_emp_cache.max_sal <br /> FROM emp; <br />END; <br />/<br /><br />CREATE OR REPLACE TRIGGER trg_bef_ins_upd_row_emp <br />BEFORE INSERT OR UPDATE ON emp <br />FOR EACH ROW <br />BEGIN <br /> :NEW.sal:=LEAST(pkg_emp_cache.max_sal, :NEW.sal); <br /> DBMS_OUTPUT.PUT_LINE('New Salary of ' || :NEW.ename || ' is set to ' || :NEW.sal); <br />END; <br />/<br /><br />Now, let's test it:<br /><br />--Single row update:<br /><br />UPDATE emp <br />SET sal = 30000 <br />WHERE ename = 'KING'<br /><br />1 row(s) updated.<br />New Salary of KING is set to 20000<br /><br />--Multiple row update:<br /><br />UPDATE emp <br />SET sal = 10*sal <br />WHERE deptno=30<br /><br />6 row(s) updated.<br />New Salary of BLAKE is set to 20000<br />New Salary of ALLEN is set to 16000<br />New Salary of WARD is set to 12500<br />New Salary of MARTIN is set to 12500<br />New Salary of TURNER is set to 15000<br />New Salary of JAMES is set to 9500Zahar Hilkevichhttps://www.blogger.com/profile/08009262994923267604noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-57785245955543084762018-07-10T09:13:50.081-07:002018-07-10T09:13:50.081-07:00won't this give a recursive trigger errors.
be...won't this give a recursive trigger errors.<br />because the driving dml is a update and the trigger also contains update on same table employees. Souravhttps://www.blogger.com/profile/06763410400255685554noreply@blogger.com