Triggers in SQL

What is Trigger in SQL?

A SQL trigger is a database object that fires when a database event happens. When a change is made to a database table, such as when a record is inserted, modified, or deleted, we can run a SQL query that will "do something" in the database. A trigger can be set on a record insert in a database table, for example. For instance, if you want to increase the number of blogs in the Reports table when a new record is put in the Blogs table, you can construct an INSERT trigger on the Blogs table and update the Reports table by increasing the number of blogs to one.

Syntax

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body]

BEFORE and AFTER of Trigger

🚀 BEFORE triggers execute the trigger action before to executing the triggering sentence.
🚀 AFTER triggers execute the trigger action following the execution of the triggering statement.

Types of Triggers

There are two types of triggers :

🚀 DDL Trigger
🚀 DML Trigger

Let's discuss them in brief,

DDL Trigger

DDL (Data Definition Language) command events that begin with Create, Alter, or Drop, such as Create table, Create view, drop table, Drop view, and Alter table, fire the DDL triggers.

create trigger alert
on Student_record
for
create_table,alter_table,drop_table
as
print'you can not create ,drop and alter this table'
rollback;

When we create, alter or drop any table in a database then the following message appears: you can not create ,drop and alter this table.

DML Trigger

The DML triggers are activated when DML (Data Manipulation Language) command events begin with Insert, Update, or Delete. Insert table, Update view, and Delete table are some examples.

create trigger alert
on Employee_data
for
insert, update, delete
as
print'you can not insert, upadte and delete the data in this table'
rollback;

When we create, alter or drop any table in a database then the following message appears: you can not insert, upadte and delete the data in this table.