Types of Triggers in DBMS | SQL Tutorial and Query Example

Text copied!

Types of Triggers in DBMS

July 11, 2023, 12:06 a.m. under SQL
    • Types of Triggers in DBMS :

    Trigger are special type of stored procedure that automatically executes in response to certain events or actions occurring in the database. These events could include data manipulation operations such as INSERT, UPDATE or DELETE statements.

    In SQL Server, there are two main types of triggers in DBMS :
    After Triggers (AFTER INSERT, UPDATE, DELETE) :

    These triggers execute after the triggering event has occurred and the data modifications have been made to the database. They are commonly used for auditing purposes, logging changes, or performing additional actions after the main operation.

    Instead Of Triggers (INSTEAD OF INSERT, UPDATE, DELETE) :

    These triggers execute instead of the triggering action (INSERT, UPDATE, DELETE). They allow you to override the default behavior of the data modification operation. Instead Of triggers are often used to enforce complex business rules or to implement custom logic before the actual data modification occurs.

    In SQL Server, below is the general syntax for creating triggers in DBMS :
    Here's a basic example of creating a simple triggers in DBMS in SQL Server :

    1. Let's consider a table named '[Product]' containing the following values :

    Thank you for exploring "types of triggers in DBMS".
    CREATE TRIGGER trigger_name
ON table_name
{AFTER | INSTEAD OF} {INSERT, UPDATE, DELETE}
AS
BEGIN

{sql_statements}

END
    Types of Triggers in DBMS 
    CREATE TRIGGER Product_trgAfterInsert
ON Product
AFTER INSERT
AS
BEGIN

UPDATE [dbo].[Product]
	SET [ModifiedDate] = GETDATE()

    -- Trigger logic here
    PRINT 'Modified date is updated into product table.';

END;
    Types of Triggers in DBMS 
    INSERT INTO [dbo].[Product] (
	    [Product_Id], [Name], [GroupName] )
        VALUES 
		(2, 'Soap', 'Group-B');
    Types of Triggers in DBMS Types of Triggers in DBMS

    Frequently Asked Questions :

    The two types of triggers in DBMS in SQL Server are After Triggers (AFTER INSERT, UPDATE, DELETE) and Instead Of Triggers (INSTEAD OF INSERT, UPDATE, DELETE). After Triggers execute after data modifications, often used for auditing, logging, or additional actions. Instead Of Triggers intervene before data modifications, enabling custom processing or validation.
    There are two types of triggers in PL/SQL: Before Triggers and After Triggers. Before Triggers execute before data modifications, while After Triggers execute after data modifications.
    DDL contains multiple types of triggers. In DDL, the types of triggers present are categorized by their specific actions and functions. So, how many types of triggers are present in DDL? There are multiple types of triggers categorized by their specific actions and functions.
    There are two types of triggers present in DML operations: Before Triggers and After Triggers.
    « Previous
    Next »
    RELATED :