Mastering SQL for Data Science: A Comprehensive Guide Day-13
Mastering SQL for Data Science: A Comprehensive Guide Day-13
Trigger
Trigger defines an action the database should take when some database related event occurs. Trigger may be used to supplement declarative referential integrity, to enforce complex business rules, or to audit changes to data. The code within a trigger, called a trigger body, is made up of PL/SQL blocks.
The execution of triggers is also transparent to the user. Triggers are also executed by the database when specific types of data manipulation commands are performed on specific tables.
In other words, a trigger is a PL/SQL block that is associated the table, stored in a table and executed in response to a specific data manipulation event. Triggers can be executed or fired in response to the following events;
ü A row inserted into a table
ü A row updated into a table
ü A row deleted from a table
It is not possible to define a trigger to fire when a row is selected.
Ø Types of triggers:
A trigger’s type is defined by the type of triggering transaction and by the level at which the trigger is executed.
1) Row level Trigger: Row level triggers execute once for each row in a transaction. Row level triggers are the most common type of trigger, they are often used in a data auditing application.
2) Statement level Trigger: Statement level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into a table, then a statement level trigger on that table would only be executed once.
3) Before and after Trigger: If you need to set a column value in an inserted row via your trigger, then you need to use a before insert trigger to access the new values, using an after insert trigger would not allow you to set the inserted value, since the row will already have been inserted into the table.
4) Instead of Trigger: You can use of instead of trigger to tell oracle what to do instead performing the actions that invoked the trigger, For example, you would use an trigger on a view to redirect inserts into table or to update multiple tables that are part of a view.
Ø Use of Triggers:
ü Enforcing business rules
ü Maintaining referential integrity
ü Enforcing security
ü Maintaining a historical log of changes
ü Generating column values, including primary key values
ü Replicating data
Syntax:
Create or replace trigger <trigger_name>
[before|after|instead of ]
[insert|update|delete]
On <table/view>
For each [row|statement]
When <condition>
E.g: — create a trigger to convert name into upper case when user insert or update any record within d_emp table
Create or replace trigger t1
Before insert or update
On d_emp
For each row
Begin
:new.name:=upper(:new.name);
End;
/
E.g: –create a trigger that is fired when an update or delete operation is performed on the d_emp table. The trigger first checks for the operations being performed on the table, then depending on the operation being performed, a variable assigned the value update or delete. Previous values modified by the user, will insert into the emp_back table.
Create or replace trigger tri1
After update or delete
On d_emp
For each row
Declare
Op varchar2(10);
Begin
if updating then
op:=’update’;
end if;
if deleting then
op:=’delete’;
end if;
insert into emp_back values (:old.emp_no,:old.ename,:old.sal,op,sysdate);
End;
/
–Instead of Trigger
When user will create a complex/Readable view, at that time user can’t insert any new record within a readable view, because it affects multiple tables at a time. So, user can use instead of trigger to insert a new record like;
E.g:
create or replace trigger view_join
instead of insert on empview
for each row
begin
insert into dept(deptno,dname,location) values (:new,depnto,:new.dname,:new.location);
insert into d_emp1(empno,ename,job,sal,deptno) values(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);
end;
/