Apex page DML Mail Alert Trigger
0
votes
0
answers
73
views
I have a Trigger and Function which sends mail with "updated" column(s),
when i am updating in my back-end (sql developer), i receive a mail with that particular column.
But when i do UPDATE directly through Apex Page i get mail listing all columns names indicating all are updated.
**Function Code:**
create or replace function SNAP_FUN(inTableName in varchar2) return varchar2 is
result varchar2(4000);
sep varchar2(2) := null;
begin
for c in (select column_name from user_tab_columns where table_name = inTableName) loop
if updating(c.column_name) then
result := result || sep || c.column_name;
sep := ', ';
end if;
end loop;
return result;
end;
/
**Trigger**
create or replace trigger SNAP_TRIG
after update on dw.W_SUPPL_SNAPSHOT_CONTROL_G
for each row
DECLARE X VARCHAR2(4000);
begin
begin
X:=X||('Updated:' || snap_fun('W_SUPPL_SNAPSHOT_CONTROL_G'));
end;
dlr_send_mail('***.com', 'SUPPLEMENTAL_SNAPSHOT', 'These are the column changes observed' || X
, '****', 'host')
end;
Asked by Notadoctor
(3 rep)
Aug 18, 2022, 07:50 PM
Last activity: Aug 19, 2022, 04:00 PM
Last activity: Aug 19, 2022, 04:00 PM