Mumbai University > Information Technology > Sem 3 > Database Management System

Marks: 10 M

Year: Dec 2014

I. Introduction:

1. A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updating of data.
2. In many cases it is convenient to specify the type of action to be taken when certain events occur and when certain conditions are satisfied.
3. Consider the following condition where the sales manager needs to receive a mail whenever they receive an order of high propriety. In this case, a trigger can be written to determine priority of the order received and send mail to sales head.

II. ECA Model:

Trigger is based on the model named ECA i.e. Event-Condition-Action.

1. Event:

a) The rules are triggered by Event.

b) These events can be database insert, update or delete statements.

c) Changing an employee’s manager can be an event on which trigger can be fired.

2. Condition:

a) The condition that determines whether the rule action should be executed.

b) Once the triggering event has occurred, an optional condition may be evaluated.

c) If no condition is specified, the action will be executed once the event occurs.

d) If a condition is specified, it is first evaluated, and only if it evaluates to true will the rule action be executed.

3. Action: a) The action is usually a sequence of SQL statements.

b) It could also be a database transaction or an external program that will be automatically executed.

c) For example, executing a stored procedure when a particular event occurs.

III. Example:

Here we are using the syntax of SQL Server database system.

Consider the following table Tbl_Employee with columns Emp_ID, Emp_Name and Emp_Sal.

Consider the following requirement in which we need to capture any changes made to Tbl_Employee shown above into another Audit Table named Tbl_EmpAudit as shown below.

| Emp_ID | Emp_Name | Emp_Sal | Audit_Action | Audit_Timestamp | |--------|----------|---------|--------------|-----------------|

A trigger needs to be fired when a new row is inserted into Tbl_Employee. The trigger makes an entry into the Tbl_EmpAudit. Following is the trigger which gets trigger which will be fired when a new row is inserted into

Tbl_Employee.

CREATE TRIGGER Trg_AfterInsert ON [Tbl_Employee]

FOR INSERT

AS DECLARE @empid INT; DECLARE @empname VARCHAR(100); DECLARE @empsal DECIMAL(10,2); DECLARE @audit_action VARCHAR(100);

SELECT @empid=i.Emp_ID FROM inserted i;

    SELECT @empname=i.Emp_Name FROM inserted i;     
    SELECT @empsal=i.Emp_Sal FROM inserted i;
SET @audit_action='Inserted Record -- After Insert Trigger.';

INSERT INTO Tbl_EmpAudit
 (
 Emp_ID,
 Emp_Name,
 Emp_Sal,
 Audit_Action,
 Audit_Timestamp
 ) 
VALUES
(
@empid,
@empname,
@empsal,
@audit_action,
getdate()
);

PRINT 'AFTER INSERT trigger fired.'

GO

To invoke the trigger, row needs to be inserted to Tbl_Employee. Consider the following insert statement.

INSERT INTO Tbl_Employee VALUES('PQR',1500);

The trigger is fired and an entry is made in the table Tbl_EmpAudit.

The entry in table Tbl_EmpAudit is as follows: