jump to navigation

How to force trigger order in SQL SERVER June 1, 2008

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , ,
2 comments

Often when we develop an application and design the database we enforce business logic with the use of triggers.

Sometimes the business logic dictates that we need to define two triggers on a table which must fire in a specific order on the same table action. For example when we insert rows in a table (INSERT statement) two triggers must fire and the second must fire after the first one, in order our logic to be implemented correctly.

Generally speaking multiple triggers on the same table for the same action are not fired in a predefined oreder. We can set the firing order for 2 AFTER triggers.We can do this by using the system stored procedure sp_settriggerorder. Do not try to do the same with INSTEAD OF triggers. You will receive an error.

I have taken the definition of sp_settriggerorder  from Books OnLine.

sp_settriggerorder [ @triggername = ] [ triggerschema. ] triggername
        , [ @order = ] value
        , [ @stmttype = ] statement_type
        [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ] 

A brief explanation of the arguments follows.

[ @triggername = ] [ triggerschema. ] triggername
It is the name of the trigger and the schema to which it belongs.
[ @order = ] value
Is the setting for the new order of the trigger. value is varchar(10) and it can be any one of the following values.
First – Trigger is fired first.
Last – Trigger is fired last.
None – Trigger is fired in undefined order.

[ @stmttype = ] ‘statement_type

Specifies the SQL statement that fires the trigger. statement_type is varchar(50) and can be INSERT, UPDATE, DELETE.

@namespace = { ‘DATABASE’ | ‘SERVER’ | NULL }

Specifies whether a DDL trigger was created on the database or on the server

In order to see an example, let’s create a table and set two INSERT triggers on that table that must fire in a specific order. Let’s call the table employee.

CREATE TABLE [dbo].[Employee](IDENTITY(1,1) NOT NULL

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

([EmployeeID] ASC))

Let’s create our triggers

Trigger number 1

create trigger dbo.tr_num_1 on dbo. employee

for insert

as
print ‘i am number 1′

go

 

Trigger number 2

create trigger dbo.tr_num_2 on dbo.employee

for insert

as

print ‘i am number 2′

go

when a new employee is inserted,tr_num_1 must be fired before tr_num_2.
we must insert some values to our table.

insert into employee default values
The triggers fire and we see that they fire in the desired order.
i am number 1
i am number 2
(1 row(s) affected)
Now if we drop the first trigger

DROP TRIGGER [dbo].[tr_num_1]
and create it again

create trigger [dbo].[tr_num_1] on [dbo].[Employee]
for insert
as
print ‘i am number 1′
GO

Now we try to insert a new row in the table.

insert into employee default values

i am number 2
i am number 1
(1 row(s) affected)

Now we have a problem. The second trigger fires first. Let’s fix that by using the sp_settriggerorder system stored procedure

exec sp_settriggerorder @triggername = ‘tr_num_1′,

@order=‘first’,

@stmttype= ‘insert’,
@namespace= null

exec sp_settriggerorder @triggername = ‘tr_num_2′,

@order= ‘last’,

@stmttype= ‘insert’,

@namespace = null

now if we enter a record in our table

insert into employee default values

we will see the following results

i am number 1
i am number 2
(1 row(s) affected)

The firing order is correct!!!!

Create a simple user defined function that checks if all characters in string are capital or not May 24, 2008

Posted by fofo in Sql Server, SQL Server 2008.
Tags: ,
1 comment so far

In this post i will try to give a simple example of how to create a simple User Defined Function. The purpose of this function is to check if all characters in a string are capitals or not. It should ignore characters like “#,!,@” e.t.c.

We must launch SQL Server 2005. and then just select a database e.g Northwind,Pubs.

In a new query window we type the function(check_capitals) written below. After the query executes successfully we can see the new function under “Programmability”->”Functions”->”Scalar-valued Functions”

I will try to explain what i am trying to do.

This function takes an argument that it is our string and then checks every letter of the string if it belongs to the 65-90 values of the ASCII table. These values(65-90) belong to the capital letters from capital “A” to capital “Z”. if all letter ascii values of the string fall within the range above, I return a value “T”=true, else even if only fails then i return “F”=false. The function should ignore any characters like “@,!,$”. To accomplish this task we use the built-in functions of SQL Server like ASCII,DATALENGTH,SUBSTRING.

to test the function just write the following sql

 

SELECT dbo.check_capitals(‘Nick’) -returns F

SELECT dbo.check_capitals(‘NICK’) -returns T

SELECT dbo.check_capitals(‘NIC!K’) -returns T

 

function check_capitals 

******************************************************************************************** 

CREATE FUNCTION check_capitals (@String VARCHAR(500))

  RETURNS char(1) AS

BEGIN 

DECLARE @returns char(1)

DECLARE @place TINYINT

SET @place =1

WHILE @place <= DATALENGTH(@string)

BEGIN

IF (ASCII(SUBSTRING(@string, @place, 1)) BETWEEN 65 AND 90) OR (ASCII(SUBSTRING(@string, @place, 1)) BETWEEN 32 AND 64)

SELECT @returns = ‘T’

ELSE

SELECT @returns = ‘F’

 

IF @Returns <> ‘F’  

SET @place = @place +1

ELSE

GOTO

Exitfunction

END

 

Exitfunction:

 RETURN

 @returns

 END

 ************************************************************* 

 

date and time data types -sql server 2008 March 10, 2008

Posted by fofo in SQL Server 2008.
Tags: ,
add a comment

in this post i am going to introduce the date and time data types that ship with sql server 2008. up to this point sql server had a datetime and smalldatetime data types to support date and time data. developers experienced many problems with those data types. sometimes we did not have enough precision or some other times we did have too much information.for example many times we had to convert the datetime to character string,strip the time component and then convert back to datetime.lets see some examples of the new data type

DECLARE @mybeautifulDate date

SELECT @mybeautifulDate = GETDATE()

PRINT @mybeautifulDate

execute the above statement

execute the following sql statement

DECLARE @Date dateSELECT @Date = ‘1977-02-17′

PRINT @Date

with the new date data type we only need 3 bytes of data to store our data since we do not store the time component.

lets have a look at the new time data type. in a new sql query window type and execute the following

DECLARE @MyTime time = GETDATE(),

@MyTime7 time(7) = GETDATE(),

@MyTime6 time(6) = GETDATE(),

@MyTime5  time(5) = GETDATE(),

@MyTime4 time(4) = GETDATE(),

@MyTime3 time(3) = GETDATE(),

@MyTime2 time(2) = GETDATE(),

@MyTime1 time(1) = GETDATE(),

@MyTime0 time(0) = GETDATE()

PRINT @MyTime

PRINT @MyTime7

PRINT @MyTime6

PRINT @MyTime5

PRINT @MyTime4

PRINT @MyTime3

PRINT @MyTime2

PRINT @MyTime1

PRINT @MyTime0

 the results shoule be like these

21:57:08.327000021:57:08.327000021:57:08.32700021:57:08.3270021:57:08.3270

21:57:08.327

21:57:08.33

21:57:08.3

21:57:08

the number inside the parenthesis indicate the precision we want to have for our data.it takes 3-5 bytes in the server to store time data depending on the precision we desire.

there is another date data type, namely datetime2. it provides us with more precision that we had with the existing datetime data types.

if we type this sql statement

DECLARE @datetime2 as DATETIME2(7) = GETDATE()

PRINT @datetime2

the result will be

2008-03-10 22:05:49.4200000we have a more precise datetime datatype.another new date data type is the datetimeoffset that has the same precision as datetime2 but stores the GMT offset as well.

DECLARE @datetimeoffset as DATETIMEOFFSET(7) = ‘1977-02-17 14:30:00 -09:00′

PRINT @datetimeoffset

if i execute this sql statement the result will be

1977-02-17 14:30:00.0000000 -09:00

it requires 10 bytes of storage.

hope it helps

new features sql server 2008-user defined table type and table valued parameters March 10, 2008

Posted by fofo in SQL Server 2008.
Tags: ,
9 comments

In Sql Server 2008 you can pass a table variable in a stored procedure as a parameter. now you have the ability to send multiple rows of data in a stored procedure.one main advantage of that is that it will reduce the amount of round trips to the server. 

we are going to walk through the following example that explains the new features in sql server 2008. we can create a sample database. for example

create database sqlserver2008sample

let’s create a table- a customers table

CREATE TABLE [dbo].[Customers](

[Cust_ID] [int] NOT NULL,

[Cust_Name] [varchar](50) NOT NULL,

[Cust_Surname] [varchar](50) NOT NULL,

[Cust_Email] [varchar](50) NOT NULL)

we must insert some values in the table. we can do that by using a stored procedure

create procedure insertintocustomer(@Cust_ID int,@Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))

as

begin

insert into customers

values(

@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)

end

in order to insert values in the table we must execute that stored procedure multiple times. for example

execute insertintocustomer 1,‘robbie’,‘fowler’,‘rb@liverpool.com’

execute insertintocustomer 2,‘michael’,‘owen’,‘mo@liverpool.com’

now lets do the same by inserting table value parameters. 

first we create a user defined table data type.

create type customertype as table

(Cust_ID int,Cust_Name varchar(50),Cust_Surname varchar(50),Cust_Email varchar(50))

then we create the new stored procedure that takes the table type as a parameter.

create procedure newcustomer(@Customer_details customertype READONLY)

as

begin

insert into customers

select * from @Customer_details

end

lets create a variable of table data type

declare @customers customertype

lets fill the table variable by using insert statements

insert into @customers values (1,‘steven’,‘gerrard’,‘sg@liverpool.com’)

insert into @customers values (2,‘jamie’,‘caragher’,‘jc@liverpool.com’)

now we can execute the stored procedure by passing the table value parameter- @customers

execute newcustomer @Customers

if we now try a select statement in the customers table we will see the new values added.

hope it helps. 

SQL Server 2008 new features-Grouping sets March 6, 2008

Posted by fofo in SQL Server 2008.
Tags: , ,
add a comment

In this post i will try to introduce a new T-SQL enhancement,Grouping sets, that will be available with SQL Server 2008.

In order to follow this example you need to open SQL Server Management Studio.Then you need to open a query window and write the following sql statement that creates a brand new table.

CREATE TABLE [dbo].[ProductSales](

[ID] [int] IDENTITY(1,1) NOT NULL,

[City] [varchar](30) NOT NULL,

[Province] [varchar](30) NOT NULL,

[Amount] [money] NOT NULL,

CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY]

then we enter some data to the new table with an insert statement that enters multiple rows at once

INSERT INTO ProductSales (City, Province, Amount)

VALUES (‘Athens’, ‘St-Ellada’, ‘125.00’),

(‘Bolos’, ‘Thessalia’, ‘120.00’),

(‘Patras’, ‘Peloponisos’, ‘55.00’),

(‘Athens’, ‘St-Ellada’, ‘425.00’),

(‘Larissa’, ‘Thessalia’, ‘150.00’),

(‘Piraeus’, ‘St-Ellada’, ‘265.00’),

(‘Aigio’, ‘Peloponisos’, ‘107.00’),

(‘Salonica’, ‘Makedonia’, ‘537.00’),

(‘Larissa’, ‘Thessalia’, ‘557.00’),

(‘Piraeus’, ‘St-Ellada’, ‘285.00’),

(‘Athens’, ‘St. Ellada’, ‘710.00’),

(‘Kavala’, ‘Makedonia’, ‘527.00’),

(‘Salonica’, ‘Makedonia’, ‘127.00’),

(‘Bolos’, ‘Thessalia’, ‘120.00’),

(‘Patras’, ‘Peloponisos’, ‘155.00’),

(‘Athens’, ‘St-Ellada’, ‘425.00’),

(‘Kavala’, ‘Makedonia’, ‘527.00’),

(‘Larissa’, ‘Thessalia’, ‘540.00’),

(‘Piraeus’, ‘St-Ellada’, ‘565.00’),

(‘Aigio’, ‘Peloponisos’, ‘107.00’)

now we want to create a group statement to group data by city

SELECT City, Sum(Amount) AS Amount

FROM ProductSales

GROUP BY City

when we execute the statement we see the total amount taken by each city.

similarly we can group by province.

SELECT Province, Sum(Amount) AS Amount

FROM ProductSales

GROUP BY Province

when we execute the statement we see the total amount taken by each province.

if we want to find the total amount taken we simply type in the query window

SELECT Sum(Amount) AS Amount

FROM ProductSales

So far i have created 3 different statements to get the results i wanted. wouldn’t be great to combine all the 3 above mentioned statements into one?

i can do that in sql server 2008 using grouping sets.so for the same example i have been using all along we have:

SELECT City, Province, Sum(Amount) AS Amount

FROM ProductSales

GROUP BY GROUPING SETS (

(City),

(Province),

())

ORDER BY City, Province

Follow

Get every new post delivered to your Inbox.

Join 1,788 other followers