Link to home
Start Free TrialLog in
Avatar of sue Tons
sue Tons

asked on

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

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,
                                          b.uwi,
                                          e.string03 AS Province,
                                          b.analysis_source,
                                          b.row_quality,
                                          b.active_ind
                          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')) ;
Avatar of ste5an
ste5an
Flag of Germany image

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.
"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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.