Why use triggers in Microsoft SQL Server?

What are triggers:

Triggers are a special type of stored procedure which are executed automatically based on the occurrence of a database event. These events can be categorized as
  1. Data Manipulation Language (DML) and
  2. Data Definition Language (DDL) events.
The benefits derived from triggers is based in their events driven nature. Once created, the trigger automatically fires without user intervention based on an event in the database.

A) Using DML Triggers:

DML triggers are invoked when any DML commands like INSERT, DELETE, and UPDATE happen on the data of a table and or view.

Points to remember:
  1. DML triggers are powerful objects for maintaining database integrity and consistency.
  2. DML triggers evaluate data before it has been committed to the database.
  3. During this evaluation following actions are performed.
    • Compare before and after versions of data
    • Roll back invalid modification
    • Read from other tables ,those in other database
    • Modify other tables, including those in other database.
    • Execute local and remote stored procedures.
     
  4. We cannot use following commands in DML trigger
    • ALTER DATABASE
    • CREATE DATABASE
    • DISK DATABASE
    • LOAD DATABASE
    • RESTORE DATABASE
     
  5. Using the sys.triggers catalog view is a good way to list all the triggers in a database. To use it, we simply open a new query editor window in SSMS and select all the rows from the view as shown below;
    select * from sys.triggers
So let us create DML trigger.

You can create and manage triggers in SQL Server Management Studio or directly via Transact-SQL (T-SQL) statements.

1) Using AFTER triggers:
  • An AFTER trigger is the original mechanism that SQL Server created to provide an automated response to data modifications
  • AFTER triggers fire after the data modification statement completes but before the statement's work is committed to the databases.
  • The trigger has the capability to roll back its actions as well as the actions of the modification statement that invoked it.

Comments

Post a Comment

Popular posts from this blog

Stack vs Heap So far we have seen how to declare basic type variables such as int , double , etc, and complex types such as arrays and structs. The way we have been declaring them so far, with a syntax that is like other languages such as MATLAB, Python, etc, puts these variables on the stack in C. The Stack What is the stack? It's a special region of your computer's memory that stores temporary variables created by each function (including the main() function). The stack is a "FILO" (first in, last out) data structure, that is managed and optimized by the CPU quite closely. Every time a function declares a new variable, it is "pushed" onto the stack. Then every time a function exits, all of the variables pushed onto the stack by that function, are freed (that is to say, they are deleted). Once a stack variable is freed, that region of memory becomes available for other stack variables. The advantage of using the stack to store variable...
Read more

Validate Mobile Number with 10 Digits in ASP.Net

Here is the code for validating textbox for 10 digit mobile number. .ASPX File: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br\>     <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"        ControlToValidate="TextBox1" ErrorMessage="RegularExpressionValidator"      ValidationExpression="[0-9]{10}"></asp:RegularExpressionValidator>
Read more
What is  Abstraction? Abstraction is another good feature of OOPS. Abstraction means to show only the necessary details to the client of the object . Do you know the inner details of the Monitor of your PC? What happen when you switch ON Monitor? Does this matter to you what is happening inside the Monitor? No Right, Important thing for you is weather Monitor is ON or NOT. When you change the gear of your vehicle are you really concern about the inner details of your vehicle engine? No but what matter to you is that Gear must get changed that’s it!! This is abstraction; show only the details which matter to the user.  Let’s say you have a method "CalculateSalary" in your Employee class, which takes EmployeeId as parameter and returns the salary of the employee for the current month as an integer value. Now if someone wants to use that method. He does not need to care about how Employee object calculates the salary? An only thing he needs to be concern is name...
Read more