Trigger

After Trigger, Instead of Trigger Example

Posted By : Shailendra Chauhan, 09 May 2011

Updated On : 24 Jun 2014

 Total Views : 162,396   

 Version Support : SQL Server 2005,2008,2012

 

 Keywords : DML Triggers Example, Sql Server Triggers Example pdf, Instead of Trigger Example, After Trigger Example

Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers.

Types of DML 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.

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/delet 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.

Example

 -- First create table Employee_Demo
CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);
Insert into Employee_Demo values ('Riyaz',1400);
--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime
) 

Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example

1. After Insert Trigger

   
    -- Create trigger on table Employee_Demo for Insert statement
   CREATE TRIGGER trgAfterInsert on Employee_Demo
   FOR INSERT
   AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @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_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values (@empid,@empname,@empsal,@audit_action,getdate());
   PRINT 'AFTER INSERT trigger fired.'
   --Output will be 
   
   

   

   
    --Now try to insert data in Employee_Demo table
   insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
   --Output will be 
   
   

   

   
    --now select data from both the tables to see trigger action
   select * from Employee_Demo
   select * from Employee_Demo_Audit
   --Output will be 
   
   

   
   Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.

2. After Update Trigger

   
    -- Create trigger on table Employee_Demo for Update statement
   CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
   FOR UPDATE
   AS
   declare @empid int, @empname varchar(55), @empsal decimal(10,2), @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='Update Record --- After Update Trigger.';
   if update (Emp_Sal)
    set @audit_action='Update Record --- After Update Trigger.';
   insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values (@empid,@empname,@empsal,@audit_action,getdate());
   PRINT 'AFTER UPDATE trigger fired.'
   --Output will be 
   
   

   

   
    --Now try to upadte data in Employee_Demo table
   update Employee_Demo set Emp_Name='Pawan' Where Emp_ID =6;
   --Output will be 
   
   

   

   
    --now select data from both the tables to see trigger action
   select * from Employee_Demo
   select * from Employee_Demo_Audit
   --Output will be 
   
   

   
   Trigger have inserted the new record to Employee_Demo_Audit table for update statement. In this way we can trace a update activity on a table using trigger.

3. After Delete Trigger

   
    -- Create trigger on table Employee_Demo for Delete statement
   CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
   FOR DELETE
   AS
   declare @empid int, @empname varchar(55), @empsal decimal(10,2), @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;
   select @audit_action='Deleted -- After Delete Trigger.';
   insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values (@empid,@empname,@empsal,@audit_action,getdate());
   PRINT 'AFTER DELETE TRIGGER fired.'
   --Output will be 
   
   

   

   
    --Now try to delete data in Employee_Demo table
   DELETE FROM Employee_Demo where emp_id = 5
   --Output will be 
   
   

   

   
    --now select data from both the tables to see trigger action
   select * from Employee_Demo
   select * from Employee_Demo_Audit
   --Output will be 
   
   

   
   Trigger have inserted the new record to Employee_Demo_Audit table for delete statement. In this way we can trace a delete activity on a table using trigger.

Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example

1. Instead of Insert Trigger

   
    -- Create trigger on table Employee_Demo for Insert statement
   CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
   INSTEAD OF Insert
   AS
   declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
   select @emp_id=i.Emp_ID from inserted i;
   select @emp_name=i.Emp_Name from inserted i;
   select @emp_sal=i.Emp_Sal from inserted i;
   SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
   BEGIN 
    BEGIN TRAN
    SET NOCOUNT ON
    if(@emp_sal>=1000)
    begin
    RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
    else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal); Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
    COMMIT;
    PRINT 'Record Inserted -- Instead Of Insert Trigger.'
   END
   --Output will be 
   
   

   

   
    --Now try to insert data in Employee_Demo table
   insert into Employee_Demo values ('Shailu',1300)
   insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are checking salary >=1000
   --Outputs will be 
   
   

    

   
    --now select data from both the tables to see trigger action
   select * from Employee_Demo
   select * from Employee_Demo_Audit
   --Output will be 
   
   

   
   Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can apply business validation on the data to be inserted using Instead of trigger and can also trace a insert activity on a table.

2. Instead of Update Trigger

   
    -- Create trigger on table Employee_Demo for Update statement
   CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
   INSTEAD OF Update
   AS
   declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
   select @emp_id=i.Emp_ID from inserted i;
   select @emp_name=i.Emp_Name from inserted i;
   select @emp_sal=i.Emp_Sal from inserted i;
   BEGIN
    BEGIN TRAN
   if(@emp_sal>=1000)
    begin
    RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
    else begin 
    insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
    COMMIT;
    PRINT 'Record Updated -- Instead Of Update Trigger.'; END
   --Output will be 
   
   

   

   
    --Now try to upadte data in Employee_Demo table
   update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
   update Employee_Demo set Emp_Sal = '900' where emp_id = 6
   --Output will be 
   
   

    

   
    --now select data from both the tables to see trigger action
   select * from Employee_Demo
   select * from Employee_Demo_Audit
   --Output will be 
   
   

   
   Trigger have inserted the updated record to Employee_Demo_Audit table for update statement. In this way we can apply business validation on the data to be updated using Instead of trigger and can also trace a update activity on a table.

3. Instead of Delete Trigger

   
    -- Create trigger on table Employee_Demo for Delete statement
   CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
   INSTEAD OF DELETE
   AS
   declare @empid int, @empname varchar(55), @empsal decimal(10,2), @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;
   BEGIN TRAN if(@empsal>1200) begin
    RAISERROR('Cannot delete where salary > 1200',16,1);
    ROLLBACK;
    end
    else begin
    delete from Employee_Demo where Emp_ID=@empid;
    COMMIT;
    insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
    values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());
    PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END
   --Output will be 
   
   

   

   
    --Now try to delete data in Employee_Demo table
   DELETE FROM Employee_Demo where emp_id = 1
   DELETE FROM Employee_Demo where emp_id = 3
   --Output will be 
   
   

    

   
    --now select data from both the tables to see trigger action
   select * from Employee_Demo
   select * from Employee_Demo_Audit
   --Output will be 
   
   

   
   Trigger have inserted the deleted record to Employee_Demo_Audit table for delete statement. In this way we can apply business validation on the data to be deleted using Instead of trigger and can also trace a delete activity on a table.

What do you think?

In this article I try to explain the After Trigger and Instead of Trigger with example. I hope after reading this article your sql triggers concepts will be strong. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

kudvenkat Venkateswarulu Kudithipudi

http://csharp-video-tutorials.blogspot.in/2014/09/how-to-become-aspnet-developer.html


https://plus.google.com/114006551575892980555/posts

http://csharp-video-tutorials.blogspot.in/

REgex split join within brackets

            string s1 = "Who [kavas,kar] the,se [kav,as] notes?";
            var myRegex = new Regex(@"\[[^\]]*\]|(,)");
            var group1Caps = new StringCollection();

            string replaced = myRegex.Replace(s1, delegate(Match m)
            {
                if (m.Groups[1].Value == "") return m.Groups[0].Value;
                else return "SPLIT_HERE";
            });

            string[] splits = Regex.Split(replaced, "SPLIT_HERE");
            ArrayList list = new ArrayList();
            foreach (string split in splits)
            {
                list.Add(split.Replace(",", "."));
            }

            string[] array = list.ToArray(typeof(string)) as string[];

            Console.WriteLine(string.Join(",", array));

            Console.WriteLine("\nPress Any Key to Exit.");
            Console.ReadKey();

AngularJS With MVC Web API (ASP.NET MVC RESTful Service)

I. Introduction

In this article, I am going to explain how AngularJS works in the client side and how AngularJS can be integrated with Web API (ASP.NET MVC RESTFul Service) for CRUD operation. I have taken example of product list and add new product.

If we need to learn any new language, then what are all things that will come to mind:

• How we can do the validation?
• How we can execute the server side code and get the response back?
• How HTML form data posts to the server side code to perform the database activities?
• How to get the data from database, when page loads to display the data?
• How to implement the session management?
• How to implement security?

I hope when you will read the complete article, you will be able to understand how AngularJS works and you will have clarifications to some of the above questions. This blog will get you to start working on AngularJS with Web API.

II. What is AngularJS

AngularJS is a JavaScript framework. Using this, we can build rich and extensible web applications. This is completely built on HTML, Javascript and CSS, and will not having any other dependency to make it work.

Anugular is very close to MVC and MVVM but Angular has been declared as MVW pattern where "W" stand for "Whatever" (whatever works for you).

Basically, Angular has three components as shown below:

• Template (View)
• Scope (Model)
• Controller

Template(View): Template is the HTML part of the application. If you have to work with ASP.NET MVC, then it is same as cshtml or any other type of the view. In Angular JS, there will be no any server side view code. Beauty of this framework is without refresh of the complete page, we can inject the data to the view (add, modify and delete the HTML data without refreshing the page).

Scope(Model): Scope is the model object of the application at client side.

Controller: Controller is a function which actually contains the logical unit of the application. This will have two parameters, one is the controller name (this is empty scope object) and the other is array in this fields can be added. Controller will be exposed to the view.

III. Why AngularJS (Advantages of AngularJS)

1. Angular does not depend on any server side technologies, this builds on pure HTML, Javascript and CSS.
2. This is a very light weight application.
3. This works on the SPA (Single Page Application) principle.
4. AS in Angular components are separated, so the application can be highly testable. This support TDD development.
5. Less development effort, in any project HTML developers are different and they develop HTML pages and provide to the development team, developer then converts all the HTML files to cshtml or ASPX or any other technologies and continues working. In this case, double effort is required for development and testing.

In AngularJS, development team can continue from the HTML page itself.

IV. AngularJS Components

1. Module
2. Directives
3. Filters
4. Expression

V. AngularJS with Server Side code(MVC Web API)

The worry for any developer is that if Angular works with only HTML, then how can server side code be executed.Angular facilitates much more flexibility on this, any server side code can be plucked through Ajax call. So in this case, separate component can be designed to invoke by Angular. API can be written in .NET, PHP, Java or any other technologies.

In this article, I am taking an example of Web API (ASP.NET MVC Web API).

Below is the architecture design for the server side code execution.

Angular will have controller which will make an Ajax call to the web API and get the response back from the server after executing the code.

VI. Develop MVC Web API

Let’s start creating a new Web API with Visual Studio 2013.

Step 1

Open Visual Studio 2013->Select new project-->Web-->ASP.NET Web Application.

Step 2

Select Web API:

Step 3

Right click on controller folder and add new controller class file.

Step 4

Select Controller and MVC 5 Controller with read/write action:

Step 5

Rename the controller to “ProductController”.

Step 6

API controller class will be like below:

Step 7

In Web API, I have two functions, one to get the product list data and the other posts the product data from product add page of Angular and adds to the product list.

Step 7.1: GetAllProducts

This is the get RESTful service method and this will return all the product data.

Controller Constructor: This is just used to fill initial product data:

Hide   Copy Code

public ProductController()
        {
            if (PgaeLoadFlag == 1) //use this only for first time page load
            {
                //Three product added to display the data
                products.Value.Add(new Product { ID = 1, Name = "bus", Category = "Toy", Price = 200.12M });
                products.Value.Add(new Product { ID = 2, Name = "Car", Category = "Toy", Price = 300 });
                products.Value.Add(new Product { ID = 3, Name = "robot", Category = "Toy", Price = 3000 });
                PgaeLoadFlag++;
            }
        }

This will give you result by running the URL “http://localhost:1110/api/product”.

Hide   Copy Code

 // GET api/product
        public List <product> GetAllProducts() //get method
        {
            //Instedd of static variable you can use database resource to get the data and return to API
            return products.Value; //return all the product list data
        }

The output of the method is as shown below:

Step 7.2: ProductAdd

This is the post RESTful service method and this will save the product data which will post from Angular form or any other application. This method will receive the product data but in this blog I have taken for Angular page form.

Hide   Copy Code

 public void ProductAdd(Product product) //post method
        {
            product.ID = ProductID;
            products.Value.Add(product); //add the post product data to the product list
            ProductID++;
            //instead of adding product data to the static product list you can save data to the database.
        }

VII. Develop AngularJS Application and Integrate with Web API

1. Solution Structure

There are two projects in the solution structure, one for Web API and the other for Pure Angular application.

2. Product List Page

In this page, I am getting the list of product data from web API and looping through the data to display in the page.

In the below snapshot, explain how module, controller, web API, model and data loop through integration.

First run the “Product_Api”, then run the “Product_Web” application.

Run the URL to access the product list page http://localhost:3442/Productlist.html. Once you run this, it will execute the method “GetAllProducts”.

After method executes and integrates with Angular page, this will display the page as below:

3. Add new Product

In this page, I have an HTML form to enter new product details and the entered data post to the Web API post method.

Please have a close look at the below snapshot to understand how you can post the form data to the Web API method.

First run the “Product_Api”, then run the “Product_Web” application.

Run the URL to access the product. Add new page http://localhost:3442/ProductAdd.html.

Once user client clicks on submit, this will execute the Web API method “ProductAdd” by passing the product data and product data gets added in the product list.

Now once rerun the product list page, you will find the “cycle” product in the product list.

I have attached the complete source code; you can download it from the link at the top of the article and enjoy with Angular. 

Note: For demo purposes, the script code is in the same page, when you design a real time application, then you can organize your module properly in a separate JS file.

remove return consecutive word by REGEX

string pattern = @"\b(\w+)\s\1\b";
            string substitution = "$+";
            string input = "The the dog dog jumped jumped over the fence fence.";
            MessageBox.Show(Regex.Replace(input, pattern, substitution,
                              RegexOptions.IgnoreCase));

https://msdn.microsoft.com/en-us/library/ewy2t5e0(v=vs.110).aspx