Create a trigger on table whenever a column is inserted or updated

Question

I have a table whose description is below

Name                          Null     Type           
----------------------------- -------- -------------- 
ID                            NOT NULL NUMBER         
CMP                                    NVARCHAR2(100) 
SAP_ID                                 NVARCHAR2(100) 
ID_OD_COUNTCHANGE                      NVARCHAR2(100) 
ID_OD_CHANGEDDATE                      DATE           
RRH_COUNTCHANGE                        NVARCHAR2(100) 
RRH_CHANGEDDATE                        DATE           
TENANCY_COUNTCHANGE                    NVARCHAR2(100) 
TENANCY_CHANGEDDATE                    DATE           
RFS_DATE                               DATE           
RFE1_DATE                              DATE           
INFRA_PROVIDER                         NVARCHAR2(100) 
IP_COLO_SITEID                         NVARCHAR2(100) 
SITE_NAME                              NVARCHAR2(500) 
R4GSTATE                               NVARCHAR2(100) 
MW_INSTALLED                           NVARCHAR2(100) 
DG_NONDG                               NVARCHAR2(100) 
EB_NONEB                               NVARCHAR2(100) 
TOWER_TYPE                             NVARCHAR2(100) 
VENDOR_CODE                            NVARCHAR2(100) 
RFCDATE                                DATE           
POLITICAL_STATE_NAME                   NVARCHAR2(100) 
POLITICAL_STATE_CODE                   NVARCHAR2(100) 
SITE_DROP_DATE                         DATE           
CITY_NAME                              NVARCHAR2(100) 
NEID                                   NVARCHAR2(100) 
FACILITY_LATITUDE                      NVARCHAR2(100) 
FACILITY_LONGITUDE                     NVARCHAR2(100) 
RJ_STRUCTURE_TYPE                      NVARCHAR2(100) 
RJ_JC_NAME                             NVARCHAR2(100) 
RJ_JC_CODE                             NVARCHAR2(100) 
COMPANY_CODE                           NVARCHAR2(100) 
BLCHAIN_RESP_MSG_MASTER                NVARCHAR2(100) 
BLCHAIN_RESP_CODE_MASTER               NVARCHAR2(100) 
SITE_ADDRESS                           CLOB           
BLCHAIN_RESP_MSG_INCREMENTAL           VARCHAR2(100)  
BLCHAIN_RESP_CODE_INCREMENTAL          VARCHAR2(100)  
CREATED_BY                             NVARCHAR2(100) 
CREATED_DATE                           DATE           
SEL_CHANGED_VAL                        NVARCHAR2(100) 
CMM                                    NVARCHAR2(20)  
FCA                                    NVARCHAR2(20)  
LAST_UPDATED_BY                        NVARCHAR2(100) 
LAST_UPDATED_DATE                      DATE

What I want is, I want to write a trigger that whenever any row is inserted or updated I want to update the LAST_UPDATED_DATE column by SYSDATE.

How can I create the trigger?

With a few modifications this question could be solved with [Added date and modified date for Oracle table audit](https://stackoverflow.com/questions/13272711/added-date-and-modified-date-for-oracle-table-audit) — CannedMoose, Nov 10 '20 at 12:36

score 1 · Accepted Answer · answered Nov 10 '20 at 12:39

For Insert, You can use the DEFAULT clause in the table and for UPDATE, You can create the simple trigger as follows:

CREATE OR REPLACE TRIGGER TRG_NAME 
    BEFORE UPDATE ON YOUR_TABLE_NAME
    FOR EACH ROW
BEGIN
    :NEW.LAST_UPDATED_DATE := SYSDATE;
END;
/

Create a trigger on table whenever a column is inserted or updated

1 Answers1