Automatically Setting a Field Value in SQL

Question

Hello,

I have a SQL Server 2008 database with two tables. The first table is called Department. The second table is called Ticket. These two tables are defined as follows:

Department
----------
ID
Name
TotalTickets

Ticket
------
ID
DepartmentID
Description
AssignedTo

I'm trying to figure out a way to dynamically update the Department.TotalTickets value. When a Ticket gets added or removed, I want to automatically increment or decrement the value of the TotalTickets. Can someone please tell me the best way to do this on SQL Server 2008?

Thank you

Answer 1

One solution would be to use Triggers to update your TotalTickets field when your INSERT, UPDATE or DELETE rows from your Tickets table.

You could also create a View, such that the TotalTickets column is calculated with aggregate functions. This is a much more normalized approach, because you wouldn't be storing calculated fields in your tables.

Answer 2

You should insert into both the Ticket table and the Department table in a transaction:

Begin Tran

Insert Ticket(Id...)
Values(@DepartmentId, ...)

Update Department
Set TotalTickets = TotalTickets + 1
Where Id = @DepartmentId

Commit Tran

This is obviously a simple example. In a real scenario, you would want to add a Try-Catch block and call Rollback Tran if an error occurred.

Answer 3

If you want to do this automagically, then a Trigger is your best bet.

You can do it two ways in a Trigger:

Your triggers on INSERT and DELETE can

• Increment or Decrement the number of tickets (faster, but can lead to bad answers if the trigger does not fire under certain conditions)

OR

• Count the Number of tickets and post the value (slower, but more reliable)

Answer 4

There are a number of ways to achieve this, you can use triggers after insert or delete on your ticket table, or you could take TotalTickets out of department and put it in a view.

CREATE VIEW viewDepartmentTickets
AS 
  SELECT d.Id, d.Name, Sum(t.ID) As TotalTickets
  FROM Department d
  LEFT OUTER JOIN Ticket t ON t.departmentid = d.departmentid
  GROUP BY d.Id, d.Name

If performance is an issue you can create the view as a indexed view, as Thomas points out the join would need to be an INNER JOIN, table names would need to be in the format dbo.Department and you would have to create the view WITH SCHEMABINDING to go down this route.

The view will be updated as the tickets are inserted/deleted.

Answer 5

Change TotalTickets column to a computed column, create a udf which calculates TotalTickets based on department id and set TotalTickets value to this udf.

This way you will need neither three triggers nor two commands.

Have a look at an example here: Using UDFs in Computed Columns

You can use indexes with computed columns too as it was stated in other answers.

Answer 6

Hi good day, just use a trigger it will do as you desire & is very fast but be sure to debug the trigger well before deploying it at production.