Showing posts with label Triggers. Show all posts
Showing posts with label Triggers. Show all posts

Sunday, 3 December 2017

SQL Server Instead Of Insert Trigger Example

In this tutorial, I am going to explain you INSTEAD OF INSERT Trigger in SQL-Server with example. In the previous tutorials, I had explained introduction to triggers in SQL-Server. Please go through the links provided below.

Related Links

There are two main types of DML trigger.1. After Triggers (also called as FOR trigger)2. Instead of Triggers

After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after SQL Server completes the execution of the action successfully that fired it. Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different 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 associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

Instead of Insert Trigger Example

When we try to insert a record into a table then instead of insert trigger is fired if trigger is associated with that table. Insert of trigger is mostly used with Views in SQL, but in this tutorial we are going to implement instead of trigger with single table. In article, we will use instead of trigger with Views. Now let's implement the trigger.

Create a table Employee with 3 columns EmployeeId, Name and Age. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

Share:

Sunday, 1 October 2017

SQL Server After Delete Trigger with example

In this article, I am going to explain you AFTER Delete Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server. Please go through the links provided below.

Related Links

AFTER Delete Trigger (for delete) with Example

When we try to delete records from a table then delete trigger is fired if trigger is associated with that table. After Delete trigger is also called as For Delete trigger. Now, let's implement after delete trigger.

Create a table Employee with four columns EmployeeId, Name, Age and gender. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

Share:

Saturday, 16 September 2017

Introduction to Triggers in SQL Server

In this article, I am going to explain you triggers in SQL-Server with examples. I have summarize all types of triggers as shown below. Links are provided for each triggers.

What is Trigger in SQL-Server

A trigger is a special kind of stored procedure that invokes or gets executed in response to certain action on table like insert, update or delete. You can not explicitly invoke triggers.

Types of Trigger

Below is types of triggers.
1. DML Triggers (Data Manipulation Language Triggers)
2. DDL Triggers (Data Definition Language Triggers)
3. CLR Triggers
4. Logon Triggers

DML Triggers

DML triggers are fired automatically in response to DML events that is INSERT, UPDATE and DELETE.
Below is types of DML triggers.
1. After Triggers (also called as FOR trigger)
2. Instead of Triggers

1. After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after SQL Server completes the execution of the action successfully that fired it.
Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
AFTER Insert (for insert) → Click here to read more.
AFTER Update (for update) → Click here to read more
AFTER Delete (for delete) → Click here to read more

2. Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different 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 associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Instead Of Insert → Click here to read more.
Instead Of Update
Instead Of Delete

DISABLING and ENABLING Trigger

We can disable and enable trigger using SQL Server Management Studio or using SQL below query.
1. Using Management Studio
Expand your database → go to Tables and expand it → Go to your respective table and expand it → go to Triggers → Right click on trigger name and now you can modify, disable and delete ..etc.
2. SQL Query

--To disable
DISABLE TRIGGER tr_Employee_AfterUpdate on Employee
--To enable
ENABLE TRIGGER tr_Employee_AfterUpdate on Employee

--Before enabling/disabling you can check if exists
IF OBJECT_ID ('tr_Employee_AfterUpdate', 'TR') IS NOT NULL
begin
   DISABLE TRIGGER tr_Employee_AfterUpdate on Employee
   print 'Trigger disabled'
end
else
begin
   print 'No trigger found'
end

DROPPING Trigger

We can drop or delete triggers using SQL Server Management Studio or using SQL below query.
1. Using Management Studio
Already explained.
2. SQL Query

--To delete or drop
DROP TRIGGER tr_Employee_AfterUpdate

--Before deleting you can check if exists
IF OBJECT_ID ('tr_Employee_AfterUpdate', 'TR') IS NOT NULL
begin
   DROP TRIGGER tr_Employee_AfterUpdate
   print 'Trigger deleted'
end
else
begin
   print 'No trigger found'
end

List All triggers Associated with Table

SELECT 
 t.name AS TableName,
 tr.name AS TriggerName  
FROM sys.triggers tr
INNER JOIN sys.tables t ON t.object_id = tr.parent_id
WHERE 
t.name in ('Table_Name');

List All triggers Associated with database

SELECT OBJECT_NAME(parent_id) as Parent_Object_Name, *
FROM sys.triggers

SELECT 
     sysobjects.name AS trigger_name ,
    USER_NAME(sysobjects.uid) AS trigger_owner, 
    s.name AS table_schema ,
    OBJECT_NAME(parent_obj) AS table_name ,
    OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS 'Update Trigger',
    OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS 'Delete Trigger', 
    OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS 'Insert Trigger',
    OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS  'After',
    OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger')AS 'InsteadOf',
    OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS 'Disabled' 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

 SELECT
    [so].[name] AS [trigger_name],
    USER_NAME([so].[uid]) AS [trigger_owner],
    USER_NAME([so2].[uid]) AS [table_schema],
    OBJECT_NAME([so].[parent_obj]) AS [table_name],
    OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS 'Update Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS 'Delete Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS 'Insert Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS 'After',
    OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS 'InsteadOf',
    OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS 'Disabled' 
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR'
Share:

Sunday, 10 September 2017

After Update Trigger in SQL Server with example

In this article, I am going to explain you AFTER UPDATE Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server.

Related Links

AFTER Update Trigger (for update) with Example

First of all, create a table Employee with four columns EmployeeId, Name, Age and gender. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

CREATE TABLE Employee
(
EmployeeId INT PRIMARY KEY IDENTITY,
Name VARCHAR(20),
Age INT,
Gender VARCHAR(10)
)
GO
INSERT INTO Employee VALUES('Mr. A',50,'Male')
INSERT INTO Employee VALUES('Mr. B',55,'Male')
INSERT INTO Employee VALUES('Mr. C',60,'Male')
INSERT INTO Employee VALUES('Mr. D',65,'Male')
INSERT INTO Employee VALUES('Mr. E',70,'Male')
GO

Now, execute the select query to check inserted records.

SELECT * FROM Employee
EmployeeId Name Age Gender
1         Mr. A 50 Male
2         Mr. B 55 Male
3         Mr. C 60 Male
4         Mr. D 65 Male
5         Mr. E 70 Male
 

Now, we will create a Audit Table for Employee table. When somebody update record of Employee table, then we will insert same / updated record in audit table by using after update trigger. Below is the script to create Employee_Audit table.

CREATE TABLE Employee_Audit
(
Id INT IDENTITY PRIMARY KEY,
EmployeeId INT,
Name VARCHAR(20),
Age INT,
Gender VARCHAR(10),
UpdatedDate datetime
)

Creating AFTER UPDATE Trigger

Now, we will create after update trigger. Below is the script to create after update trigger.

CREATE TRIGGER tr_Employee_AfterUpdate
 
ON Employee    -- table name 
 
after UPDATE   --(you can also use 'for update')
 
AS
BEGIN
     DECLARE @EmployeeId INT,
             @Name VARCHAR(20),
             @Age INT,
             @Gender VARCHAR(20)
 
     SELECT @EmployeeId=i.EmployeeId,
            @Name=i.Name,
            @Age=i.Age,
            @Gender=i.Gender 
            FROM inserted AS i
 
       INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE())  
END

When you update record of Employee table then SQL-Server creates a magic table named as 'inserted' and same copy of record will we inserted into this magic table. Inserted table is accessible inside the context of trigger. You can not access inserted table outside of triggers.

Executing Update Trigger

As we can not execute triggers explicitly, to execute trigger, we need to update an employee as shown below. Here update Name column whose employeeid is 1.

UPDATE Employee SET Name ='Mr. Z' WHERE employeeid=1

As you can see below a record will be updated for Employee table and a new will be inserted for Employee_Audit table.

SELECT * FROM Employee
SELECT * FROM Employee_Audit
EmployeeId Name Age Gender
1         Mr. Z 50 Male
2         Mr. B 55 Male
3         Mr. C 60 Male
4         Mr. D 65 Male
5         Mr. E 70 Male
 
Id EmployeeId Name Age Gender UpdatedDate
1 1         Mr. Z 50 Male 2017-09-10 20:21:45.030

As you can see from above trigger, a record will be inserted in audit table whether a single column or multiple column or no column in updated. Here employee with id is 10 is not present in Employee table. Execute below script to update with employee whose id 10. Here NULL be inserted in Employee_Audit table irrespective of column is updated or not.

UPDATE Employee SET age =100 WHERE employeeid=10
GO
SELECT * FROM Employee_Audit
--Result:
Id EmployeeId Name Age  Gender UpdatedDate
1 1         Mr. Z 50  Male 2017-09-10 20:21:45.030
2 NULL         NULL NULL     NULL 2017-09-10 20:32:31.987

Now to stop above situation from happening we can count no of record for INSERTED table.

ALTER TRIGGER tr_Employee_AfterUpdate
ON Employee    -- table name 
after UPDATE   --(you can also use 'for update')
AS
BEGIN
 DECLARE @EmployeeId INT,
         @Name VARCHAR(20),
         @Age INT,
         @Gender VARCHAR(20)
 
 SELECT @EmployeeId=i.EmployeeId,
        @Name=i.Name,
        @Age=i.Age,
        @Gender=i.Gender 
        FROM inserted AS i
 
 
 DECLARE @inscnt INT; -- INSERTED row count
 SELECT @inscnt = COUNT(*) FROM INSERTED;
 IF @inscnt > 0
 BEGIN
 INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE()) 
 END
END

Now we can also check for specific column using UPDATE() function. If specific column is updated then only insert record into audit table.

ALTER TRIGGER tr_Employee_AfterUpdate
ON Employee    -- table name 
after UPDATE   --(you can also use 'for update')
AS
BEGIN
 DECLARE @EmployeeId INT,
         @Name VARCHAR(20),
         @Age INT,
         @Gender VARCHAR(20)
 
 SELECT @EmployeeId=i.EmployeeId,
        @Name=i.Name,
        @Age=i.Age,
        @Gender=i.Gender 
        FROM inserted AS i
 
 DECLARE @inscnt INT; -- INSERTED row count
 SELECT @inscnt = COUNT(*) FROM INSERTED;
 IF @inscnt > 0
 BEGIN
  IF UPDATE(Name) --check for updated column
  BEGIN
   INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE())
  END
 END
END

Now we can also check for multiple column is updated.

Using UPDATE() function

IF (UPDATE(Name) OR UPDATE(age))
BEGIN
 INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE()) 
END 
--OR
IF (UPDATE(Name) AND UPDATE(age))
BEGIN
 INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE()) 
END

Using COLUMNS_UPDATED() function

ALTER TRIGGER tr_Employee_AfterUpdate
ON Employee
after UPDATE
AS
BEGIN
 DECLARE @EmployeeId INT,
         @Name VARCHAR(20),
         @Age INT,
         @Gender VARCHAR(20)
 
 SELECT @EmployeeId=i.EmployeeId,
        @Name=i.Name,
        @Age=i.Age,
        @Gender=i.Gender 
        FROM inserted AS i
 
/*
Check whether columns 2, 3 or 4 have been column updated. If any or all  
columns 2, 3 or 4 have been changed, then insert into audit column.
Thebitmask is: 
power(2,(2-1)) + power(2,(3-1)) + power(2,(4-1)) = 14
 
Use IF (COLUMNS_UPDATED() & 14) > 0   -- for any or all (2, 3 or 4 columns is updated)
 
Use IF (COLUMNS_UPDATED() & 14) = 14 -- for all column (2, 3, and 4 columns are updated)
 
*/
 
 DECLARE @inscnt INT; -- INSERTED row count
 SELECT @inscnt = COUNT(*) FROM INSERTED;
 IF @inscnt > 0
 BEGIN
   IF (COLUMNS_UPDATED() & 14) > 0   --check if any/all  column is updated
  BEGIN
   INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE())
  END
 
  /*
  IF (COLUMNS_UPDATED() & 14) = 14   --check if all columns are updated
  begin
   insert into Employee_Audit values(@EmployeeId,@Name,@Age,@Gender,GETDATE())
  end
  */
 
 END
 
END
Share:

Friday, 25 August 2017

After Insert Trigger in SQL Server with example

In this article, I am going to explain you AFTER Insert Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server.

Related Links

AFTER Insert Trigger (for insert) with Example

We will be discussing only after insert trigger in this demo. First of all, create a table Employee with three columns EmployeeId, Name and Age. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

create table Employee
(
EmployeeId int primary key identity,
Name varchar(20),
Age int
)
go
insert into Employee values('Mr. A',50)
insert into Employee values('Mr. B',55)
insert into Employee values('Mr. C',60)
insert into Employee values('Mr. D',65)
insert into Employee values('Mr. E',70)
go
select * from Employee
Creating table for triggers

Now, we will create a Audit Table for Employee table. When somebody insert record into Employee table, then we will insert same record in audit table by using after trigger. Below is the script to create Employee_Audit table.

create table Employee_Audit
(
Id int identity primary key,
EmployeeId int,
Name varchar(20),
Age int,
InsertedDate datetime
)

Creating AFTER INSERT Trigger

Now, we will create a trigger. Below is the script to create after insert trigger.

create trigger tr_Employee_AfterInsert
on Employee
after insert   --(you can also use 'for insert')

as
begin
     declare @EmployeeId int,
             @Name varchar(20),
             @Age int
             
     select @EmployeeId=i.EmployeeId,@Name=Name,@Age=Age from inserted as i
    
     insert into Employee_Audit values(@EmployeeId,@Name,@Age,GETDATE())
          
end

When you insert a record into Employee table then SQL-Server creates a magic table named as 'inserted' and same copy of record will we inserted into this magic table. Inserted table is accessible inside the context of trigger. You can not access inserted table outside of triggers.

Executing AFTER INSERT Trigger

As we can not execute triggers explicitly, so we will insert a new record Employee table. When you insert new record into Employee table, after insert trigger will be fired and a new record will be inserted into Employee_Audit table as shown below. Initially we are having only 5 record in Employee table as shown above.

Before executing triggers

Now we will insert a new record in Employee table.

insert into Employee values('Mr. G',75)

As you can see below a new record will be inserted into both tables Employee and Employee_Audit.

After executing triggers
Share:

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

Ads By Chitika