Trigger in SQL

 A Trigger in Structured Query Language is a set of procedural statements which are

executed automatically when there is any response to certain events on the particular table in
the database.
 Triggers are used to protect the data integrity in the database.
 In Structured Query Language, triggers are called only either before or after the below
events:
1. INSERT Event: This event is called when the new row is entered in the table.
2. UPDATE Event: This event is called when the existing record is changed or modified in
the table.
3. DELETE Event: This event is called when the existing record is removed from the table.
Types of Triggers in SQL
1. After / Before Insert trigger : - This trigger is invoked after/before the insertion of data
in the table.
2. After / Before Delete Trigger :- This trigger is invoked in SQL after / before the
modification of the data in the table.
3. After / Before Update Trigger : - This trigger is invoked after / before updating the
record in the table.
Syntax of Trigger in SQL
Create trigger trigger_name
[ before | after ] [ insert | update | delete]
on [table_name]
[ for each row | for each column ]
as
set of sql statement
 In the trigger syntax, firstly, we have to define the name of the trigger after the CREATE
TRIGGER keyword. After that, we have to define the BEFORE or AFTER keyword with
anyone event.

1
 Then, we define the name of that table on which trigger is to occur.
 After the table name, we have to define the row-level or statement-level trigger.
 And, at last, we have to write the SQL statements which perform actions on the occurring of
event.
Example of Trigger in SQL
 To understand the concept of trigger in SQL, first, we have to create the table on which
trigger is to be executed.
 The following query creates the Student_Trigger table in the SQL database:
Create table Student_Trigger
(
Student_RollNo INT NOT NULL PRIMARY KEY,
Student_FirstName Varchar (100),
Student_EnglishMarks INT,
Student_PhysicsMarks INT,
Student_ChemistryMarks INT,
Student_MathsMarks INT,
Student_TotalMarks INT,
Student_Percentage );
 The following query fires a trigger before the insertion of the student record in the table:
CREATE TRIGGER Student_Table_Marks
BEFORE INSERT
ON
Student_Trigger
FOR EACH ROW
SET new.Student_TotalMarks = new.Student_EnglishMarks + new.Student_PhysicsMarks +
new.Student_ChemistryMarks + new.Student_MathsMarks,
new.Student_Percentage = ( new.Student_TotalMarks / 400) * 100;
 The following query inserts the record into Student_Trigger table:

2
 INSERT INTO Student_Trigger
(Student_RollNo,
Student_FirstName,
Student_EnglishMarks,
Student_PhysicsMarks,
Student_ChemistryMarks,
Student_MathsMarks,
Student_TotalMarks,
Student_Percentage) VALUES ( 201, Sorya, 88, 75, 69, 92, 0, 0);

 SELECT * FROM Student_Trigger;

Student_ Student_ Student_ Student_ Student_ Student_ Student_ Student_

RollNo First English Physics chemistry Maths Total Percen
Name Marks Marks Marks Marks Marks tage
201 Sorya 88 75 69 92 324 81

Advantages of Triggers in SQL

1. SQL provides an alternate way for maintaining the data and referential integrity in the tables.
2. Triggers helps in executing the scheduled tasks because they are called automatically.
3. They catch the errors in the database layer of various businesses.
4. They allow the database users to validate values before inserting and updating.
Disadvantages of Triggers in SQL
1. They are not compiled.
2. It is not possible to find and debug the errors in triggers.
3. If we use the complex code in the trigger, it makes the application run slower.
4. Trigger increases the high load on the database system.

3
Active Database
 It is a database consisting of set of triggers.
 These databases are very difficult to be maintained because of the complexity that arises in
understanding the effect of these triggers.
 In such database, DBMS initially verifies whether the particular trigger specified in the
statement that modifies the database is activated or not, prior to executing the statement.
 If the trigger is active then DBMS executes the condition part and then executes the action
part only if the specified condition is evaluated to true.
 It is possible to activate more than one trigger within a single statement.
 In such situation, DBMS processes each of the trigger randomly.
 The execution of an action part of a trigger may either activate other triggers or the same
trigger that Initialized this action.
 Trigger that activates itself is called as ‘recursive trigger’.
 The DBMS executes such chains of trigger in some pre-defined manner but it effects the
concept of understanding.

4
Features of Active Database:
 It possess all the concepts of a conventional database i.e. data modeling facilities, query
language etc.
 It supports all the functions of a traditional database like data definition, data manipulation,
storage management etc.
 It supports definition and management of ECA rules.
 It detects event occurrence.
 It must be able to evaluate conditions and to execute actions.
 It means that it has to implement rule execution.
Advantages :
 Enhances traditional database functionalities with powerful rule processing capabilities.
 Enable a uniform and centralized description of the business rules relevant to the
information system.
 Avoids redundancy of checking and repair operations.
 Suitable platform for building large and efficient knowledge base and expert systems.
5
NULL Values:
 In SQL there may be some records in a table that do not have values or data for every field
and those fields are termed as a NULL value.
 NULL values could be possible because at the time of data entry information is not
available.
 So SQL supports a special value known as NULL which is used to represent the values of
attributes that may be unknown or not apply to a tuple.
 SQL places a NULL value in the field in the absence of a user-defined value.
 For example, the Apartment_number attribute of an address applies only to addresses that
are in apartment buildings and not to other types of residences.
Importance of NULL Value
 It is important to understand that a NULL value differs from a zero value.
 A NULL value is used to represent a missing value, but it usually has one of three different
interpretations:
i) The value unknown (value exists but is not known)
ii) Value not available (exists but is purposely withheld)
iii) Attribute not applicable (undefined for this tuple)
Principles of NULL values
 Setting a NULL value is appropriate when the actual value is unknown, or when a value is
not meaningful.
 A NULL value can be inserted into columns of any data type.
 A NULL value will evaluate NULL in any expression.
 Suppose if any column has a NULL value, then UNIQUE, FOREIGN key, and CHECK
constraints will ignore by SQL.
 When a NULL is involved in a comparison operation, the result is considered to be
UNKNOWN.

6
 Hence, SQL uses a three-valued logic with values True, False, and Unknown. It is,
therefore, necessary to define the results of three-valued logical expressions when the
logical connectives AND, OR, and NOT are used.
How To Test for NULL Values?
 SQL allows queries that check whether an attribute value is NULL.
 Rather than using = to compare an attribute value to NULL, SQL uses IS and IS NOT.
 This is because SQL considers each NULL value as being distinct from every other NULL
value, so equality comparison is not appropriate.
 Now, consider the following Employee Table.
Query:
CREATE TABLE Employee (
Fname VARCHAR(50),
Lname VARCHAR(50),
SSN VARCHAR(11),
Phoneno VARCHAR(15),
Salary FLOAT
);
INSERT INTO Employee (Fname, Lname, SSN, Phoneno, Salary)
VALUES
('Shubham', 'Thakur', '123-45-6789', '9876543210', 50000.00),
('Aman', 'Chopra', '234-56-7890', NULL, 45000.00),
('Aditya', 'Arpan', NULL, '8765432109', 55000.00),
('Naveen', 'Patnaik', '345-67-8901', NULL, NULL),
('Nishant', 'Jain', '456-78-9012', '7654321098', 60000.00);
The Output is:

Fname Lname SSN Phoneno Salary

Shubham Thakur 123-45-6789 9876543210 50000
Aman Chopra 234-56-7890 NULL 45000
Adithya Arpan NULL 8765432109 55000
Naveen Patnaik 345-67-8901 7NULL NULL
Nishant Jain 456-78-9012 7654321098 60000
The IS NULL Operator:-
 Suppose we find the Fname and Lname of the Employee having no Super_ssn then the
query will be:
Query:
Select Fname, Lname from employee where SSN IS NULL;
Output:
Fname Lname
Adithya Arpan

The IS NOT NULL Operator:-

 Now if we find the Count of number of Employees having SSNs.
Query:
Select count(*) AS Count from employee where SSN IS NOT NULL;
Count
4

Updating NULL Values in a Table

 We can update the NULL values present in a table using the UPDATE statement in SQL.
 To do so, we can use the IS NULL operator in the WHERE clause to select the rows with
NULL values and then we can set the new value using the SET keyword.
 Let’s suppose that we want to update SSN in the row where it is NULL.
Query:
Update employee
Set SSN = '789-01-2345'
Where Fname = 'Aditya' AND Lname = 'Arpan';
 Select* from Employee;
The Output is:
8
 Fname Lname SSN Phoneno Salary
Shubham Thakur 123-45-6789 9876543210 50000
Aman Chopra 234-56-7890 NULL 45000
Adithya Arpan 789-01-2345 8765432109 55000
Naveen Patnaik 345-67-8901 NULL NULL
Nishant Jain 456-78-9012 7654321098 60000

COMPLEX INTEGRITY CONSTRAINTS IN SQL

 Complex integrity constraints are more advanced rules.
 These are used to enforce business rules.
 Example: Such as limiting the range of values that can be entered into a column or
ensuring that certain combinations of values are present in a table.
i) SQL CHECK Constraint
 The CHECK constraint is used to limit the value range that can be placed in a column.
 If you define a CHECK constraint on a column it will allow only certain values for this
column.
 If you define a CHECK constraint on a table it can limit the values in certain columns based
on values in other columns in the row.

SQL CHECK on CREATE TABLE

 The following SQL creates a CHECK constraint on the "Age" column when the "Persons"
table is created.
 The CHECK constraint ensures that the age of a person must be 18, or older:
CREATE TABLE Persons (
ID int NOT NULL,
LName varchar(255) NOT NULL,
FName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Hyderabad')
9
);
SQL CHECK on ALTER TABLE
 To create a CHECK constraint on the "Age" column when the table is already created, use
the following SQL:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Hyderabad'); (On
multiple columns also we can apply)
DROP a CHECK Constraint
 To drop a CHECK constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
ii) SQL DEFAULT Constraint
 The DEFAULT constraint is used to set a default value for a column.
 The default value will be added to all new records, if no other value is specified.
SQL DEFAULT on CREATE TABLE
 The following SQL sets a DEFAULT value for the "City" column when the "Persons" table
is created:

CREATE TABLE Persons (

ID int NOT NULL,
LName varchar(255) NOT NULL,
FName varchar(255),
Age int,
City varchar(255) DEFAULT 'Hyderabad'
);
 The DEFAULT constraint can also be used to insert system values, by using functions like
GETDATE():
CREATE TABLE Orders (

10
 ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT on ALTER TABLE
 To create a DEFAULT constraint on the "City" column when the table is already created,
use the following SQL:
ALTER TABLE Persons
MODIFY City DEFAULT 'Bombay';
DROP a DEFAULT Constraint
 To drop a DEFAULT constraint, use the following SQL:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
ii) Domain Constraints
 Domain Constraints are user-defined columns that help the user to enter the value
according to the data type.
 Domain Constraints are user-defined columns that help the user to enter the value
according to the data type.
 We can create our own domain and assign into the columns names.
 If it encounters a wrong input it gives the message to the user that the column is not fulfilled
properly.
 There are two types of constraints that come under domain constraint and they are: Domain
Constraints – Not Null and Domain Constraints – Check
1. Domain Constraints – Not Null:
 Null values are the values that are unassigned or unknown or the missing attribute values.
 By default, a column can hold the null values.
 The Not Null constraint restricts a column to not accept the null values.

11
 Means you cannot insert a new record or update a record without adding a value into the
field.
 Example: In the ’employee’ database, every employee must have a name associated with
them.
Create table employee
(employee_id varchar(30),
employee_name varchar(30) not null,
salary NUMBER);
2. Domain Constraints – Check:
 It defines a condition that each row must satisfy.
 It restricts the value of a column between ranges.
 It is just like a condition or filter checking before saving data into a column.
 It ensures that when a tuple is inserted inside the relation must satisfy the predicate given in
the check clause.
 Example: We need to check whether the entered id number is greater than 0 or not for the
employee table.
Create table employee
(employee_id varchar(30) not null check(employee_id > 0),
employee_name varchar(30),
salary NUMBER);
 Suppose we have a table student which consists of 3 attributes as NAME, ROLL NO, and
MARKS.
 Now ROLL NO attributes can have only numbers associated with them and they won’t
contain any alphabet.
 So we can say that it contains the domain of integer only and it can be only a positive
number greater than 0.
Example 1:
 Creating a table “student” with the “ROLL” field having a value greater than 0.

12
Domain creaton:-:
create domain roll_no int
check(value > 0);
Table creation:-
Create table student (
Roll roll-no PRIMARY KEY,
S_name varchar(30),
Marks number);
The above example will only accept the roll no. which is greater than 0.
Example 2:
 Creating a table “Employee” with the “AGE” field having a value greater than 18.
Domain creaton:-:
create domain e_age int
check(value > 18);
Table creation:-
Create table employee (
Age e_age,
E-Name varchar(30);
E-id number PRIMARY KEY):
The above example will only accept the Employee with an age greater than 18.

iii. Assertions :-
 When a constraint involves two or more tables, the table constraint mechanism is
sometimes hard or difficult to execute and get desired results.
 To cover such situations, SQL supports the creation of assertions, which are constraints
not associated with anyone table.

13
 An assertion is a piece of SQL which makes sure a condition is satisfied or it stops action
being taken on a database object. It could mean locking out the whole table or even the
whole database.
 Assert operators are typically found in execution plans for INSERT , UPDATE , DELETE
statements to verify that no CHECK and FOREIGN KEY constraints were violated.
 Assertions are useful for enforcing data integrity and ensuring that the data in the database
meets certain conditions.
 They can be used to enforce business rules or to ensure the consistency of the data.
Example: - The sum of all loan amounts for each branch must be less than the sum of all
account balances at the branch.
This can be satisfied by creating assertion as below:
Create assertion sum-constraint check
(not exists (select * from branch
where (select sum(amount) from loan
where loan.branch-name = branch.branch-name)
>= (select sum(amount) from account
where loan.branch-name = branch.branch-name)));
Note: 1. The EXISTS operator is used to test for the existence of any record in a subquery.
2. The EXISTS operator returns TRUE if the subquery returns one or more records.
 To drop an assertion, you can use the following statement:
DROP ASSERTION assertion_name;
Note: Assertions are time-consuming to create and maintain, so they are not always used in
practice.

14