1 2 Previous Next 16 Replies Latest reply: Jul 19, 2013 11:30 AM by APC RSS

    calling a procedure

    940443

      I have procedure which runs based on the table. when ever a record insert or updates based on the last time stamp column I am calling the procedure and it is running fine. And I want to run the procedure if any record deletes from the tables as well. Here I don't want to maintaining any history of the table.

       

      Can anyone suggest.

        • 1. Re: calling a procedure
          SomeoneElse

          Are you asking about Triggers?

          • 2. Re: calling a procedure
            940443

            no.., here am not using any triggers. I have a configuration table with which I am comparing the latest time stamp in the current table. with this I am calling this procedure

            • 3. Re: calling a procedure
              BluShadow

              So use a trigger then... which triggers on a delete of a record.

              • 4. Re: calling a procedure
                940443

                Any other ideas other than triggers.. because for inserting and updating am using the time stamp as a reference.

                • 5. Re: calling a procedure
                  BluShadow

                  Well, if your record is getting deleted then the only way you're going to know about that record is if you capture the delete using a trigger, otherwise the record isn't there for you to process.

                  That's the point of triggers.  Why don't you want to use a trigger?

                  • 6. Re: calling a procedure
                    APC

                    So what does this procedure actually do?  How is it called?

                     

                    Cheers, APC

                    • 7. Re: calling a procedure
                      Smaipady

                      Hi,

                       

                      Well. How about comparing the record count of the table before/after delete... and then calling the procedure.....But triggers should be a better option...anyways...

                      • 8. Re: calling a procedure
                        rp0428

                        Smaipady wrote:

                         

                        Hi,

                         

                        Well. How about comparing the record count of the table before/after delete... and then calling the procedure.....But triggers should be a better option...anyways...

                        Please explain how that 'solution' would work.

                         

                        Someone INSERTS 5 rows and DELETES 1 row. The ROW count increases by  4. How does '4' indicate how many ROWs were inserted or deleted? Maybe 100 were INSERTed and 96 DELETEd.

                        • 9. Re: calling a procedure
                          Smaipady

                          Hi,

                           

                          Lets say... insert/update/delete is done from some program unit then the suggested solution works as follows.

                           

                          Inside program unit...

                          insert into table x values();

                          select count(*) into o_Cnt

                          from x

                           

                          delete x....

                           

                          select count(*) into n_Cnt

                          from x

                           

                          if o_Cnt!=n_cnt then

                          call proc

                          end if;

                          • 10. Re: calling a procedure
                            APC

                            You're making an awful lot of guesses about how the OP's process works.  All speculation is pointless until they explain their situation in detail.

                             

                            Cheers, APC

                            • 11. Re: calling a procedure
                              rp0428

                              You seem to be forgetting that Oracle supports multiple simultaneous users and those users can perform DML on the same table at the same time.

                               

                              A user running that procedure can NOT see uncommitted DML changes made by other users so any 'count' of rows in a table isn't guaranteed to be accurate.

                               

                              Since your method won't work to begin with I didn't even bother to mention the horrendous performance implications of repeated full table scans just to get a count.


                              • 12. Re: calling a procedure
                                940443

                                The procedure is calling from a java program(every 30 mins) and I am returning rows to the same. and the functionality of this procedure is... to check the if there are any records that are updated/inserted with the timestamps with the time stamp in the configuration table (which updates every 30 mins when ever the java program calls).

                                 

                                Please suggest.. how can I implement this w/o using triggers

                                • 13. Re: calling a procedure
                                  John Spencer

                                  As several people have already said, when a row is deleted you have no easy way to know that after the fact.  It's like the old joke, Raise your hand if you're not here.

                                   

                                  If you are determined to do it without a trigger on deletes, then it will likely take a lot more work.  If I really had to do it, I would "soft delete" the row, that is, add a deleted column to the table, and instead of actually deletng the row, set a value in the deleted column and update the timestamp.  Then, your procedure would query for timestamp between begin and end and deleted is null to get the rows that really were updated, then query again for deleted is not null to get the rows that were deleted.  Presumably, once the procedure has done whatever it needs to do with the deleted rows it would then actually delete them from the table.

                                   

                                  Note that you would then need to modify all of the queries that use the table to exclude rows where the deleted flag is set, and everybody who issues ad-hoc SQL statements against the table would need to know to do the same thing every time otherwise they will get wrong results.

                                   

                                  Personally, I think adding an on delete trigger with whatever logic is required would be easier.  Depending on how your procedure is structured, it might be as simple as putting a call to your existing procedure in the trigger body.

                                  John

                                  • 14. Re: calling a procedure
                                    APC

                                    940443 wrote:

                                     

                                    The procedure is calling from a java program(every 30 mins) and I am returning rows to the same. and the functionality of this procedure is... to check the if there are any records that are updated/inserted with the timestamps with the time stamp in the configuration table (which updates every 30 mins when ever the java program calls).

                                     

                                     

                                    I can't help thinking this is a Java developer's solution: have a routine which reads through the entire table once every half-an-hour to see what's changed.  It doesn't scale, it's not responsive and - as several people have pointed out - doesn't work for deleted rows.

                                     

                                    Whereas a database developer's solution would be to use triggers to maintain a journalling table.  This approach does scale, because it doesn't get more expensive as the taregt table grows; it is responsive, because we can find out what's changed right away; and it supports deletion.

                                     

                                    So you have three choices:

                                     

                                    1. Tell the users they cannot have this functionality.

                                    2. Take John's suggestion of logical "deletion" (actually a special update); but logical deletion is Teh Suck! and will require major re-work to other parts of your application.

                                    2. Use triggers.

                                     

                                    Cheers, APC

                                    1 2 Previous Next