DOT NET RULES

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

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

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