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 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
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.
