SQL Server 2008 - Insert Trigger: How to add values not in Inserted pseudo table

Question

Setup

I am trying to write a really simple double entry accounting system for a small business. It is more for my personal training than anything else.

I have two tables, Transactions and DebitCredits:

    [dbo].[Transactions](
    [TransactionId] [int] IDENTITY(1,1) NOT NULL,
    [TransactionDate] [date] NOT NULL,
    [IdFrom] [int] NOT NULL,
    [IdTo] [int] NOT NULL,
    [Amount] [decimal](18, 2) NOT NULL,
    [IdCostCentre] [int] NOT NULL,
    [IdCurrency] [int] NOT NULL)

...where IdFrom and IdTo are FKs for an Accounts table that holds the details for each account. From and To refer to the direction the money is going in. I.e., it is taken from From and give to To.

[dbo].[DebitCredits](
    [DebitCreditId] [int] IDENTITY(1,1) NOT NULL,
    [TransactionDate] [date] NOT NULL,
    [IdAccount] [int] NOT NULL,
    [Amount] [money] NOT NULL,
    [DorC] [char](1) NOT NULL)

What I want:

When a row gets inserted into Transactions (from an MVC 3 website), I want a trigger to insert two rows into DebitCredits:

Transactions row:

1, 01/01/2012, 33, 44, 300, 2, 1

...where 1 is the TransactionId, the date is the date, 33 is the From Account id and To is the To Account Id. 300 is the amount, 2 is an FK for a CostCentres table and 1 is the currency (eg, Argentine Pesos).

DebitCredits rows:

3, 01/01/2012, 33, -300, "D"
4, 01/01/2012, 44, 300, "C"

The problem:

I don't know how to express in T-SQL that two insert statements that I need. I can insert the values I get from the Transactions row, but I can't:

1. change the sign on the amount (300)
2. I don't know how to set the value for the DorC (Debit or Credit) column, ie, "D" or "C", depending on IdFrom and IdTo columns in the Transactions table.

Where I have got to:

ALTER TRIGGER [dbo].[tgInsertDebitCreditRows] ON [dbo].[Transactions]
FOR INSERT
AS
  INSERT INTO DebitCredits
        (IdTransaction, TransactionDate, IdAccount, Amount, DorC)
     SELECT
        TransactionId, TransactionDate, IdFrom, Amount, "C"
     FROM inserted

  INSERT INTO DebitCredits
        (IdTransaction, TransactionDate, IdAccount, Amount, DorC)
    SELECT
        TransactionId, TransactionDate, IdTo, Amount, "C"
    FROM inserted

Obviously, this fails, because SQL Server doesn't 'know' the columns "C" and "D". And these aren't columns, they are just the (expression of wishes) values that I want inserted.

And also the amounts would be 300 in both cases, instead of -300 and 300.

Any pointers? (It goes without saying that I have never written a trigger in my life... more of a web developer really).

Answer 1

I think you're pretty close, actually !

• Obviously, this fails, because SQL Server doesn't 'know' the columns "C" and "D".

Well, you need to use 'C' and 'D' (single quotes) as string literals - that'll work just fine.

• And also the amounts would be 300 in both cases, instead of -300 and 300.

No problem - just multiply one of the amounts with -1 to "switch" its sign.

ALTER TRIGGER [dbo].[tgInsertDebitCreditRows] ON [dbo].[Transactions]
FOR INSERT
AS
    INSERT INTO dbo.DebitCredits
        (IdTransaction, TransactionDate, IdAccount, Amount, DorC)
       SELECT
          TransactionId, TransactionDate, IdFrom, Amount, 'C'
       FROM inserted

    INSERT INTO dbo.DebitCredits
        (IdTransaction, TransactionDate, IdAccount, Amount, DorC)
      SELECT
        TransactionId, TransactionDate, IdTo, -1.0 * Amount, 'D'
      FROM inserted