SQL Server Triggers

Triggers can be defined as a Procedural code that gets executed automatically
upon the execution of events like insertion, updation and deletion of Database Table
or View. Triggers cannot be called or executed directly like Stored Procedures but
instead works automatically on database operations like insert,update and delete.

Suppose there is a case by which we want to save log of a table into a history table
upon insert,update and delete actions, we can create a trigger under the table and
write code inside the trigger to save the records to history table.

There are generally two types of Triggers named as (1) After Triggers or For Triggers
and (2) Instead Of Triggers.

After Triggers :

After Triggers are triggers that gets executed only after the operation that fired it

runs successfully. This trigger is not supported for Views.There are three types of 

After Triggers :

1. After Insert Trigger

2. After Update Trigger

3. After Delete Trigger

After Insert Trigger  runs after the execution of the insert operation. If in case

insertion fails, this trigger won't get executed.

After Update Trigger is a trigger that executes after an update operation.

After Delete Trigger is a trigger that executes after a delete operation.

I will explain with examples below. Suppose there is a Product table and a Product_Log

table as the following :

CREATE TABLE [dbo].[Product](

      [ProductID] [int] IDENTITY(1,1) NOT NULL,

      [Name] [nvarchar](50) NULL,

      [Price] [decimal](18, 2) NULL,

      [Description] [nvarchar](max) NOT NULL,

 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

(

      [ProductID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Note : ProductID is an auto-increment identity column.

.................................................................................................

CREATE TABLE [dbo].[Product_Log](

      [LogID] [int] IDENTITY(1,1) NOT NULL,

      [ProductID] [int] NULL,

      [Name] [nvarchar](50) NULL,

      [Price] [decimal](18, 2) NULL,

      [Description] [nvarchar](max) NULL,

      [Activity] [char](1) NULL,

      [LogDate] [datetime] NULL,

 CONSTRAINT [PK_Product_Log] PRIMARY KEY CLUSTERED

(

      [LogID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[Product_Log] ADD  CONSTRAINT [DF_Product_Log_LogDate]  DEFAULT (getdate()) FOR [LogDate]

GO

Note : LogID is an auto-increment identity column. LogDate has default value set 
as getdate().

............................................................................................

Then below is the example for After Insert Trigger created under Product table :

CREATE TRIGGER [dbo].[ProductInsertTrigger]

   ON  [dbo].[Product]

   AFTER INSERT

AS

BEGIN

    declare @id int;

      declare @name varchar(50);

      declare @price decimal(10,2);

      declare @description varchar(MAX);

      declare @activity char(1);

    select @id=i.ProductID from inserted i;    

      select @name=i.Name from inserted i;     

      select @description=i.Description from inserted i;   

      select @price=i.Price from inserted i;

      select @activity='I';

      insert into Product_Log(ProductID,Name,Price,Description,Activity) values

      (@id,@name,@price,@description,@activity)

END

In the above trigger we can see that the inserted value is obtained from the logical

table, inserted.

Suppose I run the following insert query to Product table :

insert into Product(Name,Description,Price) values ('test','test desc',100)

we can see a new row is inserted also in Product_Log table as a result of

the execution of the After Insert Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate

1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360

Next is the example for After Update Trigger created under the above Product

table :

CREATE TRIGGER ProductUpdateTrigger

   ON  [dbo].[Product]

   AFTER UPDATE

AS

BEGIN

    declare @id int;

      declare @name varchar(50);

      declare @price decimal(10,2);

      declare @description varchar(MAX);

      declare @activity char(1);

    select @id=i.ProductID from inserted i;    

      select @name=i.Name from inserted i;     

      select @description=i.Description from inserted i;   

      select @price=i.Price from inserted i;

      select @activity='U';

      insert into Product_Log(ProductID,Name,Price,Description,Activity) values

      (@id,@name,@price,@description,@activity)

END

GO

For After Update trigger also the logical table, inserted is used as there is no logical

table named updated. In order to know that whether a specified column is updated, we

can use the function update(column-name). For eg. if we want to check whether the

price column is updated, we can add the following to the trigger :

if update(Price)

 some conditional query

Suppose I run the following update query to Product table :

Update Product set Name='test2',Price=200 where ProductID=1

we can see a new row is inserted also in Product_Log table as a result of

the execution of the After Update Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate

1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360

2     1     test2 200.00      test desc   U     2016-10-14 23:19:22.533

Next is the example for After Delete Trigger created under the above Product

table :

CREATE TRIGGER ProductDeleteTrigger

   ON  [dbo].[Product]

   FOR DELETE

AS

BEGIN

    declare @id int;

      declare @name varchar(50);

      declare @price decimal(10,2);

      declare @description varchar(MAX);

      declare @activity char(1);

    select @id=i.ProductID from deleted i;     

      select @name=i.Name from deleted i;

      select @description=i.Description from deleted i;    

      select @price=i.Price from deleted i;

      select @activity='D';

      insert into Product_Log(ProductID,Name,Price,Description,Activity) values

      (@id,@name,@price,@description,@activity)

END

GO

In the above trigger we can see that the deleted value is obtained from the logical

table, deleted.

Suppose I run the following delete query to Product table :

Delete from Product where ProductID=1

we can see a new row is inserted also in Product_Log table as a result of

the execution of the After Delete Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate

1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360

2     1     test2 200.00      test desc   U     2016-10-14 23:19:22.533

3     1     test2 200.00      test desc   D     2016-10-14 23:29:05.053

Instead Of Triggers :

Instead Of Triggers are triggers that gets executed before the operation that fired it.
In short this triggers are executed instead of operations like insert, update or delete.
This trigger can be created both for tables and views.There are three types of 

Instead Of Triggers :

1. Instead Of Insert Trigger

2. Instead Of Update Trigger

3. Instead Of Delete Trigger

Instead Of  Insert Triggers:

This type of trigger is executed before the insert operation that fired it and insertion
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Insert Trigger as the following :

CREATE TRIGGER [dbo].[ProductInsteadOfInsert] ON [dbo].[Product]

INSTEAD OF Insert

AS

BEGIN

DECLARE @Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)

select @Name=i.Name from inserted i

select @Price=i.Price from inserted i

select @description=i.Description from inserted i

 if(@Price<10)

 BEGIN

 RAISERROR('Cannot Insert where price < 10',16,1)

 ROLLBACK

 END

 ELSE

 BEGIN

 Insert into Product (Name,Description,Price) values (@Name,@Description,@Price)

 PRINT 'Instead Of Insert Trigger - Product inserted'

 END

END

In the trigger you can see the condition that if the price is less than 10 it will

raise the error and otherwise insertion will take place.

If I run the insert query as :

insert into Product(Name,Description,Price) values ('test','test desc',8)

It will show the error as:

Cannot Insert where price < 10

If I run the insert query as :

insert into Product(Name,Description,Price) values ('test','test desc',800)

It will insert the data to the table and print  'Instead Of Insert Trigger - Product inserted'

Instead Of  Update Triggers:

This type of trigger is executed before the update operation that fired it and updation
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Update Trigger as the
following :

CREATE TRIGGER [dbo].[ProductInsteadOfUpdate] ON [dbo].[Product]

INSTEAD OF UPDATE

AS

BEGIN

DECLARE @ID int,@Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)

select @ID=i.ProductID from inserted i

select @Name=i.Name from inserted i

select @Price=i.Price from inserted i

select @description=i.Description from inserted i

 if(@Price<10)

 BEGIN

 RAISERROR('Cannot update where price < 10',16,1)

 ROLLBACK

 END

 ELSE

 BEGIN

 Update Product set Name=@Name,Description=@Description,Price=@Price where ProductID=@ID

 PRINT 'Instead Of Update Trigger - Product updated'

 END

END

In the trigger you can see the condition that if the price is less than 10 it will

raise the error and otherwise updation will take place.

If I run the update query as :

update Product set Price=8 where ProductID=6

It will show the error as:

Cannot update where price < 10

If I run the update query as :

update Product set Price=900 where ProductID=6

It will update the corresponding data of the table and print 

'Instead Of Update Trigger - Product updated'

Instead Of  Delete Triggers:

This type of trigger is executed before the delete operation that fired it and deletion
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Delete Trigger as the
following :

CREATE TRIGGER [dbo].[ProductInsteadOfDelete] ON [dbo].[Product]

INSTEAD OF Delete

AS

BEGIN

DECLARE @ID int,@Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)

select @ID=i.ProductID from deleted i

select @Name=i.Name from deleted i

select @Price=i.Price from deleted i

select @description=i.Description from deleted i

 if(@Price>1000)

 BEGIN

 RAISERROR('Cannot delete where price>1000',16,1)

 ROLLBACK

 END

 ELSE

 BEGIN

 Delete from Product where ProductID=@ID

 PRINT 'Instead Of Delete Trigger - Product deleted'

 END

END

In the trigger you can see the condition that if the price is greater than 1000 it will

raise the error and otherwise deletion will take place. Here logical table named deleted

is used.

Suppose product with productID has price 8000 and if I run the delete query as :

Delete Product where ProductID=5

It will show the error as:

Cannot delete where price >1000

And suppose if I run the delete query for another product with price less than 1000 :

Delete Product where ProductID=6

It will delete the corresponding data of the table and print 

'Instead Of Delete Trigger - Product deleted'

How to write an XML file using XmlWriter

XmlWriter :

XmlWriter class writes XML data to a stream or a file in a fast and non-cached way.

Some important methods of XmlWriter are the following :

Create(String) :

This method creates an XmlWriter object using the specified filename.

Create(Stream) :

This method creates an XmlWriter object using the specified stream.

Close() :

This method is used to close the current stream and its underlying stream.

Dispose() :

This method disposes all resources of the XmlWriter object.

Flush() :

This method flushes whatever in the buffer of the current stream to the underlying

stream and also it flushes from the underlying stream.

WriteAttributeString(String,String) :

This method is used to write the attribute with the specified name and value.

WriteComment(String) :

This method writes comment with the specified string inside <!--...-->

WriteElementString(String,String) :

This method writes an element with the specified name and value.

WriteEndAttribute() :

This method closes the previous WriteStartAttribute call.

WriteEndDocument() :

This method closes any open elements or attributes and puts the writer back to

the start state.

WriteEndElement() :

This method closes the current element.

WriteNode(XmlReader,Boolean) :

This method copies everything from the XmlReader to the XmlWriter and moves the

reader to the start of the next sibling.

WriteNode(XPathNavigator,Boolean) :

This method copies everything from the XPathNavigator to the XmlWriter and
XPathNavigator will remain in the same position.

WriteStartAttribute(String) :

This method writes the start of an attribute with the specified name.

WriteStartDocument() :

This method writes the XML declaration with the version "1.0".

WriteStartElement(String) :

This method writes the start tag of the element with the specified local name.

WriteString(String) :

This method writes the specified text content.

WriteValue(String) :

This writes value which is a String.

Like this there are other parameter datatypes for WriteValue like DateTime, Int32,
Int64, Boolean, Decimal, Double, etc.

Next I will show one example for the code to write XML file using XmlWriter on a

Button click.

C# Code :

protected void Button1_Click(object sender, EventArgs e)

{

        XmlWriter xmlWriter = XmlWriter.Create(Server.MapPath("~/employee.xml"));

        xmlWriter.WriteStartDocument();

        xmlWriter.WriteStartElement("employees");

        xmlWriter.WriteStartElement("employee");

        xmlWriter.WriteAttributeString("post", "Manager");

        xmlWriter.WriteString("Anoop");

        xmlWriter.WriteEndElement();

        xmlWriter.WriteStartElement("employee");

        xmlWriter.WriteAttributeString("post", "Programmer");

        xmlWriter.WriteString("Lia");

        xmlWriter.WriteEndDocument();

        xmlWriter.Close();

 }

Output :

This will generate an XML file named employee.xml in the root directory of the

website with the following contents :

<?xml version="1.0" encoding="utf-8"?>

<employees>

<employee post="Manager">

Anoop

</employee>

<employee post="Programmer">

Lia

</employee>

</employees>

Select XML using XPathNavigator

XPathDocument :

XPathDocument class provides read-only and fast representation of an XML

document using XPath data model. It is contained inside the namespace

System.Xml.XPath.

For navigating through nodes of either XPathdocument or  an XmlDocument,

we can use XPathNavigator class which is initialized using CreateNavigator()

method. XPathNavigator has various methods to select set of nodes using an XPath

expression. If it selects a single node, it is returned in an XPathNavigator object and

in case of set of nodes, it is returned in an XPathNodeIterator object.

I will list some important properties of XPathNavigator :

InnerXml - which gets or sets the mark up of the child nodes of the current node.

OuterXml - which gets or sets the mark up that represents the opening and closing tags
of the current node and its child nodes.

BaseURI - gets the BaseURI of the current node.

HasAttributes - gets a value that tells whether the current node has attributes.

HasChildren - gets a value that tells whether the current node has child nodes.

Value - gets the value of the current item.

ValueAsDouble - gets the value of the current node as a Double.

ValueAsInt - gets the value of the current node as an Int32.

ValueAsLong - gets the value of the current node as an Int64.

ValueAsBoolean - gets the value of the current node as a Boolean.

ValueAsDateTime - gets the value of the current node as a DateTime.

Name - gets the name of the current node.

NodeType - gets the XPathNodeType of the current node.

IsNode - which tells whether the current item is a node.

IsEmptyElement - which tells whether the current node is an empty element
without an end tag.

Some important methods of XPathNavigator are listed below:

CreateAttributes() - This method is used to create new attributes to the current node.

DeleteSelf() - which deletes the current node and its child nodes.

MoveToFirst() - which moves the XPathNavigator to the first sibling of the current node.

MoveToFirstAttribute() - which moves the XPathNavigator to the first attribute of the
current node.

MoveToFirstChild() - which moves the XPathNavigator to the first child node of the current node.

MoveToNext() - which moves the XPathNavigator to the next sibling node of the current node.

MoveToNextAttribute() - which moves the XPathNavigator to the next attribute of the
current node.

MoveToParent() - which moves the XPathNavigator to the parent node of the current node.

MoveToPrevious() - which moves the XPathNavigator to the previous sibling node of the current node.

MoveToRoot() - which moves the XPathNavigator to the root node that the current node belongs to.

ReadSubTree() - This method returns an XmlReader that contains the current node and its
child nodes.

SetValue(String) - Sets the value of the current node with the String specified.

SelectSingleNode(String) - Select a single node in the XPathNavigator using the specified
XPath query.

Select(String) - Select a set of nodes in the XPathNavigator using the specified
XPath query.

Below is an example to read XML data using XPathNavigator.

Suppose there is an XML document as following :

employee.xml :

<?xml version="1.0" encoding="utf-8"?>

<company>

  <department dep_name="HR">

    <employee>Amal</employee>

    <employee>Minnu</employee>

    <employee>Anu</employee>

  </department>

  <department dep_name="IT">

    <employee>Joy</employee>

    <employee>Lia</employee>

  </department>

</company>

C# code to read XML is as following :

C# Code :

We have to add the following namespace :

using System.Xml.XPath;

protected void Button1_Click(object sender, EventArgs e)

{

        XPathDocument document = new XPathDocument(Server.MapPath("~/employee.xml"));

        XPathNavigator navigator = document.CreateNavigator();

        XPathNodeIterator nodes = navigator.Select("company/department/employee");

        while (nodes.MoveNext())

        {

            Response.Write(nodes.Current.Value +"<br/>");

        }

}

Output :

Amal
Minnu
Anu
Joy
Lia

Code for selecting single node is :

XPathNavigator nav = navigator.SelectSingleNode("company/department/employee");

Response.Write(nav.Value);

This will display the result of first employee node which is Amal

Next I will explain the code using OuterXml and InnerXml of  XPathNavigator.

C# Code :

protected void Button1_Click(object sender, EventArgs e)

{

        XPathDocument document = new XPathDocument(Server.MapPath("~/employee.xml"));

        XPathNavigator navigator = document.CreateNavigator();

       

        string str1 = navigator.OuterXml;

        navigator.MoveToChild(XPathNodeType.Element);

        string str2 = navigator.InnerXml;

}

Here str1 will contain the markup of the entire XML document starting from the

Company node. And str2 contains markup starting from the child of Company

node which is department. 

That is, value of str1 is :

<company>

  <department dep_name="HR">

    <employee>Amal</employee>

    <employee>Minnu</employee>

    <employee>Anu</employee>

   </department>

  <department dep_name="IT">

    <employee>Joy</employee>

    <employee>Lia</employee>

  </department>

</company>

And value of str2 is :

  <department dep_name="HR">

    <employee>Amal</employee>

    <employee>Minnu</employee>

    <employee>Anu</employee>

  </department>

  <department dep_name="IT">

    <employee>Joy</employee>

    <employee>Lia</employee>

  </department>

How to convert an XPathNavigator to an XmlReader? 

XPathNavigator has ReadSubTree method which helps to copy the entire XML or

one node and its child nodes to an XmlReader. Below is the example code:

C# Code :

protected void Button1_Click(object sender, EventArgs e)
{

        XPathDocument document = new XPathDocument(Server.MapPath("~/employee.xml"));

        XPathNavigator navigator = document.CreateNavigator();

        XmlReader reader = navigator.ReadSubtree();

        while (reader.Read())

        {

            string str1 = reader.ReadInnerXml();

        }

        reader.Close();

        navigator.MoveToChild("company","");

        navigator.MoveToChild("department", "");

        navigator.MoveToChild("employee", "");

        XmlReader emp = navigator.ReadSubtree();

        while (emp.Read())

        {

            string str2 = emp.ReadInnerXml();

        }

        emp.Close();

 }

If we execute the above code we can see that the reader object holds markup

starting from company node as ReadSubtree function reads the current node 

and its child nodes. Value of String str1 will be the markup starting from

department node as ReadInnerXml function reads the innerXml of the Company node.

MoveToChild function helps to move to the child node from where the XPathNavigator

is positioned. We can see that the value of String str2 will be the name of the first 

employee which is Amal.