====================
—Trigger in Oracle:
====================
 — Trigger is a PLSQL named Bolck which is automatically fired when an event occured on the DB
 — The event can be DDL, DML, system event
 — Triggers code will be invoked automatically when an event occur
 — Can’t manually trigger the event.
————————————————————————————————-
 Purpose of Triggers:Â
  — Auditing
  — Enforcing complex integrity check
  — Logging
  — Enforcing security for transactions
  — Prevent the invalid data on the txn
————————————————————————————————-
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.
Types of DML Triggers:Â
======================
Row level and Statement level trigger
====================
Firing Point: Before
====================
BEFORE INSERT TRIGGER
BEFORE UPDATE TRIGGER
BEFORE DELETE TRIGGER
====================
Firing Point: After
===================
AFTER INSERT TRIGGER
AFTER UPDATE TRIGGER
AFTER DELETE TRIGGER
—————————————————————-
BEFORE INSERT
It indicates that the trigger will fire before the INSERT operation is executed.
CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE INSERT
  ON table_name
  [ FOR EACH ROW ]
DECLARE
  — variable declarations
BEGIN
  — trigger code
EXCEPTION
  WHEN …
  — exception handling
END trigger_name;
================================================================================
The syntax to a drop a trigger in Oracle in Oracle/PLSQL is:
DROP TRIGGER trigger_name;
—————————————————————-
The syntax for a disabling a Trigger in Oracle/PLSQL is:
ALTER TRIGGER trigger_name DISABLE;
——————————————————————
The syntax for a enabling a Trigger in Oracle/PLSQL is:
ALTER TRIGGER trigger_name ENABLE;
——————————————————————
The syntax for a disabling all Triggers on a table in Oracle/PLSQL is:
ALTER TABLE table_name DISABLE ALL TRIGGERS;
———————————————————————
The syntax to enable all triggers on a table in Oracle/PLSQL is:
ALTER TABLE table_name ENABLE ALL TRIGGERS;
———————————————————————
create table after_delete
(
user_name varchar2(20),
date_time date,
count number(10));
drop table after_delete;
create or replace trigger after_delete_trig
after delete on
declare
v_count number:=0;
begin
select count(*) into v_count from emp_join;
insert into after_delete values(user,sysdate,v_count);
end;
select * from after_delete;
select * from emp_join;
delete from emp_join where emp_id=10000;
——————————————————————————————————————
========================
Old and New Qualifiers:
=======================
          old  new
insert     NA   Yes
Update   Yes  Yes
Delete    Yes  NA
 CREATE TABLE HR.customer_details
  (
  CUSTOMER_ID NUMBER(10,0),
FIRST_NAME VARCHAR2(30 BYTE),
MOBILE VARCHAR2(30 BYTE),
ADDRESS VARCHAR2(30 BYTE),
ZIPCODE NUMBER(6,0),
COUNTRY VARCHAR2(30 BYTE),
PRIMARY KEY (CUSTOMER_ID)
);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values (100000,’Sravanthi’,’1234678906′,’rtretr retre’,400027,’India’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values (100001,’NIC IT Academy’,’7010080468′,’No.2 VGP nagar’,453254,’India’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values (100002,’Swathi’,’3545454544′,’Framingham’,1921,’USA’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values (100003,’Suresh’,’5678453233′,’G1, Balaji Nagar’,7745,’Australia’);
Insert into HR.customer_details (CUSTOMER_ID,FIRST_NAME,MOBILE,ADDRESS,ZIPCODE,COUNTRY) values (100004,’Sridhar’,’7650099124′,’Pond Street’,77723,’CA’);
commit;
select * from customer_details;
delete from customer_details where first_name=’Swathi’;
commit;
create table customer_bkp_trig as select * from customer_details where 1=2;
alter table customer_bkp_trig add date_of_deletion date;
alter table customer_bkp_trig add who_deleted varchar2(30);
select * from customer_bkp_trig;
create or replace trigger customer_trigger
before delete on customer_details
for each row
begin
insert into customer_bkp_trig values
(:old.customer_id,:old.first_name,:old.mobile,:old.address,
:old.zipcode,:old.country,sysdate,user);
end;
delete from customer_details where customer_id=100003;
rollback;
—————————————————————
DDL Trigger:Â
============
create table DDL_TRIGGER_TABLE_LOG
(
oracle_obj_name varchar2(50),
oracle_user varchar2(50),
ddl_execution_date date,
ora_system_event varchar2(50),
oracle_obj_type varchar2(50),
oracle_obj_owner varchar2(50)
);
—– DDL Trigger Syntax———————-
create or replace trigger trigger_name
after/before ddl/logon/logoff on database/schema
begin
executatble statement;
end;
———————————————-
create or replace trigger ddl_trigger
after ddl on schema
begin
insert into DDL_TRIGGER_TABLE_LOG
values(ora_dict_obj_name,ora_login_user,sysdate, ora_sysevent,ora_dict_obj_type,ora_dict_obj_owner);
end;
drop table customer_bkp;
select * from DDL_TRIGGER_TABLE_LOG;
— First drop the trigger then drop associated table
drop trigger ddl_trigger;
drop table DDL_TRIGGER_TABLE_LOG;
=========================================================================================
logon/logoff Trigger:Â
create table user_event_log
(
ora_login_user varchar2(30),
ora_sysevent varchar2(30),
creation_date date);
create or replace trigger logon_ddl_trigger
after logon on schema
begin
insert into hr.user_event_log
values(ora_login_user,sysdate, ora_sysevent);
end;
select * from user_event_log;
=========================================================================================
Order of Trigger execution:Â
===================
create table student
(
sno number,
sname varchar2(50),
sdoj date,
sresult varchar2(30)
);
create sequence log_seq;
create or replace trigger st_bf_ins
before insert on student
begin
DBMS_OUTPUT.PUT_LINE(‘Statement level- before Insert :’||log_seq.nextval);
end;
create or replace trigger row_bf_ins
before insert on student
for each row
begin
DBMS_OUTPUT.PUT_LINE(‘Row level- before Insert :’||log_seq.nextval);
end;
create or replace trigger row_af_ins
after insert on student
for each row
begin
DBMS_OUTPUT.PUT_LINE(‘Row level- After Insert :’||log_seq.nextval);
end;
create or replace trigger st_af_ins
after insert on student
begin
DBMS_OUTPUT.PUT_LINE(‘Statement level- after Insert :’||log_seq.nextval);
end;
set serveroutput on
begin
insert into student (sno,sname,sdoj,sresult) values(1,’Arun’,sysdate,’Pass’);
end;
select * from student;
———————————————————–
Instead of triggers:
———————
select * from employees;
desc employees;
select * from departments;
create or replace view emp_v
as select EMPLOYEE_ID, FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,
JOB_ID from employees where department_id=30;
select * from emp_v;
insert into emp_v values (300,’Arun’,’Kumar’,’Arun@gmail.com’,sysdate,’PU_CLERK’);
create or replace view emp_dept_v
as select e.EMPLOYEE_ID, e.FIRST_NAME,e.LAST_NAME,e.EMAIL,e.HIRE_DATE,e.JOB_ID,e.SALARY,e.DEPARTMENT_ID,
d.DEPARTMENT_NAME from employees e , departments d
where e.department_id=d.department_id;
select * from emp_dept_v;
insert into emp_dept_v values (301,’Arun’,’Kumar’,’Arun@gmail.com’,sysdate,’SA_REP’,50000,20,’Purchasing’);
create or replace trigger tr_emp_dept_vw_instead
instead of insert on emp_dept_v
declare
check_exist number;
begin
select count(*) into check_exist from departments where department_id=:new.department_id;
if check_exist=0 then
insert into departments (department_id,department_name)
values(:new.department_id,:new.department_name);
end if;
select count(*) into check_exist from employees where employee_id=:new.employee_id;
if check_exist=0 then
insert into employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
values(:new.EMPLOYEE_ID,:new.FIRST_NAME,:new.LAST_NAME,:new.EMAIL,:new.HIRE_DATE,:new.JOB_ID,:new.SALARY,:new.DEPARTMENT_ID);
end if;
end;
insert into emp_dept_v values (301,’Arun’,’Kumar’,’Arun1@gmail.com’,sysdate,’SA_REP’,50000,280,’Purchasing’);
select * from employees;
select * from departments;
——————————————————————
— Mutating table
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it.” message.
A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.
create table customer1
(
cust_id number,
cust_name varchar2(100)
);
create table customer2
(
cust_id number,
cust_name varchar2(100)
);
insert into customer1 values (200,’NIC IT Academy’);
Commit;
create or replace trigger mutate_trigger
after insert on customer2
for each row
begin
update customer1 set cust_id =(select max(cust_id) from customer2);
end;
insert into customer2 values (100,’NIC’);
==========================
How to fix mutating error:
==========================
— Trigger will get fired, trying to update data in customer1
— 2nd transaction is depends on customer2 table
— but customer2 itself is not commited
— 2nd table is not yet committed, 1st table transaction depends on 2nd table
— we have to make these two transactions independently using automnomous transactions
create or replace trigger mutate_trigger
after insert on customer2
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update customer1 set cust_id =(select max(cust_id) from customer2);
commit;
end;
select * from customer1;
insert into customer2 values (100,’NIC’);
