SQL Server Trigger Template

This a template for creating an After/Instead Of trigger in SQL Server 2005/2008:

CREATE TRIGGER <schema>.<tablename>$[InsteadOf]<actions>[<purpose>]Trigger
ON <schema>.<tablename>
[AFTER|INSTEAD OF] <comma delimited actions> AS
BEGIN

 DECLARE @rowsAffected INT,  --stores the number of rows affected
         @msg VARCHAR(2000)  --used to hold error message
 SET @rowsAffected = @@ROWCOUNT
 
 IF @rowsAffected = 0 RETURN
 
 SET NOCOUNT ON --to avoid the rowcount messages
 SET ROWCOUNT 0 --in case client has modified the rowcount
 
 BEGIN TRY
  --[validation section]
  
  --[modification section]

  --[perform action] --for INSTEAD OF trigger
 END TRY
 BEGIN CATCH
  IF @@trancount > 0
   ROLLBACK TRANSACTION
  
  --log the error
  EXECUTE utility.ErrorLog$insert  --this is only one example to do logging
  
  DECLARE @ERROR_MESSAGE NVARCHAR(4000)
  SET @ERROR_MESSAGE = ERROR_MESSAGE()
  RAISERROR (@ERROR_MESSAGE, 16, 1)
 END CATCH
END

Below is an example of using a table and a procedure to do error logging:

CREATE TABLE utility.ErrorLog(
 ERROR_NUMBER int NOT NULL,
 ERROR_LOCATION sysname NOT NULL,
 ERROR_MESSAGE varchar(4000),
 ERROR_DATE datetime NULL
  CONSTRAINT dfltErrorLog_error_date DEFAULT (getdate()),
 ERROR_USER sysname NOT NULL
  --use original login to capture the user name of the actual user
  --not a user that has been impersonated
  CONSTRAINT dfltErrorLog_error_user_name DEFAULT (original_login())
)
GO

CREATE PROCEDURE utility.ErrorLog$insert
(
 @ERROR_NUMBER int = NULL,
 @ERROR_LOCATION sysname = NULL,
 @ERROR_MESSAGE varchar(4000) = NULL
) AS
BEGIN
 BEGIN TRY
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  SELECT ISNULL(@ERROR_NUMBER, ERROR_NUMBER()),
      ISNULL(@ERROR_LOCATION, ERROR_MESSAGE()),
      ISNULL(@ERROR_MESSAGE, ERROR_MESSAGE())
 END TRY
 BEGIN CATCH
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  VALUES (-100, 'utility.ErrorLog$insert',
      'An invalid call was made to the error log procedure')
 END CATCH
END

Reference:
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson

Calling Controller Action Method with HTTP GET Ajax Request

In this post, we will see how to do Ajax request using HTTP GET method with JSON in ASP.NET MVC3. HTTP GET method to retrieve data in JSON format should never be used for getting sensitive information, the POST method should be used instead. For more information of how to use the POST method, you can read my previous post; Ajax with jQuery and JSON in ASP.NET MVC 3 or Passing Object in JSON to Controller.

First we prepare our controller method that will respond to the Ajax call:

public JsonResult GetDetailsUsingHttpGet(int id)
{
    Team team = teamRepository.Find(id);
    var anonymousTeam = new { TeamId = team.TeamId, Name = team.Name };

    //'JsonRequestBehavior.AllowGet' must be set, otherwise we will get an error message '500 error'
    return Json(anonymousTeam, JsonRequestBehavior.AllowGet);
}

Since MVC version 2, a controller method that is responding to a GET request is not allowed to return JSON format data for security reason. We need to explicitly state that this operation is allowed by setting 'JsonRequestBehavior.AllowGet' in the returning Json() method.

Then our JavaScript codes:

$(document).ready(function () {
    $("#ajaxBtnGetOne").click(function (event) {
        $.ajax({
            type: "GET",
            url: "/Teams/GetDetailsUsingHttpGet/1",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: AjaxGETSucceeded,
            error: AjaxFailed
        });
    });  
});
function AjaxGETSucceeded(objdata) {
    alert('success');
    alert(objdata.TeamId + ' - ' + objdata.Name);
    $('#ajaxDiv').html(objdata.TeamId + ' - ' + objdata.Name);
}
function AjaxFailed(result) {
    alert('an error has occured: ' + result.status + ' ' + result.statusText);
    alert(result.responseText);
}

Note that in the Ajax function we pass a parameter through its 'url' setting; in this example is '1' that will be passed as an 'id' parameter.