Ubuntu…

•September 12, 2010 • Leave a Comment

Two weeks ago my computer running windows 7 blue screened 3 times within an hour. I then decided to reformat my computer by re-installing the OS.  I had most of my files and music backed up on a USB drive.  So I begin the daunting task of reinstalling windows and the DVD had some problems with it and it wouldn’t work.  So I decided to give UBUNTU a try. This is a FREE operating system based on Linux. It is fast and you are able to customize add on’s to create an operating system like no other. The only downside is that some windows programs will not run…the only one that I need is Itunes to manage my Iphone and Ipod. I ended up using Oracle Virtual Box running windows 7 with Itunes and other windows software installed… If it crashes again i will be able to restore from a backup of the image.  After completing all of this it was time to restore my files from my backup USB drive…I had not seen my drive in a few days but that night Morgan pulled it out of the dryer and it was destroyed.  This time around i will create a backup of the backup’s backup.

Implementing Triggers…Part 1

•December 19, 2009 • Leave a Comment

A tigger is an implementation of code that runs in response to an event that takes place in a database. There are two types of triggers:  Data Manipulation Language (DML), which run when INSERT, UPDATE, or DELETE statements modify data in a specified table or view. Data Definition Language (DDL), which run in response to DDL events on the server such as creating, altering, or dropping an object, are using for database administration tasks such as auditing, and controlling object access. Part 1 of Implementing triggers will focus on using DML’s in SQL Server.   

Data Manipulation Language (DML) — Unlike SP’s and Functions DML Triggers are not stand-alone objects, and you cannot directly execute them. A DML trigger is attached to a specific table or view and defined for a particular event. The events that can cause a DML Trigger to fire are Insert, Update, and Delete. Triggers can fire in two different modes:  AFTER and INSTEAD OF. In AFTER mode the trigger will be fired after the event that started the trigger is commpleted. In INSTEAD OF mode the trigger will be fired instead of the event’s code. The most common use of a INSTEAD OF trigger is to update the base tables through a view. You can only define one INSTEAD OF trigger for each INSERT, UPDATE, or DELETE event for a view or table. 

The code within a a trigger can have any statements and constructs valid for a batch except for the following: 

Database cannot be created, altered, dropped, backed up, or restored. 

Structural changes cannot be made to the table that cause the trigger to fire such as CREATE/ALTER/DROPINDEX,  ALTER/DROPTABLE, etc… 

Triggers have access to two special tables that are dynamically generated, the INSERTED and DELETED tables. The structure of the the INSERTED and DELETED tables excactly matches the column definition of the table on which the trigger was created. This means that you can reference columns by using the same name as the table for whith the trigger was defined. When you execute an INSERT operation, the INSERTED table contains each row that was inserted into the table, whereas the DELETED table does not contain any rows. When you execute a DELETE statement, the DELETED table contains each row that was deleted from the table, and the INSERT table doesn’t contain any rows. When you execute and update statement the INSERTED table contains the after image of each row you updated. 

Some syntax rules for a DML are below: 

  •  Every trigger must have a name that conforms to the rules for object identifiers.
  •   You use the ON clause to specify the table or view that the trigger will be created against.
  •   Using the WITH clause you can specify whether the code will be encrypted when executing and specify an  execution context.
  •  The FOR clause specifies whether or not the trigger is an AFTER or INSTEAD OF as well as events that cause the trigger to fire.
  •  Following the AS clause you specify the code that you want to execute when the trigger is fired.

Now with that said lets look at and example:  HR as a strict policy that requires any changes to an employees pay rate to be audited. The audit must include the following:  

  •  Prior Pay Rate
  • Date the change was made
  • Current Pay Rate
  • Name of the person who made the change

Here is and example code of how to achieve this:


CREATE TRIGGER tu_employeepayaudit
ON dbo.Employee
FOR UPDATE
AS

DECLARE @now DATETIME

SET @now = GETDATE()

BEGIN TRY
 INSERT INTO dbo.EmployeeAudit
 (RowImage, PayRate, ChangeDate, ChangeUser)
 SELECT ‘BEFORE’, DELETED.PayRate, @now, suser_sname()
 FROM DELETED

 INSERT INTO dbo.EmployeeAudit
 (RowImage, PayRate, ChangeDate, ChangeUser)
 SELECT ‘AFTER’, DELETED.PayRate, @now, suser_sname()
 FROM INSERTED
END TRY
BEGIN CATCH
 –You can place some error handling code here
 ROLLBACK TRANSACTION
END CATCH

 

Implementing Stored Procedures

•December 17, 2009 • Leave a Comment

Stored Procedures are the most-used programmatic structures within a database. When you think of a procedure just think of a batch of code that is given a name, when that name is called the batch of code will be executed.

S.P.’s can return scalar values or actual result sets and are the primary interface that applications should use to access any data within a database. S.P.’s you the ability to control access to a database as well as gives you an easy way to maintain your code if changes need to be made to an application in the future.

Each procedure must have a unique name within the database. You can specifiy input paramaters and output parameters for the procedure. Inputs will be used as local variables within the procedure, and Outputs will be passed back to the routine that called the procedure.

Applying security within SQL server using stored procedures provides a very powerful secuirty mechanism. Any user granted execute permissions on a stored procedure is automatically delegated permissions to the object and commands referenced inside the stored procedure based on the permissoin set of the user who created the stored procedure.  If all data access (insertions, deletions, updates, or selects) were performed through stored procedures, users could not directly access any table in the database. Only by executing stored procedures would users be able to manage the database.

A Microsoft best practice is to use the minimum amount of code that also minimizes the amount of work performed by the system.

Now lets practice. The practice uses the AdventureWorks sample database which can be downloaded from the web.

We want to create a stored procedure to update the hire date of employees.

CREATE PROCEDURE dbo.usp_UpdateEmployeeHireDate
AS
DECLARE @now DATETIME

–Above creates a store procedure and declares a DATETIME value to be used within the procedure.

SET @now = GETDATE()

–Above sets the current date to the value.

UPDATE HumanResources.Employee
SET HireDate = @now

–Above updates the Employee tables hiredate to the value.

Chelsea vs. Portsmouth

•December 16, 2009 • Leave a Comment

 

Chelsea Beat Portsmouth in the FA Cup today.  Didnt get to watch this becuas I was at work but glad to see that we are back to our winning ways.

Implementing Functions…

•December 16, 2009 • Leave a Comment

Out of the box SQL Server provides built in functions that you can use throughout your code. One example is the GETDATE() Function. SQL Server also allows you to build your own functions allowing you to create and then use it accross multiple applications. These functions are normally called UDFs.

There are two types of UDFs, Scalar; which return a scalar value result. And table-valued functions, which return in the form of a table result

These two types can either be what  is called Deterministic or Nondeterministic. Deterministc meaning that everytime the function is used the same value will be returned. Nondeterministic is the exact opposite in that it will return a different value everyime it is called.

Now lets practice.  The practice uses the AdventureWorks sample database which can be downloaded from the web.

We want to create a scalar function to return the model name for a prodruct given a particular product ID.  Copy and paste the code into SSMS to test it out.

USE AdventureWorks

GO

CREATE FUNCTION dbo.GetModelNameForProduct (@ProductID int)
RETURNS nvarchar(50)
–Above Creates the function name and the specifies the input parameter
–as well as defining the output variable.
WITH EXECUTE AS CALLER
–Above Specifies the statements inside the module are executed in the
–context of the caller of the module.
AS
BEGIN
 DECLARE @ModelName nvarchar(50)

 SELECT @ModelName = Production.ProductModel.Name
 FROM Production.Product INNER JOIN Production.ProductModel
   ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID
 WHERE Production.Product.ProductID = @ProductID
 RETURN(@ModelName)
END;
–Above declares a variable “ModelName” which is used in the query to return the ProductID
–that is passed as the input when the function is called.
GO

SELECT dbo.GetModelNameForProduct(710);
–The above select statement will test the function out.  Run using different inputs to test.

Microsoft Exam 70-431…

•December 14, 2009 • Leave a Comment

Trying to become a

Microsoft Technical Specialist…

I have currently been studying to pass Microsoft Exam 70-431.  Microsoft SQL Server 2005 – Implementation and Maintenance is what the test covers.   However my motivation to really take a deep dive into the book has been slack. So I decided to study through the Microsoft training kit and post on my blog certain sections that interest me.

Stay tuned for some blog entries that include my study notes as well as links to helpful resources on the net.

The babies…

•December 14, 2009 • 2 Comments

Morgan

and

Roxy

So I am going to have a new family in about a month.  Morgan and I will be married and we have a lil girl named roxy.