Database Trigger

A trigger is a special kind of stored procedure that executes when an INSERT, UPDATE, or DELETE statement modifies the data in a specified table or when a DDL (CREATE, ALTER, or DROP) occurs or when a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN) occurs. In simple terms, a trigger is an action that is performed behind-the scenes when an event occurs on a table, view, schema or a database .
Creating a Trigger
Creates a DML, DDL, or logon trigger.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.  A DDL trigger gives you the opportunity to do some administrative work in response to the event. For example, ◦you will get a notification, or notify someone else using an automatically generated email, that an object (and what object) has been created, or you can use a DDL trigger to discard the operation.

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'
   ROLLBACK ;

Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. All messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.  Logon triggers do not fire if authentication fails. To audit and control server sessions. Logon triggers are basically used for restricting logins to Sql Server, restricting the number of sessions for a specific login, restricting user from logging-in outside permitted hours.
Example

Create a login guest by executing the following script:

-- Create a login CREATE LOGIN guest WITH PASSWORD = 'GuestPass1' --Create Trigger CREATE TRIGGER connection_limit_trigger ON ALL SERVER FOR LOGON AS BEGIN  IF ORIGINAL_LOGIN() = 'guest' AND   (Select count(*) from sys.dm_exec_sessions       WHERE is_user_process=1 and orginal_login_name = 'guest') > 3  BEGIN   PRINT 'You are not authorized to login'   ROLLBACK  END END

DML Trigger
AFTER/FOR INSERT Triggers
An insert trigger is a DML trigger that acts when a new record is added to its intended table. such trigger uses INSERT keyword.

-- SQL Server Syntax 
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) 

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name  
ON { table | view }  
[ WITH [ ,...n ] ] 
{ FOR | AFTER | INSTEAD OF }  
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }  
[ WITH APPEND ] 
[ NOT FOR REPLICATION ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME }

Eg:

CREATE TRIGGER Sales.ForEmployees

ON Sales.Employees

AFTER INSERT

AS

BEGIN

INSERT INTO Sales.DatabaseOperations

VALUES(default,N'Customers', SUSER_SNAME(),N'Processed salary', GETDATE())

END

GO

Trigger name: Sales.ForEmployees
Table which trigger is created: Sales.Employees

If some value gets inserted into Sales.Employees table, trigger will occur and insert all the details( name of table, who performed, what action, time the action is performed) about insertion in another table called Sales.DatabaseOperations.

AFTER/FOR UPDATE Triggers

whenever a record has been updated on a table, DML trigger will be evoked.

CREATE TRIGGER Sales.Update
ON Sales.Employee
AFTER UPDATE
AS
BEGIN
    INSERT INTO Sales.DatabaseOperations
    VALUES(default, N'Employees', SUSER_SNAME(),
    N'updated employee record.',GETDATE());
END
GO

AFTER/FOR DELETE Triggers

whenever a record has been deleted from a table, DML trigger will be evoked.

CREATE TRIGGER Sales.Delete
ON Sales.Employee
AFTER DELETE
AS
BEGIN
    INSERT INTO Sales.DatabaseOperations
    VALUES(default, N'Employees', SUSER_SNAME(),
    N'deleted employee record.',GETDATE());
END
GO

INSTEAD OF DML Triggers

Trigger will get evoked before the action takes place. basically INSTEAD OF trigger prevent a user from adding, modifying or deleting a record. 

Example:
CREATE TRIGGER Sales.Event

ON Sales.Employee
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Sales.DatabaseOperations
    VALUES(default, N'Employees', SUSER_SNAME(),
    N'User tried to insert employee record.',GETDATE());
END
GO

There are INSTEAD OF INSERT, INSTEAD OF UPDATE and INSTEAD OF DELETE Triggers.

Using 'With Execute As' in a trigger: - Allows an user to execute a trigger on behalf of someone

Example:

CREATE TRIGGER Sales.WithEmployee

ON Sales.Employee

WITH EXECUTE AS N'JIS'

AFTER INSERT

AS

BEGIN

INSERT INTO Sales.DatabaseOperations

VALUES(default, N'Employees', SUSER_NAME(), N'inserts an employee record', GETDATE());

END

GO

***** An AFTER trigger can be applied to a table only.  An INSTEAD OF trigger can be associated with either a table or a view. A table cannot have more than one INSTEAD OF INSERT trigger,
DDL Trigger
Trigger that acts when a certain type of DDL event fires. (creation, modification and removal of an object, not its records)

Example:

CREATE TRIGGER Logcreationoftable

ON DATABASE

FOR CREATE_TABLE

AS

BEGIN

INSERT INTO Sales.DatabaseOperations

VALUES(SUSER_NAME(), N'inserts an employee record', GETDATE());

END

GO

when new table is created, trigger runs, updates the name of user who created the table, date and time of table creation and message.