Text copied!
Types of Triggers in DBMS
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.
Triggers enforce rules, keep data accurate and ensure consistency in databases. Triggers can run before or after an event (BEFORE or AFTER triggers) and can operate on each affected row (ROW-level triggers) or on the entire action (STATEMENT-level triggers).
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.
Triggers are created using the CREATE TRIGGER statement and are associated with a specific table or view. They can be defined to execute for INSERT, UPDATE, or DELETE operations, either before or after the event.
In SQL Server, below is the general syntax for creating triggers in DBMS :
CREATE TRIGGER trigger_name
ON table_name
{AFTER | INSTEAD OF} {INSERT, UPDATE, DELETE}
AS
BEGIN
{sql_statements}
END
• trigger_name : This is the name you give to the trigger. It must be unique within the database and follow the rules for naming identifiers in SQL Server.
• table_name : This is the name of the table or view on which the trigger operates.
• {AFTER | INSTEAD OF} : This specifies whether the trigger should execute after the triggering action (AFTER) or instead of the triggering action (INSTEAD OF).
• {INSERT, UPDATE, DELETE} : This specifies the type of data modification operation that will trigger the execution of the trigger.
• AS : This keyword indicates the beginning of the trigger's body.
• {sql_statements} : These are the SQL statements that constitute the body of the trigger. These statements can be any valid SQL statements, including data manipulation, procedural logic, or function calls.
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 :
2. Let's establish a trigger on the Product table so that upon any insertion operation, it will automatically execute and update the modified date field for all affected records within the same Product table. Execute below query :
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;
3. Now, let's execute the following SQL statement to insert a row into the Product table. Populating three fields while leaving the modified date field empty. Run this SQL statement :
INSERT INTO [dbo].[Product] (
[Product_Id], [Name], [GroupName] )
VALUES
(2, 'Soap', 'Group-B');
4. If you observed, the created trigger was automatically executed immediately after we inserted a row into the Product table. In the trigger, we've specified the `GETDATE()` function, indicating that the modified date should be updated with the current date automatically. Now, let's examine the Product table.
5. As evident from the successful insertion, the modified date was also updated by the trigger. This concludes our overview of "types of triggers in DBMS" in SQL Server and their functionality in DBMS.