Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday 29 November 2012

SQL Server Different Types of Cursors and Its Definitions & Triggers Defintion and Different Types of Triggers


Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.

Life Cycle of Cursor

  1. Declare Cursor

    A cursor is declared by defining the SQL statement that returns a result set.
  2. Open

    A Cursor is opened and populated by executing the SQL statement defined by the cursor.
  3. Fetch

    When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
  4. Close

    After data manipulation, we should close the cursor explicitly.
  5. Deallocate

    Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax to Declare Cursor

Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The basic syntax is given below
 DECLARE cursor_name CURSOR
 [LOCAL | GLOBAL] --define cursor scope
 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
 FOR select_statement --define SQL Select statement
 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

Syntax to Open Cursor

A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:
 OPEN [GLOBAL] cursor_name --by default it is local 

Syntax to Fetch Cursor

Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:
 FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name 
INTO @Variable_name[1,2,..n] 

Syntax to Close Cursor

Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:
 CLOSE cursor_name --after closing it can be reopen 

Syntax to Deallocate Cursor

Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
 DEALLOCATE cursor_name --after deallocation it can't be reopen 

SQL SERVER – Simple Examples of Cursors

 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee 

 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF 


A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.
You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Types of Cursors

  1. Static Cursors

    A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.
    You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source. By default static cursors are scrollable.
  2. Dynamic Cursors

    A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.
  3. Forward Only Cursors

    A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.
    There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.

    A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.
    A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.
  4. Keyset Driven Cursors

    A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.

SQL SERVER – Examples of Cursors

  1. CREATE TABLE Employee
  2. (
  3. EmpID int PRIMARY KEY,
  4. EmpName varchar (50) NOT NULL,
  5. Salary int NOT NULL,
  6. Address varchar (200) NOT NULL,
  7. )
  8. GO
  9. INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
  10. INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
  11. INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
  12. INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
  13. INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
  14. GO
  15. SELECT * FROM Employee

Static Cursor - Example

  1. SET NOCOUNT ON
  2. DECLARE @Id int
  3. DECLARE @name varchar(50)
  4. DECLARE @salary int
  5. DECLARE cur_emp CURSOR
  6. STATIC FOR
  7. SELECT EmpID,EmpName,Salary from Employee
  8. OPEN cur_emp
  9. IF @@CURSOR_ROWS > 0
  10. BEGIN
  11. FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
  12. WHILE @@Fetch_status = 0
  13. BEGIN
  14. PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
  15. FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
  16. END
  17. END
  18. CLOSE cur_emp
  19. DEALLOCATE cur_emp
  20. SET NOCOUNT OFF

Dynamic Cursor - Example

  1. --Dynamic Cursor for Update
  2. SET NOCOUNT ON
  3. DECLARE @Id int
  4. DECLARE @name varchar(50)
  5. DECLARE Dynamic_cur_empupdate CURSOR
  6. DYNAMIC
  7. FOR
  8. SELECT EmpID,EmpName from Employee ORDER BY EmpName
  9. OPEN Dynamic_cur_empupdate
  10. IF @@CURSOR_ROWS > 0
  11. BEGIN
  12. FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
  13. WHILE @@Fetch_status = 0
  14. BEGIN
  15. IF @name='Mohan'
  16. Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
  17. FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
  18. END
  19. END
  20. CLOSE Dynamic_cur_empupdate
  21. DEALLOCATE Dynamic_cur_empupdate
  22. SET NOCOUNT OFF
  23. Go
  24. Select * from Employee

  1. -- Dynamic Cursor for DELETE
  2. SET NOCOUNT ON
  3. DECLARE @Id int
  4. DECLARE @name varchar(50)
  5. DECLARE Dynamic_cur_empdelete CURSOR
  6. DYNAMIC
  7. FOR
  8. SELECT EmpID,EmpName from Employee ORDER BY EmpName
  9. OPEN Dynamic_cur_empdelete
  10. IF @@CURSOR_ROWS > 0
  11. BEGIN
  12. FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
  13. WHILE @@Fetch_status = 0
  14. BEGIN
  15. IF @name='Deepak'
  16. DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
  17. FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
  18. END
  19. END
  20. CLOSE Dynamic_cur_empdelete
  21. DEALLOCATE Dynamic_cur_empdelete
  22. SET NOCOUNT OFF
  23. Go
  24. Select * from Employee

Forward Only Cursor - Example

  1. --Forward Only Cursor for Update
  2. SET NOCOUNT ON
  3. DECLARE @Id int
  4. DECLARE @name varchar(50)
  5. DECLARE Forward_cur_empupdate CURSOR
  6. FORWARD_ONLY
  7. FOR
  8. SELECT EmpID,EmpName from Employee ORDER BY EmpName
  9. OPEN Forward_cur_empupdate
  10. IF @@CURSOR_ROWS > 0
  11. BEGIN
  12. FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
  13. WHILE @@Fetch_status = 0
  14. BEGIN
  15. IF @name='Amit'
  16. Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
  17. FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
  18. END
  19. END
  20. CLOSE Forward_cur_empupdate
  21. DEALLOCATE Forward_cur_empupdate
  22. SET NOCOUNT OFF
  23. Go
  24. Select * from Employee

  1. -- Forward Only Cursor for Delete
  2. SET NOCOUNT ON
  3. DECLARE @Id int
  4. DECLARE @name varchar(50)
  5. DECLARE Forward_cur_empdelete CURSOR
  6. FORWARD_ONLY
  7. FOR
  8. SELECT EmpID,EmpName from Employee ORDER BY EmpName
  9. OPEN Forward_cur_empdelete
  10. IF @@CURSOR_ROWS > 0
  11. BEGIN
  12. FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
  13. WHILE @@Fetch_status = 0
  14. BEGIN
  15. IF @name='Sonu'
  16. DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
  17. FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
  18. END
  19. END
  20. CLOSE Forward_cur_empdelete
  21. DEALLOCATE Forward_cur_empdelete
  22. SET NOCOUNT OFF
  23. Go
  24. Select * from Employee

Keyset Driven Cursor - Example

  1. -- Keyset driven Cursor for Update
  2. SET NOCOUNT ON
  3. DECLARE @Id int
  4. DECLARE @name varchar(50)
  5. DECLARE Keyset_cur_empupdate CURSOR
  6. KEYSET
  7. FOR
  8. SELECT EmpID,EmpName from Employee ORDER BY EmpName
  9. OPEN Keyset_cur_empupdate
  10. IF @@CURSOR_ROWS > 0
  11. BEGIN
  12. FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
  13. WHILE @@Fetch_status = 0
  14. BEGIN
  15. IF @name='Pavan'
  16. Update Employee SET Salary=27000 WHERE CURRENT OF Keyset_cur_empupdate
  17. FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
  18. END
  19. END
  20. CLOSE Keyset_cur_empupdate
  21. DEALLOCATE Keyset_cur_empupdate
  22. SET NOCOUNT OFF
  23. Go
  24. Select * from Employee

  1. -- Keyse Driven Cursor for Delete
  2. SET NOCOUNT ON
  3. DECLARE @Id int
  4. DECLARE @name varchar(50)
  5. DECLARE Keyset_cur_empdelete CURSOR
  6. KEYSET
  7. FOR
  8. SELECT EmpID,EmpName from Employee ORDER BY EmpName
  9. OPEN Keyset_cur_empdelete
  10. IF @@CURSOR_ROWS > 0
  11. BEGIN
  12. FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
  13. WHILE @@Fetch_status = 0
  14. BEGIN
  15. IF @name='Amit'
  16. DELETE Employee WHERE CURRENT OF Keyset_cur_empdelete
  17. FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
  18. END
  19. END
  20. CLOSE Keyset_cur_empdelete
  21. DEALLOCATE Keyset_cur_empdelete
  22. SET NOCOUNT OFF
  23. Go Select * from Employee



Different Types of SQL Server Triggers:

Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.

Types of Triggers

In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.
  1. DDL Triggers

    In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.
    Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.
    We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.
    DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.
  2. DML Triggers

    In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types
    1. After Trigger (using FOR/AFTER CLAUSE)

      This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
    2. Instead of Trigger (using INSTEAD OF CLAUSE)

      This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
  3. CLR Triggers

    CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.
    We coded the objects(like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later.

Logon Triggers: SQL Server

As the name implies, logon triggers fire in response to the logon event that is raised when a user sessions is being established. The logon triggers can be used to audit and control server sessions, such as tracking the login activity, restricting logins to SQL Server, or limiting or denying the number of login sessions for a specific user. The logon triggers always run after the authentication phase, but before the user session is actually established, which means that trigger logon will not fire if authentication fails. Therefore, all messages originating inside the trigger logon, which would usually reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.
For example, the following code shows how logon trigger rejects attempts to logon for “testuser1″, if they are initiated outside business hours i.e. between 10:00 and 18:00 hours.
First step of the process is to create SQL user called “testuser1″ on the server, To do so execute the following code:
USE [master]
GO

--Create the login on your servel called "testuser1"
CREATE LOGIN [testuser1] WITH PASSWORD=N'StrongPassword'
    ,DEFAULT_DATABASE=[master]
    ,DEFAULT_LANGUAGE=[us_english]
    ,CHECK_EXPIRATION=OFF
    ,CHECK_POLICY=OFF
GO
Now create logon trigger called “connection_limit_trigger”, which only permits login attempts for “testuser1″ during business hours i.e. between 10:00 and 18:00 hours.
USE [master]
GO

CREATE TRIGGER [connection_limit_trigger]
ON ALL SERVER
FOR LOGON 
AS
BEGIN
DECLARE @ErrorText [varchar](128)

SET @ErrorText = 'Cannot allow login to "testuser1" outside of normal business hours. '
SET @ErrorText = @ErrorText + 'Please try again between business hours 10:00 and 18:00.'

IF ORIGINAL_LOGIN() = 'testuser1' AND
    (DATEPART(HOUR, GETDATE()) < 10 OR DATEPART (HOUR, GETDATE()) > 18)
    BEGIN
        PRINT @ErrorText
        ROLLBACK;
    END
END;
GO

ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER  
GO
Now that we have configured our logon trigger, the “testuser1″ will not be able to log on to SQL Server outside the specified business hours of 10:00 to 18:00. The following dialog box will appear if you attempt to login to SQL Server with “testuser1” SQL login:
If you review the SQL Server error logon, you will see that it has an entry about this failed login attempt containing the information message from our logon trigger:
One interesting observation about logon trigger, which you can see from above example, is that the logon triggers generates multiple entries for successful and failed logon attempts.
  1. Logon Triggers

    Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.
    Synatx for Logon Trigger
    1. CREATE TRIGGER trigger_name
    2. ON ALL SERVER
    3. [WITH ENCRYPTION]
    4. {FOR|AFTER} LOGON
    5. AS
    6. sql_statement [1...n ]

Syntax for Trigger

  1. CREATE TRIGGER trigger_name
  2. ON {table|view}
  3. [WITH ENCRYPTION|EXECUTE AS]
  4. {FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]}
  5. [NOT FOR REPLICATION]
  6. AS
  7. sql_statement [1...n ]
  1. trigger_name

    This is the name of the trigger. It should conform to the rules for identifiers in Sql Server.
  2. table|view

    This is the table/view on which the trigger is to be created.
  3. ENCRYPTION

    This option is optional. If this option is specified, original text of the CREATE TRIGGER statement will be encrypted.
  4. EXECUTE AS

    This option is optional. This option specifies, the security context under which the trigger is executed.
  5. FOR/AFTER

    FOR/AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified.AFTER triggers cannot be defined on views.
  6. INSTEAD OF

    INSTEAD OF specifies that the trigger is Instead Of Trigger.
  7. CREATE|ALTER|DROP|INSERT|UPDATE|DELETE

    These keywords specify on which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used.
  8. NOT FOR REPLICATION

    Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.
  9. AS

    After this we specifies the actions and condition that the trigger perform.
  10. sql_statement

    These are the trigger conditions and actions. The trigger actions specified in the T-SQL statements.

Note

  1. The name of a trigger should follow the rules for identifiers.
  2. DML trigger can be composed by any T-SQL statements, except CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and RESTORE LOG statements.
  3. You cannot create triggers against system tables or dynamic management views. Moreover, the TRUNCATE TABLE statement does not fire a trigger because this operation does not log individual row deletions.
  4. If you use the DATABASE option, the scope of your DDL trigger will be the current database. If you use the ALL SERVER option, the scope of your DDL triggers to the current server.
  5. AFTER triggers cannot be defined on views.
  6. AFTER is the default, if FOR is the only keyword specified.

What is a Trigger

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

Types Of Triggers

There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.

Basically, triggers are classified into two main types:- 

(i) After Triggers (For Triggers) 
(ii) Instead Of Triggers 

(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views. 
AFTER TRIGGERS can be classified further into three types as: 

(a) AFTER INSERT Trigger. 
(b) AFTER UPDATE Trigger. 
(c) AFTER DELETE Trigger. 

Let’s create After triggers. First of all, let’s create a table and insert some sample data. Then, on this table, I will be attaching several triggers. 

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

I will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into another audit table. The main purpose of this audit table is to record the changes in the main table. This can be thought of as a generic audit trigger. 

Now, create the audit table as:-
CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

(a) AFTRE INSERT Trigger

This trigger is fired after an INSERT on the table. Let’s create the trigger as:-
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=i.Emp_ID from inserted i; 
 select @empname=i.Emp_Name from inserted i; 
 select @empsal=i.Emp_Sal from inserted i; 
 set @audit_action='Inserted Record -- After Insert Trigger.';

 insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER INSERT trigger fired.'
GO
The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same. 
In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Audit table. 
To see the newly created trigger in action, lets insert a row into the main table as : 
 insert into Employee_Test values('Chris',1500);

Now, a record has been inserted into the Employee_Test table. The AFTER INSERT trigger attached to this table has inserted the record into the Employee_Test_Audit as:-
6   Chris  1500.00   Inserted Record -- After Insert Trigger. 2008-04-26 12:00:55.700

(b) AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let’s create the trigger as:-
CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test] 
FOR UPDATE
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=i.Emp_ID from inserted i; 
 select @empname=i.Emp_Name from inserted i; 
 select @empsal=i.Emp_Sal from inserted i; 
 
 if update(Emp_Name)
  set @audit_action='Updated Record -- After Update Trigger.';
 if update(Emp_Sal)
  set @audit_action='Updated Record -- After Update Trigger.';

 insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER UPDATE Trigger fired.'
GO
The AFTER UPDATE Trigger is created in which the updated record is inserted into the audit table. There is no logical table updated like the logical table inserted. We can obtain the updated value of a field from theupdate(column_name) function. In our trigger, we have used, if update(Emp_Name) to check if the column Emp_Name has been updated. We have similarly checked the column Emp_Sal for an update. 
Let’s update a record column and see what happens. 
 update Employee_Test set Emp_Sal=1550 where Emp_ID=6
This inserts the row into the audit table as:- 
6  Chris  1550.00  Updated Record -- After Update Trigger.   2008-04-26 12:38:11.843 

(c) AFTER DELETE Trigger
This trigger is fired after a delete on the table. Let’s create the trigger as:- 
CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test] 
AFTER DELETE
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=d.Emp_ID from deleted d; 
 select @empname=d.Emp_Name from deleted d; 
 select @empsal=d.Emp_Sal from deleted d; 
 set @audit_action='Deleted -- After Delete Trigger.';

 insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER DELETE TRIGGER fired.'
GO
In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the audit table. 
Let’s fire a delete on the main table. 
A record has been inserted into the audit table as:- 
 6  Chris 1550.00  Deleted -- After Delete Trigger.  2008-04-26 12:52:13.867 
All the triggers can be enabled/disabled on the table using the statement 
ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL 
Specific Triggers can be enabled or disabled as :- 
ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

This disables the After Delete Trigger named trgAfterDelete on the specified table. 

(ii) Instead Of Triggers

These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:- 

(a) INSTEAD OF INSERT Trigger. 
(b) INSTEAD OF UPDATE Trigger. 
(c) INSTEAD OF DELETE Trigger. 

(a) Let’s create an Instead Of Delete Trigger as:-
CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
 declare @emp_id int;
 declare @emp_name varchar(100);
 declare @emp_sal int;
 
 select @emp_id=d.Emp_ID from deleted d;
 select @emp_name=d.Emp_Name from deleted d;
 select @emp_sal=d.Emp_Sal from deleted d;

 BEGIN
  if(@emp_sal>1200)
  begin
   RAISERROR('Cannot delete where salary > 1200',16,1);
   ROLLBACK;
  end
  else
  begin
   delete from Employee_Test where Emp_ID=@emp_id;
   COMMIT;
   insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
   PRINT 'Record Deleted -- Instead Of Delete Trigger.'
  end
 END
GO
This trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed. 
Now, let’s try to delete a record with the Emp_Sal >1200 as:- 

delete from Employee_Test where Emp_ID=4
This will print an error message as defined in the RAISE ERROR statement as:- 

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
Cannot delete where salary > 1200 

And this record will not be deleted. 
In a similar way, you can code Instead of Insert and Instead Of Update triggers on your tables. 

CONCLUSION

In this article, I took a brief introduction of triggers, explained the various kinds of triggers – After Triggers and Instead Of Triggers along with their variants and explained how each of them works. I hope you will get a clear understanding about the Triggers in Sql Server and their usage.

1 comment:

  1. Ashok Kumar Sql Development And Dba Adminstrations Techi Blog : Sql Server Different Types Of Cursors And Its Definitions And Triggers Defintion And Different Types Of Triggers >>>>> Download Now

    >>>>> Download Full

    Ashok Kumar Sql Development And Dba Adminstrations Techi Blog : Sql Server Different Types Of Cursors And Its Definitions And Triggers Defintion And Different Types Of Triggers >>>>> Download LINK

    >>>>> Download Now

    Ashok Kumar Sql Development And Dba Adminstrations Techi Blog : Sql Server Different Types Of Cursors And Its Definitions And Triggers Defintion And Different Types Of Triggers >>>>> Download Full

    >>>>> Download LINK

    ReplyDelete