---
title: "Trigger In SQL"  
description: "Triggers are store procedure, which are automatically executed or fired when some events occur like deletion, insertion of data. Triggers are database"  
author: "Jayden Bell"  
published: 2016-07-05  
updated: 2018-03-16  
canonical: https://www.mindstick.com/blog/11174/trigger-in-sql  
category: "mysql"  
tags: ["database", "sql server", "sql"]  
reading_time: 4 minutes  

---

# Trigger In SQL

Triggers are [store procedure](https://www.mindstick.com/forum/193/auto-increment-by-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](https://www.mindstick.com/interview/864/explain-acid-rule-of-thumb-for-transactions)

· [Synchronous](https://www.mindstick.com/blog/178/synchronous-and-asynchronous-command-execution-in-c-sharp-dot-net) replication of tables

· Generating derived field values automatically

· Event storing and logging information on table access

**CREATING TRIGGER**

To add triggers in [database schema](https://www.mindstick.com/forum/160254/how-to-apply-a-migration-to-update-the-database-schema-in-entity-framework-core) CREATE TRIGGER statement is used. Triggers are [database operations](https://www.mindstick.com/forum/160213/explain-the-use-of-asynchronous-database-operations-in-dot-net-core-apis) 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](https://www.mindstick.com/forum/159366/how-to-test-regular-expressions-in-sql-without-using-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 blockBEGIN   Executable-code blockEXCEPTION   Exception-handling-code blockEND; 
```

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](https://www.mindstick.com/blog/63649/77-727-microsoft-excel-2016-core-data-analysis-manipulation-and-presentation-exam) 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](https://www.mindstick.com/forum/33764/how-to-implementation-of-class-in-c-sharp):

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 \| |  |  |  |  |
| --- | --- | --- | --- | --- |
| 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](https://www.mindstick.com/forum/157561/what-is-the-stored-procedure-create-a-procedure-to-find-the-record-by-stu_id-from-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 ROWWHEN (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](https://www.mindstick.com/forum/157532/why-is-use-the-identity-column-in-the-insert-statement-in-sql) 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:

\

---

Original Source: https://www.mindstick.com/blog/11174/trigger-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.