Oracle Trigger Case Study

-

Trigger Case Study 


CREATE TABLE LOG_TRIG_TABLE

(USER_ID VARCHAR2(8), LOG_DATE DATE, ACTION VARCHAR2(20));


CREATE TABLE LOG_TRIG_TABLE_ALL

(USER_ID VARCHAR2(8), LOG_DATE DATE, ACTION VARCHAR2(20));


CREATE TABLE DML_LOG_TRIG_TABLE

(USER_NAME VARCHAR2(8), DML_DATE DATE, DML_TYPE VARCHAR2(8));



-------------------  TRACK BEFORE LOGON 

CREATE OR REPLACE TRIGGER LOGON_TRIG

AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO  LOG_TRIG_TABLE 

(USER_ID,LOG_DATE, ACTION)

VALUES (USER,SYSDATE,'LOGGING ON ');

END;

/


CREATE OR REPLACE TRIGGER LOGON_TRIG_ALL

AFTER LOGON ON DATABASE

BEGIN

INSERT INTO  LOG_TRIG_TABLE_ALL 

(USER_ID,LOG_DATE, ACTION)

VALUES (USER,SYSDATE,'LOGGING ON ');

END;

/


-------------------  TRACK BEFORE LOGOFF 

CREATE OR REPLACE TRIGGER LOGOFF_TRIG

BEFORE LOGOFF ON SCHEMA

BEGIN

INSERT INTO  LOG_TRIG_TABLE 

(USER_ID,LOG_DATE, ACTION)

VALUES (USER,SYSDATE,'LOGGING OFF ');

END;

/


  -- Update

CREATE OR REPLACE TRIGGER update_trig

AFTER UPDATE ON student

BEGIN

               INSERT INTO  dml_log_trig_table

               (USER_NAME,DML_DATE, DML_TYPE)

               VALUES (USER,SYSDATE,'UPDATE');

END;

/



CREATE OR REPLACE TRIGGER LOGOFF_TRIG_ALL

BEFORE LOGOFF ON DATABASE -- YOU MUST BE DBA USER TO CREATE THIS TRIGGER

BEGIN

INSERT INTO  LOG_TRIG_TABLE_ALL 

(USER_ID,LOG_DATE, ACTION)

VALUES (USER,SYSDATE,'LOGGING OFF ');

END;

/


SELECT USER_ID,TO_CHAR(LOG_DATE,'FMDD "OF" MONTH YYYY FMHH24:MM:SSAM'),ACTION

FROM LOG_TRIG_TABLE;


SELECT USER_ID,TO_CHAR(LOG_DATE,'FMDD "OF" MONTH YYYY FMHH24:MM:SSAM'),ACTION

FROM LOG_TRIG_TABLE_ALL;


SELECT * 

FROM USER_TRIGGERS

WHERE TABLE_NAME='LOG_TRIG_TABLE';



---- work at secured time


CREATE OR REPLACE TRIGGER secure_emp           

before insert ON emp

BEGIN

if (to_char(sysdate,'DY') IN ('FRI')

OR to_char(sysdate,'HH24') BETWEEN '08' AND '18')

OR USER='JOTI' THEN

RAISE_APPLICATION_ERROR(-20500,'IT IS OVER BUSSINESS HOUR ');

END IF; 

END;

/

INSERT INTO rashid.EMP (EMPNO,ENAME) VALUES ('4563','MR. RASHID PRAMANIK');

INSERT INTO EMP (EMPNO,ENAME) VALUES ('4563','MR. RASHID PRAMANIK');


SELECT TRIGGER_NAME,TRIGGER_TYPE 

FROM USER_TRIGGERS;


SELECT TRIGGER_NAME,TRIGGERING_EVENT

FROM USER_TRIGGERS;


SELECT TRIGGER_NAME,TRIGGER_TYPE ,TRIGGERING_EVENT

FROM USER_TRIGGERS;


SELECT TRIGGER_NAME,TRIGGERING_EVENT,TABLE_OWNER,BASE_OBJECT_TYPE

FROM USER_TRIGGERS;


SELECT TRIGGER_NAME,TRIGGERING_EVENT,TABLE_OWNER,BASE_OBJECT_TYPE,

FROM USER_TRIGGERS;


SELECT * 

FROM USER_TRIGGERS

WHERE TRIGGER_NAME='EMP_VIEW_TRIG';

-- Deleting History

CREATE OR REPLACE TRIGGER DML_HISTORY

BEFORE INSERT OR DELETE OR UPDATE ON DEPT

BEGIN

 IF DELETING THEN

  INSERT INTO DML_LOG_TRIG_TABLE(USER_NAME,DML_DATE,DML_TYPE)

  VALUES (USER,SYSDATE,'DELETE');

 ELSIF INSERTING THEN

  INSERT INTO DML_LOG_TRIG_TABLE(USER_NAME,DML_DATE,DML_TYPE)

  VALUES (USER,SYSDATE,'INSERT');

 ELSIF UPDATING THEN

 INSERT INTO DML_LOG_TRIG_TABLE(USER_NAME,DML_DATE,DML_TYPE)

  VALUES (USER,SYSDATE,'UPDATE');

 END IF;

END;

/


-- DONT RUN THIS PORTION !!!!!!!!!!! BEWARE

DROP TRIGGER OBJECT_MAKE_LOG_TRIG;


DROP TRIGGER OBJECT_DROP_LOG_TRIG;


DROP TRIGGER OBJECT_ALTER_LOG_TRIG;


DROP TABLE DDL_LOG_TRIG_TABLE;



Comments

Post a Comment

Popular posts from this blog

How to be an Innovative Speaker?

-
1. Innovation in Effective session conduct   Define your message of the session. •         Make it participatory. In your last line, ask questions and teach by name. The final 5 to 10 minutes will be dedicated to participant comments. •         Maintain start and Ending time. •          Concentrate on the audience's needs. Patience is required. Don't retaliate if the participants engage in unwelcome behavior. •         Boost your self-confidence on topics by reading daily papers, books, articles, senior lectures and self-experience. •         Prepare yourself. Write the last update date in a text box and put red color at the top right corner of your slide. •         Do not attend class until you have updated your slide. You won't show any slides that aren't based on current data. Pl...
Read more

Developing Personal Skills

-
  Developing Personal Skills Engr. Md. Abdur Rashid  PhD, NAPD Dear Officers and Staff, you must establish and ensure the long-term growth of your organization. Maintain the following managerial abilities in order to enhance personal skills on a daily basis while working. 1.                  1)       Self-assessment 2)       Creativity 3)       Innovation 4)       Listening and feedback 5)       Find self-satisfaction 6)       Leadership 7)       Team building 8)       Hunt knowledge 9)       Self-reliance and judgment 10)    Resilience 11)    Time management 12)    Stress management 13)    Control over anything 14)    Find satisfa...
Read more

Differences between the terms Science, Technology, and Engineering

-
What are the differences between the terms Science, Technology, and Engineering? Dr. Engr. Md. Abdur Rashid Director (Admin & Finance) NAPD Science Focus: The systematic study of the natural world through observation, experimentation, and analysis. Goal: To understand and explain the universe, its laws, and its phenomena. Methods: Scientific inquiry involves formulating hypotheses, conducting experiments, collecting data, and drawing conclusions. Examples: Physics, chemistry, biology, astronomy, geology. Technology Focus: The application of scientific knowledge to create tools, machines, and systems that solve problems or fulfill human needs. Goal: To improve efficiency, productivity, and quality of life. Methods: Technological development often involves innovation, design, and engineering. Examples: Computers, smartphones, automobiles, medical devices, renewable energy systems. Eng...
Read more