Home > DeveloperSection > Blogs > Trigger In SQL

Trigger In SQL


MYSQL Database  SQL Server  SQL 
Ratings:
0 Comment(s)
 159  View(s)
Rate this:

Trigger In SQL    


Triggers are store procedure, which are automatically executed or fired when some events occur like deletion, insertion of data. Triggers are database object which is attached to a table and is executed automatically. Triggers can’t be explicitly invoked. Triggers could be defined on the schema, table, view or database with which an event is associated.

Benefits of Triggers

Triggers can be benefited for the following purposes:

·        Enforcing referential integrity

·        Imposing security authorizations

·        Auditing

·        Preventing invalid transactions

·        Synchronous replication of tables

·        Generating derived field values automatically

·        Event storing and logging information on table access

CREATING TRIGGER

To add triggers in database schema CREATE TRIGGER statement is used. Triggers are database operations that are automatically executed when some events occur like deletion, insertion of data.

A trigger is fired whenever a INSERT, UPDATE or DELETE, of a particular database table occurs, or whenever an UPDATE occurs on one or more specified field of a table. Following is the syntax for creating a trigger:

Syntax:

CREATE [OR REPLACE ] TRIGGER name_of_trigger

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF name_of_column]

ON name_of_table

[REFERENCING OLD AS old NEW AS new]

[FOR EACH ROW]

WHEN (condition1) 

DECLARE

   Declaration of a code block

BEGIN

   Executable-code block

EXCEPTION

   Exception-handling-code block

END;

 

In the above syntax,

·         CREATE [OR REPLACE ] TRIGGER name_of_trigger means : replaces or creates an existing trigger with the  name_of_trigger.

·         {BEFORE | AFTER | INSTEAD OF } : This specifies when the trigger is executed then INSTEAD OF is used for creating trigger.

·         {INSERT [OR] | UPDATE [OR] | DELETE} : This specifies the Data manipulation Language operation.

·         [OF name_of_column] : This specifies the name of column that would be updated.

·         ON name_of_table : T his specifies the table name which is associated with the trigger.

·         [REFERENCING OLD AS old NEW AS new] : This allows you to refer old and new values for various Data Manipulation Language statements, like UPDATE, INSERT and DELETE.

·         WHEN (condition1): This is a condition for rows which the trigger would fire. This condition is true only for row level triggers.

·         [FOR EACH ROW]: This is a row trigger, i.e., the trigger is executed when each row being affected.

 

Following is an example of trigger:

Code implementation:

 

Select * from student

 

 

ID

NAME

AGE

ADDRESS

MARKS

1

2

3

4

5

6

Neha

Ragini

Shreya

Kopal

Rohan

Avi

18

19

17

18

18

19

Mumbai

Ahmedabad

Pune

Allahabad

Banaras

Kanpur

35

45

23

24

43

36

 

 

The below program creates a row level trigger for the student table that would fire for DELETE or INSERT or UPDATE operations performed on the STUDENT table. This trigger will display the marks difference between the new values and old values:

CREATE OR REPLACE TRIGGER display_marks_changes

BEFORE UPDATE OR DELETE OR INSERT ON student

FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

   marks number;

BEGIN

   marks:= :NEW.marks  - :OLD.marks;

   dbms_output.put_line('Old marks: ' || :OLD. marks);

   dbms_output.put_line('New marks: ' || :NEW. marks);

   dbms_output.put_line(' marks difference: ' || marks);

END;

 

As from the above example we can see when the code is executed it produces the following result:

Trigger created

 

Triggering a Trigger

Now let’s perform some Data Manipulation language (DML) operations on the STUDENT table. In the below example one INSERT statement is given, which creates a new record in the table:

INSERT INTO STUDENT (ID, NAME, AGE, ADDRESS, marks)

VALUES (7, 'Ravi', 22, 'Chennai', 45 );

As from the above example, record has been created in STUDENT table, now display_marks_changes will be executed and it will display the following result:

 

Old marks:

New marks: 7500

marks difference:


Don't want to miss updates? Please click the below button!

Follow MindStick