CODEWITHSUNDEEP
sqloracle-databaseplsql
Nicolas Boulein
Nicolas Boulein

Reputation: 15

Oracle - PLSQL / Insert a line if the update has been made

I want to make a log about an update. So i want to insert the log if the update query has been made.

    UPDATE DEPOT_IMPORT SET DEPOT_IMPORT.CD_REGATE = 
      (SELECT CONTROLE_IMPORT_REGATE.NEW_REGATE
       FROM CONTROLE_IMPORT_REGATE 
       WHERE DEPOT_IMPORT.CD_REGATE = CONTROLE_IMPORT_REGATE.OLD_REGATE)
WHERE DEPOT_IMPORT.CD_REGATE in (SELECT DISTINCT OLD_REGATE FROM CONTROLE_IMPORT_REGATE);
-- INSERTION AVIS FONCTIONNEL
INSERT INTO DEPOT_AVIS_FONCTIONNEL VALUES (p_id_depot,'Modification du regate car obsolète','AFO505');

Upvotes: 0

Views: 77

Answers (2)

XING
XING

Reputation: 9886

Ok thanks, so now i've to learn the trigger :)

Triggers are very helpful when you do logging/auditing. Hence learning trigger should not be a overhead.

See below demo which simplifies your requirement.

-- Table created and inserted a record for instance

CREATE TABLE depot_import ( cd_regate   NUMBER);

INSERT INTO depot_import VALUES ( 1 );

-- Created trigger on table depot_import to handle logging i.e inserting into 'depot_avis_fonctionnel'

CREATE OR REPLACE TRIGGER trk_upd BEFORE
    UPDATE ON depot_import
    FOR EACH ROW
BEGIN
    INSERT INTO depot_avis_fonctionnel VALUES (
        :new.cd_regate,
        'Modification du regate car obsolète',
        'AFO505'
    );
END;

Updating table:

UPDATE depot_import
    SET
        cd_regate = 3
WHERE cd_regate = 1;

Commit;

Records gets inserted via triggers.

--Selecting

SQL>  SELECT * FROM depot_avis_fonctionnel;
      COL1 COL2                                                                             COL3
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         3 Modification du regate car obsolète                                              AFO505

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143113

OK, I see the UPDATE:

UPDATE DEPOT_IMPORT SET DEPOT_IMPORT.CD_REGATE = ...

What is the log? Is it 

INSERT INTO DEPOT_AVIS_FONCTIONNEL ...

Anyway: if you use it as part of a PL/SQL block, then you could do something like this:

begin
  update depot_import set ...

  -- Log the update:
  insert into log_table (cdate, text, rows_affected) values
    (sysdate, 'Table DEPOT_IMPORT has been updated', sql%rowcount);

  insert into dep DEPOT_AVIS_FONCTIONNEL ...

  -- Log the insert:
  insert into log_table (cdate, text, rows_affected) values
    (sysdate, 'Table DEPOT_AVIS_FONCTIONNEL has been inserted', sql%rowcount);
end;

If you use pure SQL, then yes - you'll probably use a trigger. It fires for each table separately, so you'll have to write as many triggers as there are tables you modify.

Upvotes: 0

Related Questions