SELECT 'item_code' AS item_code_count,
[001], [002]
FROM (SELECT loc_id, item_code, entry_no
FROM exp_tran) AS derivedtbl_1
PIVOT
(
count(item_code)
FOR loc_id IN ([001], [002])
) AS PivotTable
order by entry_no
IF OBJECT_ID('SalesHistory')>0 DROP TABLE SalesHistory; GO CREATE TABLE [dbo].[SalesHistory] ( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GO
CREATE TYPE SalesHistoryTableType AS TABLE ( [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GO
SELECT * FROM sys.table_types
CREATE PROCEDURE usp_InsertBigScreenProducts ( @TableVariable SalesHistoryTableType READONLY ) AS BEGIN INSERT INTO SalesHistory ( Product, SaleDate, SalePrice ) SELECT Product, SaleDate, SalePrice FROM @TableVariable WHERE Product = 'BigScreen' END GO
DECLARE @DataTable AS SalesHistoryTableType
DECLARE @i SMALLINT SET @i = 1 WHILE (@i <=1000) BEGIN INSERT INTO @DataTable(Product, SaleDate, SalePrice) VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57)) INSERT INTO @DataTable(Product, SaleDate, SalePrice) VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13)) INSERT INTO @DataTable(Product, SaleDate, SalePrice) VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29)) SET @i = @i + 1 END
EXECUTE usp_InsertBigScreenProducts @TableVariable = @DataTable
SELECT * FROM SalesHistory
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.
CREATE TABLE Employee_Test_Audit ( Emp_ID int, Emp_name varchar(100), Emp_Sal decimal (10,2), Audit_Action varchar(100), Audit_Timestamp datetime )
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.' GOThe 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.
insert into Employee_Test values('Chris',1500);
6 Chris 1500.00 Inserted Record -- After Insert Trigger. 2008-04-26 12:00:55.700
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.' GOThe 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.
update Employee_Test set Emp_Sal=1550 where Emp_ID=6This inserts the row into the audit table as:-
6 Chris 1550.00 Updated Record -- After Update Trigger. 2008-04-26 12:38:11.843
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.' GOIn this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the audit table.
6 Chris 1550.00 Deleted -- After Delete Trigger. 2008-04-26 12:52:13.867All the triggers can be enabled/disabled on the table using the statement
ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALLSpecific Triggers can be enabled or disabled as :-
ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete
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 GOThis 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.
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
ServiceController
class.Service1
by default and inherits fromSystem.ServiceProcess.ServiceBase
.ServiceName
property for Service1
toMyNewService
.Name
property to MyNewService
. Set the AutoLog
property to true
.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.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);
}
EventLog
component is used as an example of the type of components you could add to a Windows service.EventLog
component to the designer.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";
}
OnStart
method that was automatically overridden when you created the project, and write code to determine what occurs when the service begins running:protected override void OnStart(string[] args)
{
eventLog1.WriteEntry("my service started");
}
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.Enabled
property to true
. The timer would then raise events in your code periodically, at which time your service could do its monitoring.OnStop
procedure that was automatically overridden when you created the project, and write code to determine what occurs when the service is stopped:protected override void OnStop()
{
eventLog1.WriteEntry("my service stoped");
}
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:protected override void OnContinue()
{
eventLog1.WriteEntry("my service is continuing in working");
}
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.Service1
.ProjectInstaller
, and the installers it contains are the installer for your service and the installer for the service's associated process.ProjectInstaller
, and click ServiceInstaller1
.ServiceName
property to MyNewService
.StartType
property to Automatic
.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.Installer
like this:[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});
}
}
Startup object
list, choose MyNewService
. Click OK.MyServiceSetup
.MyServiceSetup
, point to Add, then choose Project Output. The Add Project Output Group dialog box appears.MyNewService
is selected in the Project box.MyNewService
is added to the setup project. Now add a custom action to install the MyNewService.exe file.MyNewService
(Active), and click OK. The primary output is added to all four nodes of the custom actions — Install, Commit, Rollback, and Uninstall.MyNewService
listed in the Services section of the window.