TriggerTrigger is a stored procedure that defines an action that the database should take when some database related statement such as insert/update/delete occurs. Using triggers we can create custom constraints (Checks) to control the integrity of the data.
Trigger Syntax
Create [or replace] trigger <trigger_name>
before/after insert/update/delete
[of column_name, ... ]
on table_name
[for each row]
[when condition]
Begin
SQL Statements
End;
/
Triggers Types
Triggers are of mainly three types
DML triggers can either be a row and statement triggers or before and after triggers
Row-level triggers: These executes once for each row in a transaction, that is row-level triggers fire once for every single row processed by DML statements. A row-level trigger is identified by For Each Row clause.
Statement level triggers: statement-level triggers are the default type of triggers and these are identified by omitting the For Each Row clause. This type of trigger is fired once on the behalf of triggering statement.
Before triggers: These triggers execute the trigger action before the triggering statement (Insert/delete/update) is executed.
After triggers: These triggers execute the trigger action after the event for which it is defined is executed.
Instead of triggers: These are written especially for modifying views (Virtual table) because these can not be directly modified through SQL, DML statements. So by using Instead of triggers we can perform update/delete or insert operation directly on the corresponding table
Enabling or Disabling trigger: For this, we can use the following command as per the following syntax
Syntax
Alter trigger <triggger_name> {disable/enable};
e.g.
Alter trigger sam_trigger disable;
Similarly to enable a trigger we can use the same syntax like
Alter trigger sam_trigger enable;
Dropping a trigger: We can drop or delete a trigger by using the following syntax
Syntax
Drop trigger <triggger_name>;
e.g.
Drop trigger sam_trigger;
Example of trigger
Create or replace trigger upd_trigger
before update of sal on emp
for each row
begin
if :new.sal < :old.sal then
raise_application_error(-20001, 'Salary can not be reduced');
end if;
end
/
Then we can check above trigger working by using the following statement
Update emp set sal=700 where ename='SMITH';
Trigger Syntax
Create [or replace] trigger <trigger_name>
before/after insert/update/delete
[of column_name, ... ]
on table_name
[for each row]
[when condition]
Begin
SQL Statements
End;
/
Triggers Types
Triggers are of mainly three types
- DML(Data Manipulation Language) triggers on table for Insert/delete/update statements
- Instead of triggers on views
- System triggers on the database or schema.
DML triggers can either be a row and statement triggers or before and after triggers
Row-level triggers: These executes once for each row in a transaction, that is row-level triggers fire once for every single row processed by DML statements. A row-level trigger is identified by For Each Row clause.
Statement level triggers: statement-level triggers are the default type of triggers and these are identified by omitting the For Each Row clause. This type of trigger is fired once on the behalf of triggering statement.
Before triggers: These triggers execute the trigger action before the triggering statement (Insert/delete/update) is executed.
After triggers: These triggers execute the trigger action after the event for which it is defined is executed.
Instead of triggers: These are written especially for modifying views (Virtual table) because these can not be directly modified through SQL, DML statements. So by using Instead of triggers we can perform update/delete or insert operation directly on the corresponding table
Enabling or Disabling trigger: For this, we can use the following command as per the following syntax
Syntax
Alter trigger <triggger_name> {disable/enable};
e.g.
Alter trigger sam_trigger disable;
Similarly to enable a trigger we can use the same syntax like
Alter trigger sam_trigger enable;
Dropping a trigger: We can drop or delete a trigger by using the following syntax
Syntax
Drop trigger <triggger_name>;
e.g.
Drop trigger sam_trigger;
Example of trigger
Create or replace trigger upd_trigger
before update of sal on emp
for each row
begin
if :new.sal < :old.sal then
raise_application_error(-20001, 'Salary can not be reduced');
end if;
end
/
Then we can check above trigger working by using the following statement
Update emp set sal=700 where ename='SMITH';
UNIT-306
PL/SQL