CODEWITHSUNDEEP
oracle-databaseplsqloracle11gsendmaildatabase-trigger
Zerimeche Khaled
Zerimeche Khaled

Reputation: 1

didn't send mail html body in plsql

I developed a trigger in plsql, the trigger work but I received 6 mails in the same time. I need just one mail, how I can do it?

CREATE or replace TRIGGER RI 
  AFTER insert or update on ap_supplier_sites_all 
  for each row 

DECLARE 
  x_count NUMBER;

begin

  select count(*) into x_count 
  from rib1 r1,rib2 r2 
  where r1.ATTRIBUTE4=r2.Supplier_RIB; 

  if(x_count > 0) 
  then 
     testrib;--execute SP
  end if;
end;

Upvotes: 0

Views: 103

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Here's how it goes:

  • trigger fires when you insert or update rows in AP_SUPPLIER_SITES_ALL

    • suppose you ran an update statement which updated 6 rows, something like this in Scott's EMP table:

      update emp set 
  sal = sal * 1.1
  where deptno = 20;

  • trigger fires for each of those rows; if there are 6 rows updated, it fires 6 times

  • it calculates number of rows in joined rib1 and rib2 tables

    • is it OK not to reference some "ID" column from ap_supplier_sites_all? Something like

      select count(*) into x_count 
from rib1 r1, rib2 r2 
where r1.ATTRIBUTE4 = r2.Supplier_RIB
  and r1.some_id = :new.some_ap_supplier_sites_all_id;      --> this
  • if that number is larger than 0, you're executing testrib procedure
    • if it sends an e-mail, then yes - it'll send it 6 times

What to do? Switch to a statement-level trigger (instead of the row-level you currently use) as it fires once per statement, regardless number of rows affected.

Upvotes: 2

Related Questions