Archive

Posts Tagged ‘UPDATE’

Using Built-in system functions – UPDATE()

02.06.2011 2 comments

UPDATE() function can be used to determine whether an INSERT or UPDATE was made on the specified column.

You can use it inside a trigger to test if a column has been updated by INSERT/UPDATE statement.

for example, I have below table:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE SqlAndMe

 

CREATE TABLE dbo.Salary

(

      ID          INT,

      Name        VARCHAR(50),

      Salary      MONEY

)

And I have also created on the table for an update as below:

CREATE TRIGGER Updated_Salary

ON dbo.Salary

AFTER UPDATE

AS

IF ( UPDATE (Salary) )

BEGIN

ROLLBACK

END

 

INSERT INTO dbo.Salary VALUES (1, 'Vishal', 1000)

Now if someone tries to update the Salary column, a message will be displayed as below:

UPDATE      dbo.Salary SET Salary = 2000

WHERE       ID = 1

Result Set:

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

However, other columns can be updated without any issues.

UPDATE      dbo.Salary SET Name = 'Gajjar'

WHERE       ID = 1

Result Set:

 

(1 row(s) affected)

Hope This Helps!

Vishal

TSQL – Common Table Expressions (CTE) – Part 0 – Introduction

13.04.2011 No comments

What is it? A CTE Creates a temporary named result set. It is available within the execution of a single DML statement. A Common Table Expression can be used to:

1. Create Recursive Query
2. Create a temporary view, to simplify another query
3. To avoid “Msg 164, Level 15, State 1” – group by a derived column / non deterministic
   
function
4. And, Use the result set returned multiple time in the same statement.

Let’s see how to create one,
I have created two tables for this example – FirstNames, LastNames as below:

Data in FirstNames and LastNames :
image image

To combine the firstname and lastname in output, we can use INNER JOIN:

SELECT FN.EmpID, FN.FirstName, LN.LastName
FROM dbo.FirstNames FN
INNER JOIN dbo.LastNames LN ON LN.EmpID = FN.EmpID

To write the same using CTE, let’s look at the syntax first:

WITH TempResultSetName(Column1, Column2, …)
AS
( 
      SELECT Query
)
SELECT Column1, Column2, FROM TempResultSetName

The “WITH TempResultSetName” defines a name for the result set, and the Column List defines the column names for the result set – this is optional, if column names are not specified it will used the names from the “SELECT Query”.

The “SELECT Query” is the actual data for the results. i.e. which returns actual rows.

The Final part of CTE is the “SELECT Column1, ….. FROM TempResultSetName” – this simply outputs the result set to user. you are not limited to only SELECT here, any valid DML can be used.

However, you can only use the TempResultSet in FROM clause of a SELECT statement, i.e. you can not INSERT INTO or UPDATE or DELETE to/from TempResultSet.

Now, let’s write the actual CTE to replace INNER JOIN:

WITH TempResultSetName(EmpID, FirstName, LastName)
AS
( 
      SELECT FN.EmpID, FN.FirstName, LN.LastName
      FROM dbo.FirstNames FN
      INNER JOIN dbo.LastNames LN ON LN.EmpID = FN.EmpID
)     SELECT EmpID, FirstName, LastName
      FROM TempResultSetName

Line 1 – Defines the Result Set and Columns in Result Set
Line 4 to 6 – Populates the Result Set
Line 8 & 9 – Outputs the Result Set – The output can also include other tables, I have added examples in the sample script.

Download the scripts used in post here.

Hope This Helps!

Vishal