CODEWITHSUNDEEP
oracle-databasetriggersaverage
cooow
cooow

Reputation: 859

How to get table fields from which was called a trigger with Oracle 10g?

I want to calculate the average of values ​​in a column of my table (PRA_COEFF) (RAPPORT_VISITE) for a field (PRA_NUM) given, when I add or change a row of it. Then I want to save this value in another table (PRACTITIONER) to the row where PRA_NUM worth PRA_NUM given above. 

CREATE TABLE "RAPPORT_VISITE" 
(
"RAP_NUM" NUMBER (10,0), 
"PRA_NUM" NUMBER (10,0), 
"PRA_COEFF" NUMBER (10,0), 
) 

CREATE TABLE "PRATICIEN" 
(
"PRA_NUM" NUMBER (10,0), 
"PRA_COEFCONF" NUMBER 
)

The trigger is called when adding or Modification RAPPORT_VISITE the table. I tried like this, but I can not retrieve the row affected by the trigger, and thus PRA_NUM, that I need to read.

create or replace TRIGGER UDPATE_PRAT_COEFCONF
  AFTER INSERT or UPDATE ON RAPPORT_VISITE

  DECLARE
  somme NUMBER;
  nb NUMBER;
  moyenne NUMBER;
  rapport NUMBER;
  pra_id NUMBER;

BEGIN

  /*SELECT MAX(RAP_NUM) INTO rapport FROM RAPPORT_VISITE; // Not want I need in case where I modify a row... */
  SELECT PRA_NUM INTO pra_id FROM RAPPORT_VISITE WHERE RAP_NUM=rapport;
  SELECT SUM(PRA_COEFF) INTO somme FROM RAPPORT_VISITE WHERE PRA_NUM=pra_id;
  SELECT COUNT(*) INTO nb FROM RAPPORT_VISITE WHERE PRA_NUM=pra_id;

  IF (nb != 0) THEN
    moyenne := somme/nb;
    moyenne := TRUNC (moyenne,1);
    UPDATE PRATICIEN SET PRA_COEFCONF=moyenne WHERE PRA_NUM=pra_id;
  END IF;

END;

Upvotes: 0

Views: 58

Answers (1)

vav
vav

Reputation: 4694

Here are 2 limits that triggers are usually have:

  1. when invoked for the all affected records, you don't know what exactly have changed

  2. when invoked for individual records (FOR EACH ROW), you are limited with access to the modified table

To address that limitation, starting from Oracle 11g, we can use compound trigger:

CREATE OR REPLACE TRIGGER <trigger-name>
FOR <trigger-action> ON <table-name>
COMPOUND TRIGGER

-- Global declaration.
g_global_variable VARCHAR2(10);
-- block 1
BEFORE STATEMENT IS
BEGIN
NULL; -- Do something here.
END BEFORE STATEMENT;
-- block 2
BEFORE EACH ROW IS
BEGIN
NULL; -- Do something here.
END BEFORE EACH ROW;
-- block 3
AFTER EACH ROW IS
BEGIN
NULL; -- Do something here.
END AFTER EACH ROW;
-- block 4
AFTER STATEMENT IS
BEGIN
NULL; -- Do something here.
END AFTER STATEMENT;

END <trigger-name>;

And looks like this is what you need. In block 1, initialize your variables, in block 2 or 3 collect changes from individual rows, then in block 4 use that information to create the rest of the business logic.

If we are limited by 10g, then we can emulate compound trigger using package variables. This solution is limited, because package variables are global for the session. If withing a session you have 2 similar operations, their results would be merged.

Here is the solution

  1. You will have 3 separate triggers, that would represent block 1, (2 or 3), and 4 from the trigger above.

  2. You will have a package with variable g_global_variable (from above)

3 actions:

1. in trigger for block 1 initiate g_global_variable 
2. in trigger for block 2 or 3, populate it with actual values
3. in trigger for block 4, create your logic

Ofcourse, g_global_variable could be not alone, it could be record or collection.

Upvotes: 1

Related Questions