1
Create or replace trigger secure_emp
Before
Insert or update or delete
On emp
-- for each row
-- declare
Begin
IF
(
to
_char (sysdate,’DY’) in (‘THU’,’FRI’)
OR
To_number (To_char (sysdate,’HH24’) not between 8 and 18))
THEN
Raise_application_error (-20500, ‘
You May Only Insert Into EMP During
Normal Hours’)
;
End if;
End;
****************************Execution *****************************
INSERT INTO emp (empno, ename, deptno)
VALUES (7745, ‘ALI’, 40);
2
Create or Replace Trigger
secure_emp
Before
Insert or Update or Delete
On emp
Begin
IF
(
to
_char (sysdate,’DY’) in (‘THU’,’FRI’)
OR
To_number (To_char (sysdate,’HH24’) not between 8 and 18))
THEN
IF
Inserting
THEN
Raise_application_error
(
-20500
, 'You may only insert into
EMP
during normal
hours');
ELSIF
Deleting
THEN
Raise_application_error
(-20502
,’you may only delete from
EMP
during normal
hours');
ELSIF
Updating
('SAL')
THEN
Raise_application_error
(-20503,'You may only update
SAL
during normal hours');
ELSE
Raise_application_error
(-20504,'You may only update
EMP
during normal hours');
END IF;
END if;
End;
Create table audit_table
(user_name
varchar2(20),
Tablename
varchar2(20),
Column_name
varchar2(20),
Ins
number(5),
Upd
number(5),
Del
number(5),
Max_ins
number(5),
Max_upd
number(5),
Max_del
number(5) );
Insert into audit_table
Values (‘SCOTT’, ‘EMP’, ‘SAL’, null , 1 , null , null , 5, null);
3
4
Create or replace trigger
check_salary_count
After
Update of sal
On emp
-- for each row
Declare
V_salary_changes
NUMBER
;
V_max_changes
NUMBER
;
Begin
Update
audit_table
Set upd = upd + 1;
Select
upd
,
max_upd
into
v_salary_changes
,
v_max_changes
From
audit_table
Where
user_name = USER
AND
tablename = 'EMP'
AND
column_name = ‘SAL’
;
IF
v_salary_changes
>
v_max_changes
THEN
Raise_application_error (-20501
,
'
You may only make a maximum of
'
|| TO_CHAR
(
v_max_changes
)
||
'
changes to
the SAL column
')
;
END IF;
END;
Update emp
Set sal= sal + 500;
5
****** Instead of **** >>>>
VIEW
*****
Create or replace view
v1
As
Select empno
,
ename , sal
From emp
Where
sal > 4000;
-----------------------------------------------------------------------
Create or replace view
v2
As
Select empno, ename, sal
From emp
Where sal > 1000000;
--------------------------------------------------------------------------------------------
Create or replace trigger
diverting
Instead of
Insert or update
On
v1
For each row
-- declare
Begin
Insert
into v2
Values (1521 , 'mohamed' , 2000 );
End;
/
**********************************************************************
Insert
into v1
Values (3566 , 'ahmed ' , 15000);
