Trigger in DBMS

Database Management System

1. Triggers

 Triggers allow applications to monitor database events (activities) and take actions when specified
events take place. They also provide a way of adding rules and actions without modifying existing
application code.

 A trigger is a statement that is automatically executed by the system as a side effect of a modification
to the database. To design a trigger mechanism, two requirements must be met:

1. Specify when a trigger is to be executed:

a) an event: that caused the trigger to be checked
b) a condition: that must be satisfied for trigger execution to proceed

2. Specify the actions to be taken when the trigger executes.
Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers
are, in fact, written to be executed in response to any of the following events −
 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
 A database definition (DDL) statement (CREATE, ALTER, or DROP).
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTU P, or SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is associated.
How Triggers Are Used/ Benefits of Triggers
In addition, triggers are commonly used to
 automatically generate derived column values
 prevent invalid transactions
 enforce complex security authorizations
 enforce referential integrity across nodes in a distributed database
 enforce complex business rules
 provide transparent event logging
 provide sophisticated auditing
 maintain synchronous table replicates
 gather statistics on table access

2. Creating Triggers
The syntax for creating a trigger is −
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n ]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

Where,
 CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with
the trigger_name.
 {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The
INSTEAD OF clause is used for creating trigger on a view.
 {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
 [OF col_name] − This specifies the column name that will be updated.
 [ON table_name] − This specifies the name of the table associated with the trigger.
 [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various
DML statements, such as INSERT, UPDATE, and DELETE.
 [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each
row being affected. Otherwise the trigger will execute just once when the SQL statement is
executed, which is called a table level trigger.
 WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause
is valid only for row-level triggers.
Limitations of Triggers
1. Trigger should be written with great care, since a trigger error detected at runtime causes the failure
of the insert/delete/update statement that set off the trigger.
2. The action of one trigger can set off another trigger
3. In the worst case, this could even lead to infinite chain of triggering.

3. Authorization in SQL

Authorization is the process where the database manager gets information about the authenticated user.
Part of that information is determining which database operations the user can perform and which data
objects a user can access.

A user can be assigned several forms of authorization on parts of the database:

Authorization on data: (instance)

1. Read authorization: allows reading, but not modification of data

2. Insert authorization: insertion of new data, but not modification of existing data

3. Update authorization: allows modification, but not deletion of data

4. Delete authorization: allows deletion of data

We may assign all, none or a combination of authorization.

Authorization on database schema:

1. Index authorization: allows the creation and deletion of indices

2. Resource authorization: allows the creation of new relations

3. Alteration authorization: allows the addition or deletion of attributes in a relation

4. Drop authorization: allows the deletion of relations

Notes:

1. Drop and delete authorization is not same.

The drop and delete authorization differ in that delete authorization allows deletion of tuples only. If a user
deletes all tuples of a relation, the relation still exists, but it is empty. If a relation is dropped, it no longer
exists

2. A user with resource authorization who creates a new relation is given all privileges on that relation
automatically.

3. The ultimate owner of all authorization is database administrator (DBA). He/she is a super-user and may
authorize new users, restructure the database etc.


Granting of Privileges

 A user who has been granted some form of authorization may be allowed to pass on this authorization
to other users. This is called granting of privileges. But this must be ensured that such authorization
can be revoked at some future time.

 Authorization Graph: Assume that the database administrator grants update authorization on loan
relation to user U1, U2 and U3 who may in tern pass on this authorization to other users. The passing
of authorization from one user to another can be represented by a graph called authorization graph.

4. Node: The nodes of this graph are the users.
Edge: The graph includes an edge Ui → Uj is user Ui grants update authorization on loan to Uj. It is
required that all edges in an authorization graph be part of some path originating with the DBA.
Root: The root of the graph is database administrator.
In the sample graph in

Figure 6.6, observe that user U5 is granted authorization by both U1 and U2; U4 is granted authorization
by only U1.


Figure 6.6: Authorization-grant graph

 A user has an authorization if and only if there is a path from root of the authorization graph down to
the node representing the user.

 Revocation: Suppose DBA decides to revoke the authorization of user U1. Since U4 has authorization
from U1, that authorization should be revoked as well. However, U5 was granted authorization by both
U1 and U2. Since the DBA didn’t revoke update authorization on loan from U2, U5 retains update
authorization on loan. If U2 eventually revokes authorization from U5, the U5 loses the authorization.