How  to Turn an update into a procedure and update automatically?

sue Tons
sue Tons used Ask the Experts™
I can update this one column UWI but I would like to turn this into a procedure, where if there is a new uwi in the table which  meet the requirement, it updates automatically.

UPDATE well_core_analysis a
SET    a.active_ind = 'N'
WHERE  active_ind = 'Y'
       AND a.analysis_source = 'GOVT'
       AND uwi IN (SELECT uwi
                   FROM  (SELECT DISTINCT e.string02 AS License_num,
                                          e.string03 AS Province,
                          FROM   data_entry.well_core_analysis b
                                 join well a
                                   ON b.uwi = a.uwi
                                 join well_license c
                                   ON a.uwi = c.uwi
                                 join license l
                                   ON c.license_id = l.license_id
                                 join publicdata.entity e
                                   ON l.license_id = e.string03  || '|'|| e.string02
                                 join publicdata.l_entity_document led
                                   ON e.entity_id = led.entity_id
                                 join publicdata.document d
                                   ON led.document_id = d.document_id
                                 join publicdata.l_document_document_type lddt
                                   ON d.document_id = lddt.document_id
                          WHERE  b.analysis_source = 'GOVT'
                                 AND lddt.document_type_id = 7
                                 AND Upper(document_name) LIKE '%.PDF'
                                 AND b.active_ind = 'Y'
                                 AND b.row_quality = 'VALID'
                                 AND e.string03 = 'MB')) ;
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

It depends on your data model. Cause the first thing to consider would be a trigger, which evaluate INSERTs or UPDATEs and process your logic when it applies.
Mark GeerlingsDatabase Administrator

"if there is a new uwi in the table which meet[s] the requirement, it updates automatically."

As ste5an suggested, that sounds more like a requirement for a database trigger than for a procedure.

Do you have the option to create a database trigger (or a combination of triggers) to do this task?  If not, you can use a procedure, but you will need something to run your procedure regularly.  This could be:
1. a database job, scheduled with dbms_scheduler
2. an OEM job (in Oracle Enterprise Manager, assuming that you use OEM)
3. a crontab job (that will need to be able to launch SQL*Plus and run a *.sql script)
4. some other job scheduler

But, with any of these job scheduler options, there will be a time lag between when the record(s) get(s) created, and when your procedure runs.

Using a database trigger would avoid the time lag, but you would need a combination of:
1. a row level trigger (that includes the "for each row" clause) to put the new rowid into a temporary holding area
2. a temporary holding area (this could be a global temporary table or a PL/SQL array)
3. a statement-level trigger (with no "for each row" clause) to retrieve the rowid(s), actually do the update you want for each now record, and clear the temporary holding area,  If you use a global temporary table, clearing it will happen automatically.

This multi-step approach is required because a "for each row" trigger is not allowed to select from the triggering table itself, but a "for each row" trigger is the only way to detect a new row efficiently.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial