Before Update and After Update Triggers

Before Update and After Update Triggers-Learn how to use Before and After Update Triggers with Examples.

BEFORE UPDATE TRIGGER

The Before UPDATE Trigger is fired before UPDATE operation performed on the table. Before Keyword access the new values before they go into the table.

Syntax

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE UPDATE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example Program – to validate the email id before inserting or updating data into table.

create or replace
TRIGGER  EMAIL_VALID_ON_EMP
BEFORE INSERT OR UPDATE ON EMP1
FOR EACH ROW
BEGIN
IF :NEW.EMAIL NOT LIKE '%@%.%' THEN
RAISE_APPLICATION_ERROR(-20000,'PLEASE ENTER VALID EMAIL');
END IF;

END;

Insertion Query

INSERT INTO EMP1(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL)
VALUES(14,'JHKHKHK','PHJINJJ','JHKHKHK.PHJINJJ.GMAIL.COM');

Output

Error report:
SQL Error: ORA-20000: PLEASE ENTER VALID EMAIL
ORA-06512: at “TRAINING593.EMAIL_VALID_ON_EMP”, line 4
ORA-04088: error during execution of trigger ‘TRAINING593.EMAIL_VALID_ON_EMP’
20000. 00000 – “%s”

Insertion Query

INSERT INTO EMP1(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL)
VALUES(14,'JHKHKHK','PHJINJJ','[email protected]');

Output

1 rows inserted.

Updation Query

UPDATE EMP1 SET EMAIL='RAKESH.RAK.COM' WHERE EMPLOYEE_ID=14;

Output

SQL Error: ORA-20000: PLEASE ENTER VALID EMAIL
ORA-06512: at “TRAINING593.EMAIL_VALID_ON_EMP”, line 4
ORA-04088: error during execution of trigger ‘TRAINING593.EMAIL_VALID_ON_EMP’
20000. 00000 – “%s”

Updation Query

UPDATE EMP1 SET EMAIL='[email protected]' WHERE EMPLOYEE_ID=14;

Output
1 rows updated.

AFTER UPDATE TRIGGER

The AFTER UPDATE Trigger is fired AFTER UPDATE operation performed on the table.
AFTER Keyword access the old values after the initial changes applied on the table and the table is back in a consistent state.

Syntax

CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER UPDATE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example Program – to store old values of emp table after performing update operation with new values on emp table

CREATE OR REPLACE TRIGGER TR_EMPLOYYE_TBL_AUDIT AFTER
  UPDATE ON EMP1 FOR EACH ROW BEGIN IF :OLD.EMAIL  :NEW.EMAIL THEN
  INSERT
  INTO EMP_AUDIT
    (
      EMPID ,
      PARAMETER_NAME,
      PARAMETER_VALUE ,
      MOD_FIED_DATE
    )
    VALUES
    (
      :OLD.EMPLOYEE_ID,
      'EMAIL',
      :OLD.EMAIL,
      SYSDATE
    );
END IF;
IF :OLD.FIRST_NAME  :NEW.FIRST_NAME THEN
  INSERT
  INTO EMP_AUDIT
    (
      EMPID ,
      PARAMETER_NAME,
      PARAMETER_VALUE ,
      MOD_FIED_DATE
    )
    VALUES
    (
      :OLD.FIRST_NAME,
      'FIRST_NAME',
      :OLD.FIRST_NAME,
      SYSDATE
    );
END IF;
IF :OLD.LAST_NAME  :NEW.LAST_NAME THEN
  INSERT
  INTO EMP_AUDIT
    (
      EMPID ,
      PARAMETER_NAME,
      PARAMETER_VALUE ,
      MOD_FIED_DATE
    )
    VALUES
    (
      :OLD.LAST_NAME,
      'LAST_NAME',
      :OLD.LAST_NAME,
      SYSDATE
    );
END IF;
END;

Updation Query

UPDATE EMP1  SET EMAIL='[email protected]' WHERE EMPLOYEE_ID=14;

SELECT * FROM EMP_AUDIT;

Output

SELECT * FROM EMP;

Output