TRIGGERS IN SQL SERVER

INTRODUCTION

TRIGGERS IN SQL SERVER

BACKGROUND

This article gives a brief introduction about Triggers in Sql Server 2000/2005.

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. 

 Collapse | Copy Code

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:-

 Collapse | Copy Code

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:-

 Collapse | Copy Code

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 : 

 Collapse | Copy Code

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:-

 Collapse | Copy Code

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:-

 Collapse | Copy Code

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. 

 Collapse | Copy Code

update Employee_Test set Emp_Sal=1550 where Emp_ID=6

This inserts the row into the audit table as:- 

 Collapse | Copy Code

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:- 

 Collapse | Copy Code

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:- 

 Collapse | Copy Code

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 

 Collapse | Copy Code

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL 

Specific Triggers can be enabled or disabled as :- 

 Collapse | Copy Code

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 anInstead 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:-

 Collapse | Copy Code

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:- 

 Collapse | Copy Code

delete from Employee_Test where Emp_ID=4

This will print an error message as defined in the RAISE ERROR statement as:- 

 Collapse | Copy Code

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.

query from different database

sp_addlinkedserver [mycom-devp]
EXEC sp_addlinkedsrvlogin [mycom-devp], 'false', NULL, 'sa', '123456'

select  * from [mycom-devp].restdb_ff.dbo.bill_tran

Windows Service

As a matter of fact Microsoft Windows services, formerly known as NT services enable you to create long-running executable applications that run in its own Windows session, which then has the ability to start automatically when the computer boots and also can be manually paused, stopped or even restarted.

This makes services ideal for use on a server or whenever you need long-running functionality that does not interfere with other users who are working on the same computer. You can also run services in the security context of a specific user account that is different from the logged-on user or the default computer account.

Windows services don’t have any interface to the user, so it can not be debugged like any regular application, but it’s debugged as a process. .NET has a very nice tool that enables processes debugging while it’s in the run status, by easily pressing Ctrl + Alt + P shortcut.

Background

I’ve searched well so many sites about a code that I can with the help of it, build a simple Windows service, but I found a lot of code on how to manage the current Windows services of the system and that’s through theServiceController class.

After searching the MSDN, I’ve found some nice code that helped me to create this simple Windows service. Hope it can help as a basic architecture for and usage of such a Windows service.

Using the code

At first you should simply open VS.NET and then at the File menu click on New, Project. From the New Project Dialog Box, choose the Windows service template project and name it MyNewService like shown below:

The project template automatically adds a component class that is called Service1 by default and inherits fromSystem.ServiceProcess.ServiceBase.

Click the designer. Then, in the Properties window, set the ServiceName property for Service1 toMyNewService.

Set the Name property to MyNewService. Set the AutoLog property to true.

In the code editor, edit the Main method to create an instance of MyNewService. When you renamed the service in step 3, the class name was not modified in the Main method. To access the Main method in VC#, expand the Component Designer generated code region.

 Collapse | Copy Code

static void Main()
{ 
    System.ServiceProcess.ServiceBase[] ServicesToRun; 
    //Change the following line to match. 
    ServicesToRun = new 
        System.ServiceProcess.ServiceBase[] { new MyNewService() }; 
    System.ServiceProcess.ServiceBase.Run(ServicesToRun);  
}

In the next section, you will add a custom event log to your Windows service. Event logs are not associated in any way with Windows services. Here the EventLog component is used as an example of the type of components you could add to a Windows service.

To add custom event log functionality to your service:

1. In the Solution Explorer, right-click Service1.vb or Service1.cs and select View Designer.
2. From the Components tab of the Toolbox, drag an EventLog component to the designer.
3. In the Solution Explorer, right-click Service1.vb or Service1.cs and select View Code.
4. Edit the constructor to define a custom event log.

To access the constructor in Visual C#, expand the Component Designer generated code region.

 Collapse | Copy Code

public MyNewService()
{
 
    InitializeComponent()
    if(!System.Diagnostics.EventLog.SourceExists("DoDyLogSourse"))
    System.Diagnostics.EventLog.CreateEventSource("DoDyLogSourse",
                                                          "DoDyLog");

    eventLog1.Source = "DoDyLogSourse";
    // the event log source by which 

    //the application is registered on the computer

    eventLog1.Log = "DoDyLog";
}

To define what happens when the service starts, in the code editor, locate the OnStart method that was automatically overridden when you created the project, and write code to determine what occurs when the service begins running:

 Collapse | Copy Code

protected override void OnStart(string[] args)
{ 
    eventLog1.WriteEntry("my service started"); 
}

The OnStart method must return to the operating system once the service's operation has begun. It must not loop forever or block. To set up a simple polling mechanism, you can use the System.Timers.Timer component. In theOnStart method, you would set parameters on the component, and then you would set the Timer.Enabledproperty to true. The timer would then raise events in your code periodically, at which time your service could do its monitoring.

To define what happens when the service is stopped, in the code editor, locate the OnStop procedure that was automatically overridden when you created the project, and write code to determine what occurs when the service is stopped:

 Collapse | Copy Code

protected override void OnStop()
{ 
    eventLog1.WriteEntry("my service stoped");
}

You can also override the OnPause, OnContinue, and OnShutdown methods to define further processing for your component. For the method you want to handle, override the appropriate method and define what you want to occur. The following code shows what it looks like if you override the OnContinue method:

 Collapse | Copy Code

protected override void OnContinue()
{
    eventLog1.WriteEntry("my service is continuing in working");
}

Some custom actions need to occur when installing a Windows service, which can be done by the Installer class. Visual Studio can create these installers specifically for a Windows service and add them to your project. To create the installers for your service.

1. Return to design view for Service1.
2. Click the background of the designer to select the service itself, rather than any of its contents.
3. In the Properties window, click the Add Installer link in the gray area beneath the list of properties. By default, a component class containing two installers is added to your project. The component is namedProjectInstaller, and the installers it contains are the installer for your service and the installer for the service's associated process.
4. Access design view for ProjectInstaller, and click ServiceInstaller1.
5. In the Properties window, set the ServiceName property to MyNewService.
6. Set the StartType property to Automatic.

Tip

To avoid being asked about the system username and password you must change the Account for theserviceProcessInstaller to LocalSystem. This is done by opening the ProjectInstaller design and then selecting the serviceProcessInstaller, press F4 and then change the Account property to LocalSystem. Or you can manually do that by creating a class that inherits from System.Configuration.Install.Installerlike this:

 Collapse | Copy Code

[RunInstaller(true)]

public class ProjectInstaller : System.Configuration.Install.Installer 
private System.ServiceProcess.ServiceProcessInstaller 
                                  serviceProcessInstaller1;
private System.ServiceProcess.ServiceInstaller serviceInstaller1; 
/// <summary> 
/// Required designer variable. 
/// </summary> private System.ComponentModel.Container components = null;

public ProjectInstaller()
   // This call is required by the Designer.
   InitializeComponent();

   // TODO: Add any initialization after the InitComponent call 
} 
private void InitializeComponent() 
{ 
   this.serviceProcessInstaller1 = 
     new System.ServiceProcess.ServiceProcessInstaller(); 
   this.serviceInstaller1 = 
     new System.ServiceProcess.ServiceInstaller(); 
   // serviceProcessInstaller1 
   // 
   this.serviceProcessInstaller1.Account = 
     System.ServiceProcess.ServiceAccount.LocalSystem; 
   this.serviceProcessInstaller1.Password = null;
   this.serviceProcessInstaller1.Username = null; 
   // 
   // serviceInstaller1 
   // 
   this.serviceInstaller1.ServiceName = "MyNewService"; 
   this.serviceInstaller1.StartType = 
     System.ServiceProcess.ServiceStartMode.Automatic;

   // 
   // ProjectInstaller 
   // 
   this.Installers.AddRange
     (new System.Configuration.Install.Installer[] 
   { 
       this.serviceInstaller1, 
       this.serviceInstaller1});
   }
}

To build your service project

1. In Solution Explorer, right-click your project and select Properties from the shortcut menu. The project'sProperty Pages dialog box appears.
2. In the left pane, select the General tab in the Common Properties folder.
3. From the Startup object list, choose MyNewService. Click OK.
4. Press Ctrl+Shift+B to build the project. 

Now that the project is built, it can be deployed. A setup project will install the compiled project files and run the installers needed to run the Windows service. To create a complete setup project, you will need to add the project output, MyNewService.exe, to the setup project and then add a custom action to have MyNewService.exe installed.

To create a setup project for your service

1. On the File menu, point to Add Project, and then choose New Project.
2. In the Project Types pane, select the Setup and Deployment Projects folder.
3. In the Templates pane, select Setup Project. Name the project MyServiceSetup.

A setup project is added to the solution. Next you will add the output from the Windows service project,MyNewService.exe, to the setup.

To add MyNewService.exe to the setup project

1. In Solution Explorer, right-click MyServiceSetup, point to Add, then choose Project Output. The Add Project Output Group dialog box appears.
2. MyNewService is selected in the Project box.
3. From the list box, select Primary Output, and click OK.
   A project item for the primary output of MyNewService is added to the setup project. Now add a custom action to install the MyNewService.exe file.

To add a custom action to the setup project

1. In Solution Explorer, right-click the setup project, point to View, then choose Custom Actions. The Custom Actions editor appears.
2. In the Custom Actions editor, right-click the Custom Actions node and choose Add Custom Action. The Select Item in Project dialog box appears.
3. Double-click the application folder in the list box to open it, select primary output from MyNewService(Active), and click OK. The primary output is added to all four nodes of the custom actions — Install, Commit, Rollback, and Uninstall.
4. Build the setup project.

To install the Windows Service

Browse to the directory where the setup project was saved, and run the .msi file to install MyNewService.exe.

To start and stop your service

1. Open the Services Control Manager by doing one of the following:
   • In Windows 2000 Professional, right-click My Computer on the desktop, then click Manage. In theComputer Management console, expand the Services and Applications node.
     - Or -
   • In Windows 2000 Server, click Start, point to Programs, click Administrative Tools, and then clickServices.
     Note: In Windows NT version 4.0, you can open this dialog box from Control Panel.
2. You should now see MyNewService listed in the Services section of the window.
3. Select your service in the list, right-click it, and then click Start.

Right-click the service, and then click Stop.

To verify the event log output of your service

1. Open Server Explorer and access the Event Logs node. For more information, see Working with Event Logs in Server Explorer.
   Note: The Servers node of Server Explorer is not available in the Standard Edition of Visual Basic and Visual C# .NET.
   

To uninstall your service

• On the Start menu, open Control Panel and click Add/Remove Programs, and then locate your service and clickUninstall.
• You can also uninstall the program by right-clicking the program icon for the .msi file and selecting Uninstall.

XBAP - This application type has been disabled

http://stackoverflow.com/questions/5877586/xbap-this-a

up vote11down vote

Or you can enable XBAP applications in IE9. Security -> .NET Framework -> XAML browser applications Disable Enable Prompt

pplication-type-has-been-disabled

Eight Different Ways to Transfer Data from One Page to Another Page

1. Use the querystring:

protected void QueryStringButton_Click(object sender, EventArgs e)
         {
             Response.Redirect("QueryStringPage.aspx?Data=" + Server.UrlEncode(DataToSendTextBox.Text));
        }

2. Use HTTP POST:

<asp:Button ID="HttpPostButton" runat="server" Text="Use HttpPost"
             PostBackUrl="~/HttpPostPage.aspx" onclick="HttpPostButton_Click"/>

protected void HttpPostButton_Click(object sender, EventArgs e)
        {
     // The PostBackUrl property of the Button takes care of where to send it!
        }

  3. Use Session State:

   protected void SessionStateButton_Click(object sender, EventArgs e)
         {
             Session["Data"] = DataToSendTextBox.Text;
             Response.Redirect("SessionStatePage.aspx");
        }

  4.  Use public properties:

   public string DataToSend
        {
            get
            {
                 return DataToSendTextBox.Text;
             }
        }

        protected void PublicPropertiesButton_Click(object sender, EventArgs e)
         {
             Server.Transfer("PublicPropertiesPage.aspx");
        }

5. Use PreviousPage Control Info:

protected void ControlInfoButton_Click(object sender, EventArgs e)
         {
             Server.Transfer("ControlInfoPage.aspx");
        }

  // target page:
protected void Page_Load(object sender, EventArgs e)
        {
            var textbox = PreviousPage.FindControl("DataToSendTextbox") as TextBox;
             if (textbox != null)
            {
                DataReceivedLabel.Text = textbox.Text;
            }
        }

6. Use HttpContext Items Collection:

  protected void HttpContextButton_Click(object sender, EventArgs e)
         {
             HttpContext.Current.Items["data"] = DataToSendTextBox.Text;
             Server.Transfer("HttpContextItemsPage.aspx");
        }

// target page:
protected void Page_Load(object sender, EventArgs e)
         {
             this.DataReceivedLabel.Text =(String) HttpContext.Current.Items["data"];
        }

7. Use Cookies:

protected void CookiesButton_Click(object sender, EventArgs e)
        {
            HttpCookie cook =  new HttpCookie("data");
            cook.Expires = DateTime.Now.AddDays(1);
            cook.Value = DataToSendTextBox.Text;
             Response.Cookies.Add(cook);
             Response.Redirect("HttpCookiePage.aspx");
        }

// target page:
protected void Page_Load(object sender, EventArgs e)
        {
            DataReceivedLabel.Text = Request.Cookies["data"].Value;
        }

8. Use Cache:

protected void CacheButton_Click(object sender, EventArgs e)
         {
             Cache["data"] = DataToSendTextBox.Text;
             Server.Transfer("CachePage.aspx");
        }
   // target page:
    protected void Page_Load(object sender, EventArgs e)
         {
             this.DataReceivedLabel.Text = (string) Cache["data"];
        }

Querying from Dataset

  var qry = from packing in sp_ds.Tables[8].AsEnumerable()
                          where packing.Field<string>("pack_id") == global.prod_mastList[0].Last_pack_id.Trim()
                          select packing;
                if (qry.Count() >= 1)
                {
                    DataTable dt = qry.CopyToDataTable();
                    txtlast_Packing.Text = dt.Rows[0][2].ToString();
                }