Trigger
Both tables and databases can have triggers.
Table triggers are mechanisms that run before or after insert, update or delete operations.
Example: When there is a link between 2 databases, in a situation where one had an insert on table X a trigger can be used so that the same data can automatically be inserted on table Y (insert trigger – before insert, after insert).
A trigger can also be used in a situations such as preventing making changes outside of working hours, by configuring the trigger to check the working hours.
Types of triggers
Statement Level Trigger: Triggered whenever a particular statement is run. (example: blocking inserts during specified hours).
Row Level Trigger: Works on every row in a table. Used in scenarios where changes made are to be mirrored in another location.
Instead of Trigger: Can update xx fields, while using a view. Doesn’t have a wide variety of usage.
Example: To prevent delete/update/insert operations to the employees table outside of working hours
|
1 2 3 4 5 6 7 8 9 10 11 12 |
create or replace trigger security_time_check before insert or update or delete on emp -- can also be declared as: before update of salary on emp declare day varchar2(3); hour number(2); begin day := to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH'); hour := to_number(to_char(sysdate, 'fmHH24')); if (day = 'SATURDAY' OR day = 'SUNDAY') OR (hour not between 8 and 17) then raise_application_error(-20100, 'Changes outside of working hours are not allowed'); end if ; end; |
To disable the trigger:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
alter trigger security_time_check disable ; create or replace trigger security_time_check before insert or update or delete on emp declare day varchar2(3); hour number(2); begin day := to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH'); hour := to_number(to_char(sysdate, 'fmHH24')); if (day = 'SATURDAY' OR day = 'SUNDAY') OR (hour not between 8 and 17) then if inserting then raise_application_error(-20100, 'Insert operations are not allowed outside of working hours'); elsif updating then raise_application_error(-20100, 'Update operations are not allowed outside of working hours'); else raise_application_error(-20100, 'Delete operations are not allowed outside of working hours'); end if; end if ; end;up |
Row Level Triggers
for each row: applies to every row in the table
Example: when updating the salary, it shouldn’t be below the average
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create or replace trigger bigger_than_av_salary before insert or update or delete of salary on emp for each row declare av_salary number ; begin select round(avg(salary)) into av_salary from emp ; if :new.salary < av_salary then -- new salary raise_application_error(-20100, 'The new value for salary cannot be less than the average salary of ('||av_salary||')'); end if ; end; |
As this codeblock works on a row level, it’s unable to find the average salary
The log table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
create table emp_log_table ( user_name varchar2(20), date_time date, table_name varchar2(30), transaction varchar2(10), employees_id number, old_salary number, new_salary number ) create or replace trigger trg_emp_salary_log after update of salary on emp for each row declare begin INSERT INTO HR.EMP_LOG_TABLE ( USER_NAME, DATE_TIME, TABLE_NAME, TRANSACTION, EMPLOYEES_ID, OLD_SALARY, NEW_SALARY) VALUES (user ,sysdate ,'employees' ,'update',:new.employee_id , :old.salary , :new.salary ); end; |
Create a table
|
1 2 3 4 5 6 |
create table customer ( customer_id number primary key, name varchar2(20), surname varchar2(20) ) |
Create a sequence
|
1 2 3 4 5 6 |
create table customer ( customer_id number primary key, name varchar2(20), surname varchar2(20) ) |
Create a trigger
|
1 2 3 4 5 6 7 8 9 |
create or replace trigger trg_bef_ins_customer before insert on customer for each row declare begin select seq_customer.nextval into :new.no from dual; end; |
To check
|
1 2 |
insert into customer(name, surname) values ('Testing', 'Mistake') |
The ‘new’ and ‘old’ keywords can only be used for each row, i.e. on a row level and not on a statement level.
The best way to add a field to an Auto increment is by using both a sequence and a trigger.
Example: To log the users and dates of transactions.
|
1 2 3 4 5 6 |
create table customer_log ( user_name varchar2(20), transaction varchar2(10), date_time date ) |
Create a procedure
|
1 2 3 4 5 6 |
create or replace procedure proc_log_customer (user_name varchar2, transaction varchar2) as begin insert into customer_log values(user_name, transaction, sysdate); end; |
Create a trigger
|
1 2 3 4 5 6 7 |
create or replace trigger trg_after_ins_customer after insert on customer for each row declare begin proc_log_customer(user, 'insert'); end; |
Alternatively
|
1 2 3 |
create or replace trigger trg_after_ins_customer_1 after insert on customer call proc_log_customer(user,'insert') |
Note: If the previous 3 lines of code are placed between a begin and an end, it won’t be called.
To check
|
1 2 |
alter trigger trg_after_ins_customer compile alter trigger trg_after_ins_customer disable/enable |
In newer DBs, fields that used to store data in long type, now store them as CLOBs (character large objects).
The Long data type can hold upto 2GBs of data, whereas CLOB can hold upto 4GB. Another advantage of using CLOB over Long is that is isn’t possibie to search for text within a Long field type.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create table textbody1 ( id number, textbody long ); create table textbody2 ( id number, textbody clob ); insert into textbody1 values(1,'test test') insert into textbody2 values(1,'test test') select * from textbody1 where textbody like '%test%' |
This previous query returns a result for textbody2 but gives an error for textbody1.
type… table
Declared as ‘index by table’. Choosing between Binary integer or PLS_integer, PLS is preferred as it is faster.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
declare type count_list is table of number index by pls_integer; numbers count_list; begin FOR i IN 1 .. 6 LOOP numbers(i) := i + 5; END LOOP; FOR i IN numbers.first..numbers.last LOOP dbms_output.put_line(numbers(i)); END LOOP; end; declare type count_list is table of number index by pls_integer; numbers count_list; begin FOR i IN 1 .. 6 LOOP numbers(i) := i + 5; END LOOP; FOR i IN count_list.first..count_list.last LOOP dbms_output.put_line(count_list(i)); END LOOP; dbms_output.put_line('number:'||count_list.count); end; declare type count_list is table of number index by pls_integer; numbers count_list; begin FOR i IN 1 .. 6 LOOP numbers(i) := i + 5; END LOOP; FOR i IN numbers.first..numbers.last LOOP dbms_output.put_line(numbers(i)); END LOOP; dbms_output.put_line('number:'||numbers.count); if (numbers.exists(6)) then dbms_output.put_line('There are 6 employees.'); end if; end; |
Checking to see whether there are 6 employees:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
output 6 7 8 9 10 11 count:6 There are 6 employees numbers.first numbers, takes it to 1. numbers.last numbers, takes it to 6. numbers.count numbers, shows the total number of employees. numbers.exists, whether specified numbers exist, i.e. is there 0, or 6, or 7, etc. declare type count_list is table of number index by pls_integer; numbers count_list; begin FOR i IN 1 .. 6 LOOP numbers(i) := i + 5; END LOOP; FOR i IN numbers.first..numbers.last LOOP dbms_output.put_line(numbers(i)); END LOOP; dbms_output.put_line('number:'||numbers.count); if (numbers.exists(6)) then dbms_output.put_line('There are 6 employees.'); end if; end; |
Using RMAN for...
12 March 2019