Triggers in SQL

Introduction

Triggers are a special kind of Stored Procedure that executes automatically when a user tries to modify a database. Triggers are built for SELECT, UPDATE, INSERT and DELETE statements; whenever a user tries to executes these queries to perform a transaction, triggers stop him and keep our database secure.

Sample Code for Triggers

use [Database_Name]
GO
Create Trigger Trigger_Name
on dbo.table_name
AFTER INSERT
AS
BEGIN
PRINT 'INSERTION IS NOT ALLOWED !! '
ROLLBACK TRANSACTION
end
Go

This code creates a trigger on a particular table; if a user tries to insert a value into this table, a trigger would be generated automatically which would prevent the user from any insertion.

A little bit of explanation about this code:

use [database] --the database which is in use
Create Trigger Trigger_Name --Creates a Trigger, specifying its name
on dbo.table_name --specifies the table where we want to set the Trigger
AFTER INSERT --Specify a keyword here "INSERT", "UPDATE", "DELETE" or "SELECT", after the keyword AFTER
ROLLBACK TRANSACTION -- Rolls a transaction back to a savepoint or beginning of transaction, in this case it will Rollback us to the original state of the table (no change will occur)

You can use the same code for other statements too.

For example if we don't want our user to update data in the database we would follow up with this trigger:

use [DatabaseName]
GO
create Trigger updateTrigger
on dbo.tableName
AFTER UPDATE
AS
BEGIN
PRINT 'UPDATION IS NOT ALLOWED'
ROLLBACK TRANSACTION
END
GO

To prevent deletion:

use [DatabaseName]
GO
create Trigger Delete_Trigger
on dbo.tableName
AFTER DELETE
AS
BEGIN
PRINT 'DELETION IS NOT ALLOWED'
ROLLBACK TRANSACTION
END
GO

To prevent selection:

use [DatabaseName]
GO
create Trigger Select_Trigger
on dbo.tableName
AFTER SELECT
AS
BEGIN
PRINT 'SELECTION IS NOT ALLOWED'
ROLLBACK TRANSACTION
END
GO

Conclusion

We can do many modifications with the same triggers. Use these and make your programming more efficient.