SQL Server Triggers

Level : Advanced

Mentor: Shailendra Chauhan

Duration : 00:02:00

Creating After DML Triggers

After DML (Data Manipulation Language) triggers in SQL Server are used to execute actions automatically after INSERT, UPDATE, or DELETE operations on a table. These triggers are often used for auditing or enforcing business rules.

Example

CREATE TRIGGER AfterInsertTrigger
ON YourTableName
AFTER INSERT
AS
BEGIN
  -- Trigger logic here
END;

Logon Trigger

A logon trigger is an event trigger in SQL Server that executes when a user logs in. It is commonly used to track logins or enforce custom security policies.

Example

CREATE TRIGGER LogonTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
  -- Trigger logic here
END;

Update Trigger

Update triggers are used to perform actions automatically after an UPDATE operation is executed on a specific table.

Example

CREATE TRIGGER AfterUpdateTrigger
ON YourTableName
AFTER UPDATE
AS
BEGIN
  -- Trigger logic here
END;

Delete Trigger

Delete triggers are used to execute actions automatically after a DELETE operation is performed on a table.

Example

CREATE TRIGGER AfterDeleteTrigger
ON YourTableName
AFTER DELETE
AS
BEGIN
  -- Trigger logic here
END;

Creating INSTEAD OF Triggers

INSTEAD OF triggers are used to intercept and replace DML operations (INSERT, UPDATE, DELETE) on a view. They allow you to customize the behavior of these operations.

Creating DDL Triggers

DDL (Data Definition Language) triggers responses to events like CREATE, ALTER, and DROP statements, allowing you to track and control changes to database schema.

Disabling Triggers

You can disable a trigger to temporarily stop it from executing automatically.

Example

DISABLE TRIGGER YourTriggerName ON YourTableName;

Enabling Triggers

To re-enable a previously disabled trigger:

Example

ENABLE TRIGGER YourTriggerName ON YourTableName;

Viewing Definition of Triggers

To view the definition of a trigger, you can use the following query:

Example

sp_helptext 'YourTriggerName';

Listing All Triggers

To list all triggers in a database:

Example

SELECT name, type_desc, parent_object_id
FROM sys.objects
WHERE type = 'TR';

Removing Triggers

To drop a trigger

Example

DROP TRIGGER YourTriggerName ON YourTableName;

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.