Simple Usage of Trigger

 Below given are few examples of Triggers mostly used

a)Using Trigger to track user while Logging Off

CREATE OR REPLACE TRIGGER log_table

BEFORE LOGOFF ON SCHEMA_NAME

BEGIN

INSERT INTO RECORDS_LOG

VALUES (USER, TO_CHAR(SYSDATE,'DD DY HH24:MI:SS'),LOGG_OFF);

END;

b)Using Trigger to track user while Logging On

CREATE OR REPLACE TRIGGER log_table

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO RECORDS_LOG VALUES (USER, TO_CHAR(SYSDATE,'DD DY HH24:MI:SS'), 'LOGG_ON');

END;

c)Simple Trigger to compare Old and New Salary

CREATE OR REPLACE TRIGGER SAL_CHECK

BEFORE UPDATE OF SAL ON EMP

FOR EACH ROW

WHEN (NEW.SAL < OLD.SAL)

BEGIN

RAISE_APPLICATION_ERROR (-20508,

'NEW SALARY CANNOT BE LESS THAN OLD SALARY');

END;

d)Trigger to Store the Old and New values

CREATE OR REPLACE TRIGGER SAL_RECORD

BEFORE UPDATE OF SAL ON EMP

FOR EACH ROW

BEGIN

INSERT INTO RECORDS_SAL VALUES(:OLD.SAL,:NEW.SAL);

END;

e)To check for Insert Update or Delete on a table

CREATE OR REPLACE TRIGGER CHANGE_TRIGGER

AFTER INSERT OR UPDATE OR DELETE ON EMP

BEGIN

INSERT INTO RECORDS_LOG VALUES (USER,TO_CHAR(SYSDATE,'DD DY HH24:MI:SS'));

END;