What is a trigger in SQL Server? Why use triggers?

What is a trigger in SQL? Why use triggers?

A trigger is a special kind of operations that execute automatically when an event occurs in the database (tables and views) when you trying to INSERT, UPDATE OR DELETE operations in the tables and views.

All the triggers are directly attached with the tables and views. Each of the tables has their own trigger.

Noted Points:-

1. We can’t create triggers against the system tables and views.

2. The AFTER triggers can’t be defined on the views.

3. You try to avoid using nested triggers.

4. You try to avoid using recursive triggers.

What types of trigger in SQL Server?

There are two types of Triggers available in the SQL that is

DML Triggers: - i) After Trigger ii) Instead of Trigger

DDL Triggers

There are three query actions that are used in SQL INSERT, UPDATE OR DELETE.

Syntax:-

--CREATE DATABASE TRIGGER TEMPLATE 
CREATE TRIGGER <trigger_name, sysname, table_alter_drop_safety> ON DATABASE 
 FOR <data_definition_statements, , DROP_TABLE, ALTER_TABLE> 
AS 
IF IS_MEMBER ('db_owner') = 0
BEGIN
   PRINT 'You must ask your DBA to drop or alter tables!'
   ROLLBACK TRANSACTION
END
GO

How to create triggers?

--How to Create trigger?
--CREATE TRIGGER
CREATE TRIGGER myTrigger3  ON employee
 FOR UPDATE, INSERT, DELETE
  AS
   -- BEFORE INSERT
  SELECT 'Before INSERT'
  INSERT Employee (ID, Name) VALUES (31, 'Rick')
  GO
   --BEFORE UPDATE
   SELECT 'Before UPDATE'
     UPDATE Employee
      SET Name = 'Test'
     WHERE ID = 1
  GO

  -- BEFORE DELETE
  SELECT 'Before DELETE'
   DELETE FROM Employee 
  WHERE ID = 1

I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

Pin it

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2