What is Trigger in SQL
Triggers in SQL, especially in PL/SQL, are powerful tools used to automatically execute a specified set of SQL statements in response to certain events on a table or view. These triggers can be activated before or after data manipulation operations such as INSERT, UPDATE, or DELETE. By using triggers, developers can enforce business rules, maintain audit trails, and ensure data integrity effortlessly. PL/SQL triggers are highly customizable, allowing for complex logic to be implemented directly within the database. Leveraging trigger in SQL can significantly enhance the robustness and efficiency of database applications.
A Set Of Pl/sql Statements Stored Permanently In Database And “automatically” Activated When Ever An Event Raising Statement (Dml / Ddl) Is Performed.
They Are Used To Impose User Defined Restrictions(Or)business Rules On Table / Schema.they Are Also Activated When Tables Are Manipulated By Other Users Or By Other Application S/w Tools.they Provide High Security On Tables.they Are Stored In “user_triggers” System Table.T ypes of trigger in sql
1. DML TRIGGERS
2. DDL TRIGGER / DB TRIGGERS
Use Of Trigger in SQL Server
1. To Invoking User Defined Message (Or) Alerts At Event Raise.
2. To Control Dml / Ddl Operations.
3. To Implementing Business Logical Conditions.
4. To Validating Data.
5. For Auditing.
1. DML TRIGGERS:
These Triggers Are Executed By System Automatically When User Perform Dml (Insert / Update / Delete) Operations On A Specific Table.
Syntax:
Create Or Replace Trigger <trigger_name>
Before/after Insert Or Update Or Delete
[ Of <columns>] On <table Name>
[ For Each Row]
When <condition> (True -> Executes The Trigger,
False – Not Execute)
Declare
<variable Declaration>;]
Begin
<exec Statements>;
[ Exception
<exec Statements>;]
End;
Trigger Event:
Indicates When To Activate The Trigger
Before Trigger:
1. First Trigger Body Is Executed
2. Later Dml Command Executed
After Trigger:
1. First Dml Command Executed
2. Later Trigger Body Executed
Trigger Levels:
Trigger Can Create At Two Levels.
A. ROW LEVEL TRIGGER:
In This Level, Trigger Body(Logic) Is Executing For Each Row Wise For A Dml Operation.
Ex:
Create Or Replace Trigger Tr1
After Update On Test
For Each Row
Begin
Dbms_output.put_line(‘hello’);
End;
/
Trigger Created.
Testing:
Sql> Update Test Set Sal=10000 Where Sal=15000;
Hello
Hello
2 Rows Updated.
B. Statement Trigger:
In This Level, Trigger Body Is Executing Only One Time For A Dml Operation.
Ex:
Create Or Replace Trigger Tr1
After Update On Test
Begin
Dbms_output.put_line(‘hello’);
End;
/
Trigger Created.
Testing:
Sql> Update Test Set Sal=12000 Where Sal=10000;
Hello
2 Rows Updated.
Bind Variables:
Bind Variables Are Just Like Variables Which Are Used To Store Values While Inserting, Updating, Deleting Data From A Table.
These Are Two Types,
1. New:
This Bind Variable Will Store New Values When We Insert.
Syntax: New. <column Name>= <value>;
Ex: New.sal = 15000;
2.Old:
This Bind Variable Will Store Old Values When We Delete.
Syntax: old. <column Name>= <value>;
Ex: Old.sal = 12000;
Note: These Bind Variables Are Used In “Row Level Triggers Only”.
1. To Invoking User Defined Message / Alert At Event Fire.
Ex:
Create Or Replace Trigger Trinsert
After Insert On Test
Begin
Dbms_output.put_line (‘some One Inserted Data Into Your Table’);
End;
/
Trigger Created.
Testing:
Sql> Insert Into Test Values (105,’scott’,36000);
Some One Inserted Data Into Your Table
1 Row Created.
Ex:
Create Or Replace Trigger Trupdate
After Update On Test
Begin
Dbms_output.put_line (‘some One Updated Data Into Your Table’);
End;
/
Trigger Created.
Testing:
Update Test Set Sal=22000 Where Eid=1021;
Some One Updating Data In Your Table
1 Row Updated.
Ex:
Create Or Replace Trigger Trdelete
After Delete On Test
Begin
Dbms_output.put_line (‘some One Deleted Data From Your Table’);
End;
/
Trigger Created.
Testing:
Delete From Test Where Edi=1022;
Some One Deleting Data From Your Table.
1 Row Deleted.
Ex:
Create Or Replace Trigger Trdml
After Insert Or Update Or Delete On Test
Begin
Dbms_output.put_line (‘some One Performing Dml Operations On Your Table’);
End;
/
Trigger Created.
2. To Control / Restricted Dml Operations On A Table:
Ex:
Create Or Replace Trigger Trin
After Insert On Test
Begin
Raise_application_error (-20487,’some One Inserting Data Into Your Table’);
End;
/
Testing:
Insert Into Test Values (106,’miller’,52000)
Error At Line 1:
Ora-20487: Some One Inserted Data Into Ur Table
Ex:
Create Or Replace Trigger Trup
After Update On Test
Begin
Raise_application_error (-20481,’some One Updating Data In Your Table’);
End;
/
Testing:
Update Test Set Sal=22000 Where Eid=1021;
Error At Line 1:
Ora-20487: Some One Updating Data In Your Table
Ex:
Create Or Replace Trigger Trdel
After Delete On Test
Begin
Raise_application_error (-20481,’some One Deleting Data From Your Table’);
End;
/
Testing:
Delete From Test Where Edi=1022;
Error At Line 1:
Ora-20487: Some One Deleting Data From Your Table.
Ex:
Create Or Replace Trigger Trdel
After Insert Or Update Or Delete On Test
Begin
Raise_application_error (-20481,’some One Performing Dml Operations On Your Table’);
End;
/
Testing:
Insert Into Test Values (106,’miller’,52000);
Update Test Set Sal=22000 Where Eid=1021;
Delete From Test Where Edi=1022;
Error At Line 1:
Ora-20781: Some One Performing Dml Operations On Your Table.
3. To Implementing Business Logical Conditions:
Ex:
Create A Trigger To Restricted Dml Operations On Every Saturday?
Create Or Replace Trigger Trday
After Insert Or Update Or Delete On Test
Begin
If To_char(Sysdate,’dy’) = ‘sat’ Then
Raise_application_error (-20456,’you Cannnot Perform Dml Operations On Every Saturday’);
End If;
End;
/
Ex:
Create A Trigger To Restricted Dml Operations On Test Table In Between 9am To 5pm?
Create Or Replace Trigger Trtime
After Insert Or Update Or Delete On Test
Begin
If To_char(Sysdate,’hh24′) Between 9 And 16 Then
Raise_application_error (-20456,’you Cannnot Perform Dml Operations Between 9am To 5pm’);
End If;
End;
/
Ex:
Trigger Name: Holi_trig
Table Name: Emp
Trigger Event: Before Insert Or Update Or Delete
Sol:
Create Or Replace Trigger Holi_trig
Before Insert Or Update Or Delete
On Emp
Declare
Cnt Number;
Begin
If To_char(Sysdate,’hh24′) Not Between 10 And 16 Then
Raise_application_error (-20001,’offtimings, Trans. Are Not Allowed.’);
End If;
If To_char(Sysdate,’dy’) In (‘sat’,’sun’) Then
Raise_application_error (-20002,’weekends, Trans. Are Not Allowed.’);
End If;
Select Count(Hdate) Into Cnt From Holiday
Where To_char(Sysdate,’dd/mm/yy’) =to_char(Hdate,’dd/mm/yy’);
If Cnt>0 Then
Raise_application_error (-20003,’today Public Holiday, Trans. Are Not Allowed.’);
End If;
End;
4. To Validating Data:
Ex: Create A Trigger To Validate Insert Operation If New Salary Is Less Than 5000?
Sql> Create Or Replace Trigger Trsal1
Before Insert On Test
For Each Row
Begin
If: New.sal < 5000 Then
Raise_application_error (-20348,’new Sal Should Not Be Less Than To 5000′);
End If;
End;
/
Testing:
Insert Into Test Values (1021,’smith’,4500); —–not Allowed
Insert Into Test Values (1021,’smith’,5500); —–allowed
Ex:
Create Or Replace Trigger Dept_trig
Before Insert On Dept
For Each Row
Begin
: New.dname: =upper (: New.dname);
: New.loc: =upper (: New.loc);
End;
Testing:
Sql> Insert Into Dept Values (50,’economics’,’hyd’);
Ex:
Create A Trigger To Validate Update Operation On Test Table If New Salary Is Less Than To Old Salary?
Sql> Create Or Replace Trigger Trsal2
Before Update On Test
For Each Row
Begin
If: New.sal <: Old.sal Then
Raise_application_error (-20748,’new Sal Should Not Be Less Than To Old Salary’);
End If;
End;
/
Testing: Update Test Set Sal=6000 Where Sal=8000; —–not Allowed
Update Test Set Sal=9000 Where Sal=8000; —— Allowed
Ex:
Create A Trigger To Validate Delete Operation On Test Table If We Try To Delete The Employee “Smith” Details?
Sql> Create Or Replace Trigger Trdata
Before Delete On Test
For Each Row
Begin
If: Old.ename = ‘smith’ Then
Raise_application_error (-20648,’we Cannot Delete Smith Employee Details’);
End If;
End;
/
Testing:
Delete From Test Where Ename=’smith’; —–not Allowed
Delete From Test Where Ename=’allen’; —-allowed
5. For Auditing:
When We Manipulate Data In A Table Those Transactional Values Are Stored In Another Table Is Called As Auditing Table.
Ex:
Sql> Create Table Emp1(Eid Int, Ename Varchar2(10), Sal Number (10));
Table Created.
Sql> Create Table Auditemp1(Eid Int, Audit_infor Varchar2(100));
Table Created.
Ex:
Create Or Replace Trigger Traudit_insert
After Insert On Emp1
For Each Row
Begin
Insert Into Auditemp1 Values (: New.eid,’some One Inserted A New Row Into Emp1 Table On’||’ ‘||
To_char (Sysdate,’dd-mon-yyyy Hh:mi: Ss Am’));
End;
/
Testing:
Insert Into Emp1 Values (1021,’smith’,4500);
Ex:
Create Or Replace Trigger Traudit_update
After Update On Emp1
For Each Row
Begin
Insert Into Auditemp1 Values (: Old.eid,’some One Updated Data In Emp1 Table On’||’ ‘||
To_char (Sysdate,’dd-mon-yyyy Hh:mi: Ss Am’));
End;
/
Testing:
Update Emp1 Set Sal=6000 Where Eid=1021;
Ex:
Create Or Replace Trigger Traudit_delete
After Delete On Emp1
For Each Row
Begin
Insert Into Auditemp1 Values (: Old.eid,’some One Deleted Data From Emp1 Table On’||’ ‘||
To_char (Sysdate,’dd-mon-yyyy Hh:mi: Ss Am’));
End;
/
Testing:
Delete From Emp1 Where Ename=’smith’;
Ex:
Create Or Replace Trigger Traudit_dml
After Insert Or Update Or Delete On Emp1
For Each Row
Begin
Insert Into Auditemp1 Values (: Old.eid,’some One Performing Dml Operations On Emp1 Table On’||’ ‘||
To_char (Sysdate,’dd-mon-yyyy Hh:mi: Ss Am’));
End;
/
Testing:
Insert Into Emp1 Values (1021,’smith’,4500);
Update Emp1 Set Sal=6000 Where Eid=1021;
Delete From Emp1 Where Ename=’smith’;
DDL Triggers / DB Triggers:
These Triggers Are Executed By System Automatically When User Perform Ddl (Create / Alter / Drop / Rename) Operations On A Specific Schema / Database.
These Trigger Are Handling By Dba Only.
Syntax:
Create Or Replace Trigger <trigger_name>
Before/after Create Or Alter Or Drop Or Rename
On Username.schema
[ For Each Row]
[ Declare
<variable Declaration>;]
Begin
<exec Statements>;
End;
Ex:
Sql> Create Or Replace Trigger Trddl
After Create On Mydb9am.schema
Begin
Raise_application_error (-20456,’we Cannot Create A New Table In Mydb9am Schema’);
End;
/
Trigger Created.
Sql> Create Table
T1(Sno Int);
Error At Line 1:
Ora-20456: We Cannot Create A New Table In Mydb9am Schema
Ex:
Sql> Create Or Replace Trigger Trddl
After Alter On Mydb9am.schema
Begin
Raise_application_error (-20456,’we Cannot Alter A Table In Mydb9am Schema’);
End;
/
Sql> Alter Table Emp1 Add Eadd Varchar2(10);
Ora-20456: We Cannot Alter Table In Mydb9am Schema
Sql> Create Or Replace Trigger Trddl
After Drop On Mydb9am.schema
Begin
Raise_Application_error (-20456,’we Cannot Drop A Table From Mydb9am Schema’);
End;
/
Trigger Created.
Sql> Drop Table Emp1 Purge;
Ora-20456: We Cannot Drop A Table From Mydb9am Schema
Sql> Create Or Replace Trigger Trddl
After Rename On Mydb9am.schema
Begin
Raise_application_error (-20456,’we Cannot Rename A Table In Mydb9am Schema’);
End;
/
Trigger Created.
Sql> Rename Emp1 To Empdetails;
Ora-20456: We Cannot Rename A Table In Mydb9am Schema
Ora-06512: At Line 2
Ex:
Sql> Create Or Replace Trigger Trddl
After Create Or Alter Or Rename Or Drop On Mydb9am.schema
Begin
Raise_application_error (-20456,’we Cannot Perform Ddl Operations On Mydb9am Schema’);
End;
/
Trigger Created.
Ex:
Create A Trigger To Restricted Ddl Operations On Mydb9am Schema In Between 9am To 5pm?
Create Or Replace Trigger Trddltime
After Create Or Alter Or Rename Or Drop On Mydb9am.schema
Begin
If To_char(Sysdate,’hh24′) Between 9 And 16 Then
Raise_application_error (-20456,’you Cannnot Perform Ddl Operations On Mydb9am Between 9am To 5pm’);
End If;
End;
/
Dropping Triggers:
