ORACLE trigger 5-instead of trigger

The instead of trigger can be used to insert, update, delete, and merge tables in a view composed of more than one table. Instead of can also be used to create partition tables.

Syntax for creating an instead of trigger:

1 Create [or replace] trigger trigger_name
2 intead of Operation
3 On view_name
4 For each row
5 begin
6... code goes here...
7 end;

Next, we will test the instead of insert, instead of update, instead of Delete triggers, and instead of triggers for nested tables.

1. Create a test table: employee, permission_code, user_role

Create Table employee (
Employee_no varchar2 (8 ),
Last_name varchar2 (25) not null,
First_name varchar2 (10) Not null,
Dept_code varchar2 (3) not null,
Active_flag varchar2 (1) default 'y ',
Mod_user_id varchar2 (30) Default User,
Mod_user_date date default sysdate );

Create Table permission_code (
Pcode varchar2 (2 ),
Pcode_description varchar2 (40) not null,
Mod_user_id varchar2 (30) Default User,
Mod_user_date date default sysdate );

Create Table user_role (
Dept_code varchar2 (3 ),
Pcode varchar2 (2 ),
Access_level varchar2 (1) default 'R ',
Mod_user_id varchar2 (30) Default User,
Mod_user_date date default sysdate );

Create Table user_permission (
Employee_no varchar2 (8 ),
Pcode varchar2 (2 ),
Access_level varchar2 (1) default 'R ',
Mod_user_id varchar2 (30) Default User,
Mod_user_date date default sysdate );

Create Table dept_code (
Dept_code varchar2 (3 ),
Dept_name varchar2 (30 ));

Create Table Test (
Test varchar2 (20 ));

2. Insert test data into the table:

-- Employee table
Insert into employee
(Employee_no, last_name, first_name, dept_code, active_flag)
Values
('20140901', 'mark', 'townsend', 'lcr ', 'y ');

Insert into employee
(Employee_no, last_name, first_name, dept_code, active_flag)
Values
('123456', 'dacko', 'carol ', 'asc', 'y ');

Insert into employee
(Employee_no, last_name, first_name, dept_code, active_flag)
Values
('123', 'Morgan ', 'Daniel', 'adm ', 'y ');

-- Permission_code table data
Insert into permission_code
Values ('bo', 'billing options', user, sysdate );

Insert into permission_code
Values ('cl', 'class Code', user, sysdate );

Insert into permission_code
Values ('cr ', 'crews', user, sysdate );

Insert into permission_code
Values ('ct ', 'crew types', user, sysdate );

Insert into permission_code
Values ('cu ', 'customer types', user, sysdate );

Insert into permission_code
Values ('dh ', 'workorder Dash Numbers', user, sysdate );

Insert into dept_code (dept_code, dept_name)
Values ('adm ', 'admin ');

Insert into dept_code (dept_code, dept_name)
Values ('coo ', 'coordinator ');

Insert into dept_code (dept_code, dept_name)
Values ('ese ', 'Electrical Service ');

Insert into dept_code (dept_code, dept_name)
Values ('asc', 'Electrical service rep ');

Insert into dept_code (dept_code, dept_name)
Values ('eng', 'engine ');

Insert into dept_code (dept_code, dept_name)
Values ('lcr ', 'Line crew ');

Insert into dept_code (dept_code, dept_name)
Values ('mcr', 'meter crew ');

Insert into dept_code (dept_code, dept_name)
Values ('non', 'network engine ');

Insert into dept_code (dept_code, dept_name)
Values ('sks', 'sketch artlist ');

Insert into user_role
(Dept_code, pcode, access_level)
Select R. dept_code, P. pcode, 'R'
From dept_code R, permission_code P;

Insert into user_permission
(Employee_no, pcode, access_level)
Select E. employee_no, R. pcode, R. access_level
From employee e, user_role R
Where E. dept_code = R. dept_code;

Commit;

3. Create the role_permission_view and employee_permission_view views and test whether the View data can be updated or deleted.

Create or replace view role_permission_view
Select R. dept_code, R. pcode, P. pcode_description, R. access_level
From user_role R, permission_code P
Where R. pcode = P. pcode;

-- This will fail
Insert into role_permission_view
(Dept_code, pcode, pcode_description, access_level)
Values
('Dan', 'dm', 'Morgan ', 'w ');

-- This will fail too
Update role_permission_view
Set access_level = 'W'
Where dept_code = 'sks ';

-- Another Relational View
Create or replace view employee_permission_view
Select E. employee_no,
E. first_name | ''| E. last_name name, E. dept_code,
R. pcode, R. access_level defacclvl, U. access_level,
P. pcode_description
From employee e, user_role R, user_permission U, permission_code P
Where E. dept_code = R. dept_code
And E. employee_no = U. employee_no
And R. pcode = U. pcode
And R. pcode = P. pcode
Order by 1, 3;

-- This will fail too
Delete from employee_permission_view
Where dept_code = 'lcr ';

4. Example of instead of insert trigger:

Create or replace trigger ioft_insert_role_perm
Instead of insert
On role_permission_view
For each row
Declare
X integer;
Begin
Select count (*)
Into X
From permission_code
Where pcode =: New. pcode;

If x = 0 then
Insert into permission_code
(Pcode, pcode_description, mod_user_id, mod_user_date)
Values
(: New. pcode, 'new code', user, sysdate );
End if;

Select count (*)
Into X
From dept_code
Where dept_code =: New. dept_code;

If x = 0 then
Insert into dept_code
(Dept_code, dept_name)
Values
(: New. dept_code, 'new dept ');
End if;

Insert into user_role
(Dept_code, pcode, mod_user_id)
Values
(: New. dept_code,: New. pcode, 'Morgan ');

Insert into test
(Test)
Values
('Z ');
End ioft_insert_role_perm;
/

Select *
From permission_code
Where pcode = 'dm ';

Select *
From dept_code
Where dept_code = 'dan ';

Select *
From user_role
Where dept_code = 'dan ';

Select * from test;

-- Insert works
Insert into role_permission_view
(Dept_code, pcode, pcode_description, access_level)
Values
('Dan', 'dm', 'Morgan ', 'w ');

-- View results
Select *
From permission_code
Where pcode = 'dm ';

Select *
From dept_code
Where dept_code = 'dan ';

Select *
From user_role
Where dept_code = 'dan ';

Select * from test;

5. Example of instead of update trigger:

Create or replace trigger ioft_role_perm
Instead of update
On role_permission_view
For each row
Begin
Update user_role
Set access_level =: New. access_level,
Mod_user_id = user,
Mod_user_date = sysdate
Where dept_code =: Old. dept_code
And permission_code =: Old. permission_code;
End ioft_role_perm;
/

Select trigger_name, trigger_type, action_type,
Description
From user_triggers;

Select * From employee_permission_view;

Update role_permission_view
Set access_level = 'W'
Where dept_code = 'sks ';

Select * From employee_permission_view;

Update employee_permission
Set access_level = 'Z ';

6. Example of instead of Delete trigger:

Select * From employee_permission_view;

Select * From dept_code;

Select * from employee;

Create or replace trigger ioft_emp_perm
Instead of Delete
On employee_permission_view
For each row
Begin
Delete from dept_code
Where dept_code =: Old. dept_code;

Update employee
Set dept_code = NULL,
Mod_user_id = user,
Mod_user_date = sysdate
Where dept_code =: Old. dept_code;

Delete from test
Where test = 'Z ';
End ioft_emp_perm;
/

Select * From employee_permission_view;

Delete from employee_permission_view
Where dept_code = 'lcr ';

Desc employee

Delete from employee_permission_view
Where dept_code = 'lcr ';

7. Example of an instead of trigger for a nested table:

Conn Scott/Tiger

Create or replace type emp_type as object (
Empno number (4 ),
Ename varchar2 (10 ),
Job varchar2 (9 ),
Mgr number (4 ),
Hiredate date,
Sal number (7, 2 ),
Comm number (7, 2 ));
/

Create or replace type emp_tab_type as table of emp_type;
/

Create or replace type dept_type as object (
Deptno number (2 ),
Dname varchar2 (14 ),
Loc varchar2 (13 ),
EMPs emp_tab_type );
/

Create or replace view dept_or of dept_type
With object identifier (deptno)
Select deptno, dname, Loc, cast (Multiset (
Select empno, ename, job, Mgr, hiredate, Sal, comm
From EMP
Where EMP. deptno = Dept. deptno) as emp_tab_type)
From dept;
/

Create or replace trigger dept_emplist_tr
Instead of update on nested table EMPs of dept_or
Referencing new as new parent as parent
For each row
Begin
Dbms_output.put_line ('new: '|: New. Job );
Dbms_output.put_line ('parent: '|: parent. dname );
End;
/

Set serveroutput on

Update table (
Select P. EMPs
From dept_or P
Where deptno = 10)
Set ename = lower (ename );

------------------------------------------------------------------ Test and organize the content from the Internet ----------------------------------------------------------------------------