Leverage
Reputation: 73
Function to detect Columns getting Updated For Oracle Apex
I wrote a function to detect which columns are getting updated for a table.
This Table is present in Oracle Apex.
I use this function to send mail for Update performed through APEX UI on it.
Trigger Code:
create or replace TRIGGER TRIAL AFTER UPDATE ON TABLE
FOR EACH ROW
DECLARE
result varchar2(4000);
begin
result := snap_fun('TABLE_NAME');
SEND_MAIL('JOHN@****',
'TABLE Modified',result,'bidev-noreply@***','HOST');
end;
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 all_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;
Problem : When i am updating any column through back end, i am receiving correct mail with only columns that are actually being updated but when i Update through Oracle Apex (using UI), I receive the list of all the columns.
Upvotes: 2
Views: 477
Answers (2)
Leverage
Reputation: 73
So this is the Implementation i came up with:
> I have 15 columns to address so:
1. Check if difference exists.
2.Yes Proceed to check next flag
3.Insert Each Change with column name.
3.After checking all move them to Single Variable by looping through Log Table
4.Push this to SMTP Proc.
5.Truncate table(used Autonomous Trans along with Delete proc.)
CREATE TABLE "DW"."LOG_SUPPLEMENTAL_SNAPSHOT"
( "CHANGED_COLUMN" VARCHAR2(255) ,
"CHANGED" VARCHAR2(2) ,
"MONTH_ID" VARCHAR2(255) ,
"CHANGED_BY" VARCHAR2(255)
);
/
CREATE OR REPLACE PROCEDURE DW.PROC_TRUNCATE_TABLE (TABLE_NAME IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || TABLE_NAME;
END;
/
create or replace TRIGGER "DW"."TRG_W_SUPPL_SNAPSHOT_CONTROL_G"
BEFORE INSERT OR UPDATE
ON DW.W_SUPPL_SNAPSHOT_CONTROL_G
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
DECLARE
PRAGMA autonomous_transaction;
BODY VARCHAR2(4000);
ENV VARCHAR2(255);
I_EMAIL_TO VARCHAR2(4000);
CHANGE_BY VARCHAR2(255);
BEGIN
BEGIN
BEGIN
Select SUCCESS_NOTIFY_LIST into I_EMAIL_TO from DW.W_ETL_EMAIL_CONTROL where TASK_NAME='W_SUPPL_SNAPSHOT_CONTROL_G';
SELECT VALUE into ENV FROM DW.W_ETL_EMAIL_CONFIG WHERE NAME='EMAIL_FROM';
IF INSERTING THEN
SELECT SYSDATE, NVL(v('APP_USER'),'AUTO_INSERT'), NVL(v('APP_USER'),'AUTO_INSERT'), SYSDATE
INTO :NEW.CREATED_ON_DT, :NEW.CREATED_BY,:NEW.CHANGED_BY, :NEW.CHANGED_ON_DT
FROM DUAL;
SELECT :NEW.CHANGED_BY INTO CHANGE_BY FROM DUAL;
END IF;
IF UPDATING THEN
SELECT SYSDATE, v('APP_USER')
INTO :NEW.CHANGED_ON_DT, :NEW.CHANGED_BY
FROM DUAL;
SELECT :NEW.CHANGED_BY INTO CHANGE_BY FROM DUAL;
END IF;
END;
IF UPDATING('DFT_LEASE_ACT_LOCK_FLG') AND ( :NEW.DFT_LEASE_ACT_LOCK_FLG <> :OLD.DFT_LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'DFT_LEASE_ACT_LOCK_FLG',
:NEW.DFT_LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('WESTIN_LEASE_ACT_LOCK_FLG') AND( :NEW.WESTIN_LEASE_ACT_LOCK_FLG <> :OLD.WESTIN_LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'WESTIN_LEASE_ACT_LOCK_FLG',
:NEW.WESTIN_LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('ASCENTY_LEASE_ACT_LOCK_FLG') AND ( :NEW.ASCENTY_LEASE_ACT_LOCK_FLG <> :OLD.ASCENTY_LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'ASCENTY_LEASE_ACT_LOCK_FLG',
:NEW.ASCENTY_LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('TELX_LEASE_ACT_LOCK_FLG') AND( :NEW.TELX_LEASE_ACT_LOCK_FLG <> :OLD.TELX_LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'TELX_LEASE_ACT_LOCK_FLG',
:NEW.TELX_LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('DIGITAL_REALTY_LEASE_ACT_LOCK_FLG') AND ( :NEW.DIGITAL_REALTY_LEASE_ACT_LOCK_FLG <> :OLD.DIGITAL_REALTY_LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'DIGITAL_REALTY_LEASE_ACT_LOCK_FLG',
:NEW.DIGITAL_REALTY_LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('TELECITY_LEASE_ACT_LOCK_FLG') AND( :NEW.TELECITY_LEASE_ACT_LOCK_FLG <> :OLD.TELECITY_LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'TELECITY_LEASE_ACT_LOCK_FLG',
:NEW.TELECITY_LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('INXN_LEASE_ACT_LOCK_FLG') AND ( :NEW.INXN_LEASE_ACT_LOCK_FLG <> :OLD.INXN_LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'INXN_LEASE_ACT_LOCK_FLG',
:NEW.INXN_LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('INXN_OA_LOCK_FLG') AND( :NEW.INXN_OA_LOCK_FLG <> :OLD.INXN_OA_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'INXN_OA_LOCK_FLG',
:NEW.INXN_OA_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('TERACO_OA_LOCK_FLG') AND ( :NEW.TERACO_OA_LOCK_FLG <> :OLD.TERACO_OA_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'TERACO_OA_LOCK_FLG',
:NEW.TERACO_OA_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('TERACO_LA_LOCK_FLG') AND( :NEW.TERACO_LA_LOCK_FLG <> :OLD.TERACO_LA_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'TERACO_LA_LOCK_FLG',
:NEW.TERACO_LA_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('LEASE_ACT_LOCK_FLG') AND ( :NEW.LEASE_ACT_LOCK_FLG <> :OLD.LEASE_ACT_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'LEASE_ACT_LOCK_FLG',
:NEW.LEASE_ACT_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('CUST_LOCK_FLG') AND( :NEW.CUST_LOCK_FLG <> :OLD.CUST_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'CUST_LOCK_FLG',
:NEW.CUST_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('PROPERTY_LOCK_FLG') AND ( :NEW.PROPERTY_LOCK_FLG <> :OLD.PROPERTY_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'PROPERTY_LOCK_FLG',
:NEW.PROPERTY_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('OA_LOCK_FLG') AND ( :NEW.OA_LOCK_FLG <> :OLD.OA_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'OA_LOCK_FLG',
:NEW.OA_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
IF UPDATING('FUSION_OA_LOCK_FLG') AND ( :NEW.FUSION_OA_LOCK_FLG <> :OLD.FUSION_OA_LOCK_FLG ) THEN
INSERT INTO DW.LOG_SUPPLEMENTAL_SNAPSHOT (
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
) VALUES (
'FUSION_OA_LOCK_FLG',
:NEW.FUSION_OA_LOCK_FLG,
:NEW.MONTH_ID,
NVL( V('APP_USER'),' MANUAL UPDATE')
);
END IF;
END;
FOR I IN (
SELECT
CHANGED_COLUMN,
CHANGED,
MONTH_ID,
CHANGED_BY
FROM
DW.LOG_SUPPLEMENTAL_SNAPSHOT
) LOOP
BODY := BODY
|| ( 'Modification Performed : '||chr(13)||chr(10)
|| 'Flag : '|| I.CHANGED_COLUMN||chr(13)||chr(10)
|| 'Period : '|| I.MONTH_ID||chr(13)||chr(10)
|| 'TO: ' || I.CHANGED||chr(13)||chr(10)
|| ' BY : '|| I.CHANGED_BY||chr(13)||chr(10)||chr(13)||chr(10) );
END LOOP;
SELECT SUCCESS_NOTIFY_LIST INTO I_EMAIL_TO FROM DW.W_ETL_EMAIL_CONTROL WHERE TASK_NAME = 'W_SUPPL_SNAPSHOT_CONTROL_G';
SELECT VALUE INTO ENV FROM DW.W_ETL_EMAIL_CONFIG WHERE NAME = 'EMAIL_FROM';
SEND_MAIL(I_EMAIL_TO, 'SUPPLEMENTAL SNAPSHOT CONTROL TABLE MODIFIED', BODY,ENV, 'host'
);
PROC_TRUNCATE_TABLE('DW.LOG_SUPPLEMENTAL_SNAPSHOT');
END;
Upvotes: 0
Jason Seek Well
Reputation: 271
I would suggest a workaround like this due to the behavior of Apex UI. You will need to test every column as Justin Cave noted in his comment. The column list can be removed from the AFTER UPDATE clause if updates only occur via the Apex UI. Finally, if multiple rows can be updated in one transaction, you could use a compound trigger and gather all of the row-level changes in one email.
Oracle Create Trigger Statement
CREATE TRIGGER sample_trg
-- can list each column explicitly if updates can occur outside of Apex UI
-- I only put two in this example
AFTER UPDATE OF number_col, date_col ON sample_tbl
FOR EACH ROW
DECLARE
l_result VARCHAR2(4000);
l_sep VARCHAR2(2) := ', ';
BEGIN
-- check for differences on each of your columns
-- I only put two in this example
IF (:old.number_col IS NULL AND :new.number_col IS NOT NULL
OR :old.number_col IS NOT NULL AND :new.number_col IS NULL
OR :old.number_col != :new.number_col)
THEN
l_result := l_result || 'NUMBER_COL' || l_sep;
END IF;
IF (:old.date_col IS NULL AND :new.date_col IS NOT NULL
OR :old.date_col IS NOT NULL AND :new.date_col IS NULL
OR :old.date_col != :new.date_col )
THEN
l_result := l_result || 'DATE_COL';
END IF;
-- remove any trailing separator
l_result := RTRIM(l_result, l_sep);
-- if there were any changed columns send email
IF (LENGTH(l_result) > 0) THEN
send_mail('john@****', 'SAMPLE_TBL is modified ' || l_result
,'bi-dev@*****', 'HOST');
END IF;
END sample_trg;
Upvotes: 0
Related Questions
- Create a trigger on table whenever a column is inserted or updated
- How to check if a column value is updated/modified
- Trigger for only changed values
- Oracle SQL trigger on update of column
- oracle trigger on update
- Trigger to see if a table is updated
- Fire trigger only when rows are updated
- how to trigger when multiple columns are updated
- On update triggering in oracle?
- How can we figure out that a column in my oracle table is being populated/updated by a trigger of another table?