1 Follows Statement in triggers.
drop table emp;
create table emp
(id number,
name varchar2(100)
);
drop table which_fired_first;
create table which_fired_first
(seq number,
trigger_name varchar2(100)
);
drop sequence s1;
create sequence s1;
create or replace trigger t1
before
insert
on emp
begin
insert into which_fired_first values ( s1.nextval,'t1');
end;
create or replace trigger t2
before
insert
on emp
begin
insert into which_fired_first values ( s1.nextval,'t2');
end;
insert into emp values (1,'khaled');
select * from which_fired_first
order by seq;
delete from which_fired_first
create or replace trigger t2
before
insert
on emp
follows t1
begin
insert into which_fired_first values ( s1.nextval,'t2');
end;
insert into emp values (1,'khaled');
select * from which_fired_first
order by seq;
2 Understanding Compound triggers
drop table test_emp;
create table test_emp
( emp_id number,
first_name varchar2(100)
);
---now we will compound theses 4 triggers into one trigger
create or replace trigger comp_test
for insert or update or delete
on test_emp
compound trigger
--we can define vaiables here
--x number;
before statement is
--y number;
begin
DBMS_OUTPUT.PUT_LINE('1');
end before statement;
before each row is
begin
DBMS_OUTPUT.PUT_LINE('2');
end before each row;
after each row is
begin
DBMS_OUTPUT.PUT_LINE('3');
end after each row;
after statement is
begin
DBMS_OUTPUT.PUT_LINE('4');
end after statement;
end;
insert into test_emp
values (1,'mazen');
insert into test_emp
select employee_id, first_name
from
employees
where department_id 20;
3 Understanding Mutating Table
drop table customers;
create table customers
( cust_id number,
cust_name varchar2(100)
);
select max(cust_id) from customers;
select nvl(max(cust_id),0) from customers;
create or replace trigger customers_seq
before insert
on
customers
for each row
declare --use declare when you want define variables
v_max_id number;
begin
select nvl(max(cust_id),0) +1
into v_max_id
from customers;
:new.cust_id: v_max_id;
end;
------------
--now let us try to insert a record
insert into customers (cust_name) values ('oracle');
select * from customers;
insert into customers (cust_name) values ('Khaled');
select * from customers
insert into customers (cust_name)
select first_name
from employees;
--ORA-04091: table HR.CUSTOMERS is mutating
--THE TRIGGER CODE QUERY SAME RECORD inserted
4 Solving Mutating Table issue using compound trigger
select * from employees
where job_id'IT_PROG'
order by salary;
--i want to create trigger that prevent insert/update any employee with jobIT_PROG
--and his salary out of ranges of IT_PROG
create or replace trigger IT_PROG_range
before
insert OR UPDATE
on employees
for each row
when (new.job_id'IT_PROG')
declare
v_min_IT_PROG number;
v_max_IT_PROG number;
begin
select min(salary), max(salary)
into v_min_IT_PROG,v_max_IT_PROG
from employees
where job_id:new.job_id;
if :new.salary not between v_min_IT_PROG and v_max_IT_PROG then
raise_application_error (-20300,'invalid range');
end if;
end;
------
insert into employees
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(900,'NADIA','AHMED','TEST@GMAIL.COM',null,sysdate,'IT_PROG',
1500,0,null,90
);
--NOW THIS WILL GIVE ERROR able HR.EMPLOYEES is mutating
--THE TRIGGER CODE QUERY SAME RECORD UPDATED
UPDATE employees
SET SALARY6000
WHERE employee_ID107;
--the solution is
create or replace trigger IT_PROG_range
for
insert OR UPDATE
on employees
when (new.job_id'IT_PROG')
compound trigger
v_min_IT_PROG number;
v_max_IT_PROG number;
before statement is
begin
select min(salary), max(salary)
into v_min_IT_PROG,v_max_IT_PROG
from employees
where job_id'IT_PROG';
end before statement;
before each row is
begin
if :new.salary not between v_min_IT_PROG and v_max_IT_PROG then
raise_application_error (-20300,'invalid range');
end if;
end before each row;
end;
UPDATE employees
SET SALARY200
WHERE employee_ID107;
5 Using Compound trigger & array to solve Mutating.
select * from employees
where job_id'IT_PROG'
order by salary;
drop trigger IT_PROG_range;
select job_id, min(salary),max(salary)
from employees
group by job_id
order by 1;
create or replace trigger salary_range
before
insert OR UPDATE
on employees
for each row
declare
v_min_sal number;
v_max_sal number;
begin
select min(salary), max(salary)
into v_min_sal,v_max_sal
from employees
where job_id:new.job_id;
if :new.salary not between v_min_sal and v_max_sal then
raise_application_error (-20300,'invalid range');
end if;
end;
select * from employees
where Job_id'IT_PROG'
order by salary;
UPDATE employees
SET SALARY6000
WHERE employee_ID107;
-----------------------------------------
/* we should save the result of query
select job_id, min(salary),max(salary)
from employees
group by job_id
order by 1
into array
*/
create or replace trigger salary_range
for
insert OR UPDATE
on employees
compound trigger
type job_t is record( minsal number,maxsal number);
type emp_t is table of job_t index by varchar2(100);
emp emp_t;
before statement is
begin
for i in(
select job_id, min(salary) min_sal ,max(salary) max_sal
from employees
group by job_id order by 1
)
loop
emp(i.job_id).minsal:i.min_sal;
emp(i.job_id).maxsal:i.max_sal;
end loop;
end before statement;
before each row is
begin
if :new.salary not between emp(:new.job_id).minsal and emp(:new.job_id).maxsal then
raise_application_error (-20300,'invalid range');
end if;
end before each row;
end;
----------
select * from employees
where Job_id'IT_PROG'
order by salary;
UPDATE employees
SET SALARY100
WHERE employee_ID107;
6 Mutating table & ON DELETE CASCADE
drop table DPET1;
CREATE TABLE DPET1
(DEPTNO NUMBER,
DNAME VARCHAR2(100),
CONSTRAINT DPET1_PK PRIMARY KEY (DEPTNO )
);
INSERT INTO DPET1 (DEPTNO,DNAME)
VALUES (1,'HR DEPT');
INSERT INTO DPET1 (DEPTNO,DNAME)
VALUES (2,'PO DEPT');
COMMIT;
SELECT * FROM DPET1;
drop table emp1;
CREATE TABLE EMP1
( EMPID NUMBER PRIMARY KEY,
ENAME VARCHAR2(100),
DEPTNO NUMBER,
CONSTRAINT EMP1_FK FOREIGN KEY (DEPTNO) REFERENCES DPET1(DEPTNO) ON DELETE CASCADE
);
INSERT INTO EMP1 VALUES (1, 'khaled','1');
INSERT INTO EMP1 VALUES (2, 'ali','1');
INSERT INTO EMP1 VALUES (3, 'ahmed','1');
INSERT INTO EMP1 VALUES (4, 'rania','2');
INSERT INTO EMP1 VALUES (5, 'lara','2');
COMMIT;
SELECT * FROM EMP1;
DELETE FROM DPET1
WHERE DEPTNO1;
SELECT * FROM EMP1;
create or replace trigger EMP1_t
before
delete
on EMP1
for each row
declare
minv number;
begin
select min(EMPID)
into minv
from emp1;
end;
DELETE FROM DPET1
WHERE DEPTNO2;
7 Database Triggers VS Stored procedures
8 Creating DDL triggers ( Schema or Database )
drop table DPET1;
CREATE TABLE DPET1
(DEPTNO NUMBER,
DNAME VARCHAR2(100),
CONSTRAINT DPET1_PK PRIMARY KEY (DEPTNO )
);
INSERT INTO DPET1 (DEPTNO,DNAME)
VALUES (1,'HR DEPT');
INSERT INTO DPET1 (DEPTNO,DNAME)
VALUES (2,'PO DEPT');
COMMIT;
SELECT * FROM DPET1;
drop table emp1;
CREATE TABLE EMP1
( EMPID NUMBER PRIMARY KEY,
ENAME VARCHAR2(100),
DEPTNO NUMBER,
CONSTRAINT EMP1_FK FOREIGN KEY (DEPTNO) REFERENCES DPET1(DEPTNO) ON DELETE CASCADE
);
INSERT INTO EMP1 VALUES (1, 'khaled','1');
INSERT INTO EMP1 VALUES (2, 'ali','1');
INSERT INTO EMP1 VALUES (3, 'ahmed','1');
INSERT INTO EMP1 VALUES (4, 'rania','2');
INSERT INTO EMP1 VALUES (5, 'lara','2');
COMMIT;
SELECT * FROM EMP1;
DELETE FROM DPET1
WHERE DEPTNO1;
SELECT * FROM EMP1;
create or replace trigger EMP1_t
before
delete
on EMP1
for each row
declare
minv number;
begin
select min(EMPID)
into minv
from emp1;
end;
DELETE FROM DPET1
WHERE DEPTNO2;
9 Create system event trigger
alter session set containerorclpdb;
show con_name
drop table log_table;
create table log_table
(user_id varchar2(100),
log_date date,
action varchar2(100)
);
create or replace trigger logon_t
after
logon
on database
begin
insert into log_table values (user,sysdate,'logon');
end;
create or replace trigger logoff_t
before
logoff
on database
begin
insert into log_table values (user,sysdate,'logoff');
end;
--use sqlplus and logon by hr
--hr/hr@orclpdb
select user_id,to_char(log_date,'dd-mm-yyyy hh:mi:ss'), action
from log_table;
drop trigger logon_t;
drop trigger logoff_t;
No comments:
Post a Comment