Statement Level trigger VS Row Level (Part 1)
drop table t1;
create table t1
( emp_id number,
ename varchar2(100)
);
insert into t1 values (1,'ford');
insert into t1 values (2,'aya');
---statement trigger
create or replace trigger t1_b4_update
before update --this timeing + event
on t1
begin
DBMS_OUTPUT.PUT_LINE(':)');
end;
update t1
set ename=ename||' s';
update t1
set ename=ename||' s'
where emp_id=5555;
------------------------
Statement Level trigger VS Row Level (Part 2)
==================================================
--row trigger
create or replace trigger t1_b4_update
before update --this timeing + event
on t1
for each row
begin
DBMS_OUTPUT.PUT_LINE(':)');
end;
update t1
set ename=ename||' s';
update t1
set ename=ename||' s'
where emp_id=5555;
------------------------------------------------------------------
Triggers common cases
==========================
Statement Level trigger Example 1
========================================
delete from departments;
-- let us do statment level trigger
create or replace trigger dept_check_time
before
insert or update or delete
on DEPARTMENTS
begin
if to_number (to_char(sysdate,'hh24') ) not between 8 and 16 then
raise_application_error(-20010, 'DML operations not allowed now ');
end if;
end;
--if to_number(to_char(sysdate,'hh24')) not between 8 and 16 then:
--This line checks if the current hour (extracted from sysdate)
--is not within the range of 8 AM to 4 PM (16:00 hours).
--try to test the trigger
delete from departments;
delete from departments
where department_id=-4;
select * from user_objects
where object_name='DEPT_CHECK_TIME';
select * from user_triggers
where trigger_name='DEPT_CHECK_TIME';
-------------------------------
Statement Level trigger Example 2
========================================
create or replace trigger dept_check_time
before
insert or update or delete
on DEPARTMENTS
begin
if to_number (to_char(sysdate,'hh24') ) not between 8 and 16 then
if inserting then
raise_application_error(-20010, 'Insert operations not allowed now ');
elsif deleting then
raise_application_error(-20011, 'Delete operations not allowed now ');
elsif updating then
raise_application_error(-20012, 'Update operations not allowed now ');
end if;
end if;
end;
delete from departments;
update departments
set department_name='x'
where department_id = -5;
--------------------------------------------------------------------------------------------
Understanding the NEW & OLD qualifiers
Row level trigger example
======================================
DROP TABLE EMP_COPY;
CREATE TABLE EMP_COPY
AS SELECT * FROM employees;
SELECT * FROM EMP_COPY;
create or replace trigger check_sal
before
insert or update of salary
on
EMP_COPY
for each row
begin
if :new.salary<500 then
raise_application_error(-20030, 'min sal is 500');
end if;
end;
-------------------
update EMP_COPY
set salary=200
where employee_id=100;
UPDATE EMP_COPY
set salary=200;
SELECT COUNT(1) FROM EMP_COPY;
select * from user_triggers
where trigger_name='CHECK_SAL';
--------------------------------------------------------------------------------
create or replace trigger check_sal
before
insert or update of salary
on
EMP_COPY
REFERENCING NEW AS NEW OLD AS OLD
for each row
begin
if :new.salary<500 then
dbms_output.put_line('s');
end if;
end;
update EMP_COPY
set salary=200
where employee_id=100;
update EMP_COPY
set salary=200;
----
create or replace trigger check_sal
before
insert or update of salary
on
EMP_COPY
REFERENCING NEW AS N OLD AS O
for each row
begin
if :n.salary<500 then
dbms_output.put_line('s');
end if;
end;
--------
update EMP_COPY
set salary=200
where employee_id=100;
update EMP_COPY
set salary=200;
-----------------------------------------------------------------------------------------------------
Row level trigger (create Audit table)
============================================
drop table emp_copy;
create table emp_copy
as select * from employees;
select * from emp_copy;
drop table EMP_SAL_AUDIT;
CREATE TABLE EMP_SAL_AUDIT
( EMP_ID NUMBER,
OPERATION VARCHAR2(20),
OLD_SAL NUMBER,
NEW_SAL NUMBER,
OP_DATE DATE,
BY_USER VARCHAR2(100)
);
--now we need to create trigger for audit
create or replace trigger emp_copy_sal_audit
after insert or update of salary or delete
on emp_copy
for each row
begin
if inserting then
insert into EMP_SAL_AUDIT(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
values (:new.employee_id,'Inserting',null,:new.salary,sysdate,user);
end if;
if updating then
insert into EMP_SAL_AUDIT(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
values (:old.employee_id,'updating',:old.salary,:new.salary,sysdate,user);
end if;
if deleting then
insert into EMP_SAL_AUDIT(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
values (:old.employee_id,'deleting',:old.salary,null,sysdate,user);
end if;
end;
--note1: we dont use commit or rollback in triggers
--the commit or rollback should be in the main transaction(DML)
--1 testing the insert operation
insert into emp_copy (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,salary,hire_date,job_id)
values (1,'ahmed','naser','ahmed.n@hotmail.com',900,sysdate,'AD_PRES');
select * from emp_copy
where EMPLOYEE_ID=1;
select EMP_ID,OPERATION,OLD_SAL,NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss')OP_DATE ,BY_USER
from EMP_SAL_AUDIT
order by OP_DATE;
--2 testing the update operation
update emp_copy
set salary=salary +10
where department_id=20;
select EMP_ID,OPERATION,OLD_SAL,NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss')OP_DATE ,BY_USER
from EMP_SAL_AUDIT
order by OP_DATE;
--3 Testing the delete
delete from emp_copy
where EMPLOYEE_ID=1;
select EMP_ID,OPERATION,OLD_SAL,NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss')OP_DATE ,BY_USER
from EMP_SAL_AUDIT
order by OP_DATE;
--------------------------------------------------------------------------------------------------------------
Trigger Firing sequence
===============================
--we will create 4 triggers for same table
--1 before statment
--2 before for each row
--3 after each row
--4 after statment
-- we want to know the sequence for firing the triggers
drop table test_emp;
create table test_emp
( emp_id number,
first_name varchar2(100)
);
drop table test_emp_sequence;
create table test_emp_sequence
( seq number,
trigger_type varchar2(100)
);
drop sequence s;
create sequence s;
--1 before statment
create or replace trigger before_insert_stat
before
insert
on test_emp
begin
insert into test_emp_sequence values (s.nextval,'before_insert_stat');
end;
--2 before each row
create or replace trigger before_insert_each_row
before
insert
on test_emp
for each row
begin
insert into test_emp_sequence values (s.nextval,'before_insert_each_row');
end;
--3 after each row
create or replace trigger after_insert_each_row
after
insert
on test_emp
for each row
begin
insert into test_emp_sequence values (s.nextval,'after_insert_each_row');
end;
--4 after statment
create or replace trigger after_insert_stat
after
insert
on test_emp
begin
insert into test_emp_sequence values (s.nextval,'after_insert_stat');
end;
---------------
insert into test_emp
values (1,'mazen');
select * from test_emp_sequence
order by seq;
insert into test_emp
select employee_id, first_name
from
employees
where department_id = 20;
select * from test_emp_sequence
order by seq;
Compile/enable/ disable/ drop trigger
====================================
--to compile trigger
alter trigger after_insert_each_row compile;
--to disable all triggers on a table
alter table test_emp disable all triggers;
--to enbale all triggers on a table
alter table test_emp enable all triggers;
--to disable or enable specific trigger
alter trigger after_insert_stat enable;
alter trigger after_insert_stat disable;
--to drop trigger
drop trigger after_insert_stat;
-----------------------------------------------------------------------------------------------------
Using the triggers for populating a default value
=====================================================
--WE CAN USING triggers for default value
drop table customers;
create table customers
( cust_id number,
name varchar2(100),
status char(1)
);
create or replace trigger customers_defaulf_status
before
insert
on customers
FOR EACH ROW
begin
:new.status:='A';
end;
--------
INSERT INTO customers(cust_id,name)
VALUES(1,'OMAR');
SELECT * FROM customers;
-------------------------------------------------------------------------------------------------------------
Instead of triggers ( on Views)
==================================
delete from employees
where employee_id=10;
DROP VIEW emp_all_v;
create or replace view emp_all_v
as select * from employees;
select * from emp_all_v;
INSERT INTO emp_all_v
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(10,'OMAR','AHMED','TEST@GMAIL.COM',null,sysdate,'AD_PRES',
1500,0,null,90
);
select * from employees
where employee_id=10;
rollback;
--------------------------------------------
create or replace trigger TEST_INSTEAD
instead of
insert
on emp_all_v
begin
DBMS_OUTPUT.PUT_LINE('TEST');
end;
INSERT INTO emp_all_v
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(10,'OMAR','AHMED','TEST@GMAIL.COM',null,sysdate,'AD_PRES',
1500,0,null,90
);
select * from employees
where employee_id=10;
rollback;
------------------------------------------------
create or replace trigger TEST_INSTEAD
instead of
insert
on emp_all_v
begin
INSERT INTO employees
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(:new.EMPLOYEE_ID,:new.FIRST_NAME,:new.LAST_NAME,:new.EMAIL,:new.PHONE_NUMBER,:new.HIRE_DATE,:new.JOB_ID,
:new.SALARY,:new.COMMISSION_PCT,:new.MANAGER_ID,:new.DEPARTMENT_ID
);
DBMS_OUTPUT.PUT_LINE('TEST');
end;
INSERT INTO emp_all_v
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(10,'OMAR','AHMED','TEST@GMAIL.COM',null,sysdate,'AD_PRES',
1500,0,null,90
);
select * from employees
where employee_id=10;
No comments:
Post a Comment