define trigger execution order

sp_settriggerorder use for defining the After trigger execution order. You can specify only the first trigger to be executed and the last trigger to be executed.

the syntax of the command is

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
       , [ @order = ] 'value(First or Last)'
       , [ @stmttype = ] 'statement_type ( Insert or update or delete)'
       [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

Note that after executing the sp_settriggerorder if you edit your trigger you will notice the call to the sp_settriggerorder
store procedure at the bottom of the trigger. This is because the trigger execution sequence can change any time a trigger is Alter  
For http://msdn2.microsoft.com/en-us/library/ms186762.aspx

Note

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.

If the same trigger must be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for that statement type.

Update from

You can use the Update from statement to update tables of one table from another

for example you have a table Employee_old_table (employee_id,employee_name,employee_lname)
and e table employee_new_table (employee_id,employee_full_name,employee_first_name,employee_last_name)

to set the employee order.employee_full_name = employee.employee_name + ' ' + employee_lname

you can do this

update employee_full_name
set
employee_full_name = e.employee_name + ' ' + e.empoyee_lname,
employee_first_name = e.employee_name,
employee_last_name = e.employee_lname
from employee e
where e.employee_id = employee_id

instead of

update employee_full_name
set
employee_full_name = (select e.employee_name + ' ' + e.empoyee_lname from employee e where e.employee_id = employee_id),
employee_first_name = (select e.employee_name from employee e where e.employee_id = employee_id),
employee_last_name = (select e.employee_lname from employee e where e.employee_id = employee_id)

Return a value from a strore procedure value

Store procedures can return value using a return statement in the store procedure

you can do something like this

create PROCEDURE dbo.test1
(
@iID int
)
AS
BEGIN
SET NOCOUNT ON;

Declare @iNewId int

set @iNewId = @iID + 1
return @iNewId
END

declare @iT int
exec @iT = dbo.test1 6

print @iT

Adding elements to a structure

While I was playing with a request structure in coldfusion I come across a strange scenario with adding elements in the structure
There are 3 ways to add an element in the structure

<cfset request.sMyStructre[elementid] = value />

<cfset structInsert(request.sMyStructre,elementid,value) />

<cfset request.sMyStructre.elementid = value />

Even though the result of those 3 commant 99% of the times is the same I found a case where they produce a different output

I create an object test.cfc

<cfcomponent output="true">

<cfset request.sReqObjects1 = structNew() />

</cfcomponent>


and a test cfm page
<cfset request.sReqObjects1 = structNew() /> <Cfset request.sReqObjects1['t2'] = createObject("component","Test") /> using the request.sReqObjects1['t2']<br />
<cfdump var="#request.sReqObjects1#"> <cfset structInsert(request.sReqObjects1,'t3',createObject("component","Test")) /> using the structInsert(request.sReqObjects1,'t3',createObject("component","Test")) <br /> <cfdump var="#request.sReqObjects1#"> <Cfset request.sReqObjects1.t1 = createObject("component","Test") />
using the request.sReqObjects1.t1 <cfdump var="#request.sReqObjects1#">


And the output was
using the request.sReqObjects1['t2']

struct [empty]

using the structInsert(request.sReqObjects1,'t3',createObject("component","Test"))

struct [empty]

using the request.sReqObjects1.t1

struct
T1	component Test

The only reason I can thing for this is that
using [request.sReqObjects1['t2'] =] or [structInsert ] creates the element on a different memory location and just adds a reference to that location in the structure, which then is cleared by the <cfset request.sReqObjects1 = structNew() /> in the cfc constructor. But when using the <Cfset request.sReqObjects1.t1 = the new element is actually created in the memory space of the structure.

ColdFusion 8 and MSSQL triggers

Coldfusion 8 handles MSSQL triggers different that older versions of Coldfusion.

IN older CFMX

We had some For insert triggers on tables for updating related tables etc.. The last trigger used to return the new identity using select SCOPE_IDENTITY() as @iNewRec

the cfquery was

<cfquery name="qInsert" datasource="#myDSN#">
insert into tbl1 (name,fname)
values
('test1','test2')
</cfquery>


Then you could do the

<cfset variables.iNewId = qInsert.iNewRec />

In CFML 8 this will not work. You will get the error that qInsert is Undefined.

The solution is
a) make sure that the select SCOPE_IDENTITY() as @iNewRec is executed before any update/insert delete statement in any trigger in the sequence...

or

b) In the cfquery tag after the insert add a dummy select

<cfquery name="qInsert" datasource="#myDSN#"> insert into tbl1 (name,fname) values ('test1','test2') select 'ttt' as t </cfquery>

DateDiff in coldfusion does not work the same way as dateDiff in sql

DateDiff in coldfusion does not work the same way as dateDiff in sql

I believe Coldfusion gets the different between date1 and date2 in ms and then converts this value to the date part that you specify on the dateDiff function. Sql subtracts the datepart value of date 1 from the date part value of date2.

What does that mean

DateDiff('d','2007/10/02','2007/12/01') in coldfusion is : 1 dateDiff(month,'2007/10/02','2007/12/01') in SQL is : 2 DateDiff('d','2007/10/02','2007/12/04') in coldfusion is : 2 dateDiff(month,'2007/10/02','2007/12/04') in SQL is : 2

To make coldfusion return the same value as SQL use this code

datePart('m',createDate(2007,12,1)) - datePart('m',createDate(2007,10,1))

Calling public methods with in the component without 'this' is faster

I did some testing whether i should use this to call public methods with in the component and it seams that calling a public method with in the component is faster if you do not use the 'this' .

I created 2 simple cfc that were calling internally a public function the one with this.function name and the other one the function name directly not using the this was faster for 15-20 %

MSSQL, - 2005 - search in the code of Store procedure,function, views and triggers

We had an sql function that we didn't know if is used for anywhere, so we decided to remove it. There was no reference to the store procedure in our code so the only place remaining was the sql store procedures, functions and triggers and views

To do this we use the sql query

select *
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION','VIEW','SQL_STORED_PROCEDURE',
'SQL_SCALAR_FUNCTION','SQL_TRIGGER')
and OBJECT_DEFINITION (object_id) like '%MinPricePreCovered%'

Query Of Queries is case sensitive

I discover recently that Query of queries is case sensitive. to solve the problem i use the function Lcase to convert the coldfusion variables to low case and the query function Lower to convert the query column to low case.


select *
from qQ2
where
lower(column_1) = '#LCase(cF1)#'

ColdFusion 8 Bug - CronService fails with: whith listAll schedule task

This is an error i found on CF 8 using the coldfusion.server.ServiceFactory

When you use the createObject("JAVA","coldfusion.server.ServiceFactory").getCronService().listAll() to get the list of schedule task on the server it doesn't return any newly added schedule tasks that were added with the cfschedule command (it return [undefined array element]) - however this is not the case when you add the schedule task directly in the server.

The solution is to use the load() method before retrieving the list of all schedule task