x

Triggers - Where am I going wrong?

-- Write an insert, delete and updated trigger named

-- trgLineUpdate for the Line Table. Update the Invoice

-- table to show the total value of the invoice (the sum

-- of the Line table Line_Units times Line_Price).

CREATE TRIGGER trgLineUpdate
ON LINE
AFTER INSERT, DELETE, UPDATE
NOT FOR REPLICATION
AS
IF @@ROWCOUNT > 0
BEGIN
    DECLARE @ActionType int
    , @RowCount int
    , @INV_NUMBER nvarchar(50) ;
    SET @ActionType = 0 ;
    SELECT @RowCount = count(*)
    FROM deleted ;
    IF @RowCount>0
       SET @ActionType = 1 ;
    SELECT @RowCount = count(*)
    FROM inserted ;
    IF @RowCount > 0
       SET @ActionType = @ActionType + 2 ;     
    IF @ActionType = 1
       SELECT @INV_NUMBER = INV_NUMBER
       FROM deleted ;
    ELSE
       SELECT @INV_NUMBER + INV_NUMBER
       FROM inserted ;
    INSERT INTO INVOICE
       (AMOUNT)
    VALUES
       (foo.TotalPrice)
    SELECT INVOICE I
       INNER JOIN
       (
         SELECT INV_NUMBER
          , LINE_UNITS * LINE_PRICE AS TotalPrice
         FROM LINE L
         GROUP BY INV_NUMBER
       )
       AS foo
       ON I.INV_NUMBER = foo.INV_NUMBER
    CASE @ActionType
       WHEN 1 THEN 'Delete'
       WHEN 2 THEN 'Insert'
       WHEN 3 THEN 'Update'
END
more ▼

asked Dec 04, 2010 at 05:06 AM in Default

rawilken gravatar image

rawilken
101 8 11 11

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can create the trigger a little bit simpler and more clear.

You can write the trigger like this:

CREATE TRIGGER trgLineUpdate
ON LINE
AFTER INSERT, DELETE, UPDATE
NOT FOR REPLICATION
AS
  WITH DiferenceSums AS (
    SELECT
      ISNULL(I.IVN_NUMBER, D.INV_NUMBER) AS INV_NUMBER,
      SUM(ISNULL(I.LINE_UNITS, 0) * ISNULL(I.LINE_PRICE, 0) - ISNULL(D.LINE_UNITS, 0) * ISNULL(D.LINE_PRICE, 0)) AS INV_SUM
    FROM INSERTED I
    FULL OUTER JOIN DELETED D ON I.INV_NUMBER = D.INV_NUMBER
    GROUP BY ISNULL(I.INV_NUMBER, D.INV_NUMBER)
  )
  UPDATE I SET
    AMOUNT = ISNULL(AMOUNT, 0) + DF.INV_SUM
  FROM INVOICE I
  INNER JOIN DifferenceSums DF ON I.INV_NUMBER = DF.INV_NUMBER
GO

Hope I wrote the trigger correctly and don't have a typo there as I'm not on my PC and it's written in Notepad.

The CTE calculates SUM difference.. If the record was inserted you wil receive sum of the inserted records for invoices. If records were deleted you will receive sum of the deleted records as negative value. If records were modified you will receive the SUM of differences.. Sum of new values - sum of old values.. Then when joined to the infoce the, original amount is updated by the difference. This helps you that you do not need to read the lines table again as you have all the diff data in the INSERTED and DELETED virtual tables.

When the trigger is written in this way, it will also keep the invoice table consistent, even you write a query, which inserts/deletes/updates lines from several invoices in single query. The triger updates amounts for all the invoices affected.

Anyway I thinkg and a stored proc for adding, removing or modifying rows from the invoce could be better and the sums can be calculated in the stored proc. But if you require the trigger, the one I post here, could be a way.

EDIT - Sample

CREATE TABLE dbo.Invoice (
    INV_NUMBER int NOT NULL IDENTITY(1,1),
    INV_DATE datetime NOT NULL,
    AMOUNT money,
    CONSTRAINT PK_Invoice PRIMARY KEY CLUSTERED (INV_NUMBER)
)
GO

CREATE TABLE dbo.Lines (
    LINE_ID int NOT NULL IDENTITY(1,1),
    INV_NUMBER int NOT NULL,
    LINE_UNITS int NOT NULL,
    LINE_PRICE money NOT NULL,
    CONSTRAINT PK_Lines PRIMARY KEY CLUSTERED (LINE_ID),
    CONSTRAINT FK_Lines_Invoice FOREIGN KEY (INV_NUMBER) REFERENCES dbo.Invoice(INV_NUMBER) ON UPDATE CASCADE ON DELETE CASCADE
)
GO

CREATE TRIGGER dbo.trgLineUpdate
ON dbo.Lines
AFTER INSERT, DELETE, UPDATE
NOT FOR REPLICATION
AS
  WITH DiferenceSums AS (
    SELECT
      ISNULL(I.INV_NUMBER, D.INV_NUMBER) AS INV_NUMBER,
      SUM(ISNULL(I.LINE_UNITS, 0) * ISNULL(I.LINE_PRICE, 0) - ISNULL(D.LINE_UNITS, 0) * ISNULL(D.LINE_PRICE, 0)) AS INV_SUM
    FROM INSERTED I
    FULL OUTER JOIN DELETED D ON I.INV_NUMBER = D.INV_NUMBER
    GROUP BY ISNULL(I.INV_NUMBER, D.INV_NUMBER)
  )
  UPDATE I SET
    AMOUNT = ISNULL(AMOUNT, 0) + DF.INV_SUM
  FROM INVOICE I
  INNER JOIN DiferenceSums DF ON I.INV_NUMBER = DF.INV_NUMBER
GO

DECLARE @inv_number int
DECLARE @line_number int

INSERT INTO dbo.Invoice(INV_DATE) VALUES (GETDATE())
SELECT @inv_number = SCOPE_IDENTITY()
SELECT * FROM dbo.Invoice

INSERT INTO dbo.Lines(INV_NUMBER, LINE_UNITS, LINE_PRICE)
VALUES(@inv_number, 5, 15)

SELECT * FROM dbo.Invoice

INSERT INTO dbo.Lines(INV_NUMBER, LINE_UNITS, LINE_PRICE)
VALUES (@inv_number, 10, 5)

SELECT @line_number = SCOPE_IDENTITY()

SELECT * FROM dbo.Invoice

UPDATE dbo.Lines SET LINE_UNITS = 5 WHERE LINE_ID = @line_number

SELECT * FROM dbo.Invoice

DELETE dbo.Lines WHERE LINE_ID = @line_number

SELECT * FROM dbo.Invoice
more ▼

answered Dec 04, 2010 at 07:33 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

This code looks good but gives me the same results, a list without any values for the Amount. Amount is all zeros. So it is either the code or the data set I am working with. I can't check the data set till Tuesday as I am testing it remotely on a model that does not allow me to view the data set.
Dec 04, 2010 at 08:15 AM rawilken
I dont' know what you mean by the list without any values.. The trigger does exactly what you wanted and described in the question.. I've added a complete sample to my answer :-)
Dec 04, 2010 at 09:52 AM Pavel Pawlowski
Nice answer Pavel...the question evokes another design question: should a db have the same data stored in multiple places, if yes when is it acceptable?
Dec 04, 2010 at 11:36 AM Scot Hauder

@Scot, agree, whether duplicate the data or not, depends on the design and how you will use the data. E.g. in datawarehouse you will gain much better performance when querying invoice amounts (if we speak about this small sample), but OLTP not so much. But even in OLTP this could be worth if I have to query very frequently the totals of invoices and do not care about separate items.

It's a question of performance impact during inserts/updates/deletes and implact on additional storage required and the gain in performance during querying.

Another question is whether implement this by triggers or use stored procedures which will take care about the updates.
Dec 04, 2010 at 11:58 AM Pavel Pawlowski
True, I guess my perspective isn't a concern for the extra storage lost, but what I've seen in client's dbs is where the multiple pieces of data become out of sync. In this case, it would be where the sum of items no longer match the total on the invoice. Then you are at a point where you can't trust the data--do I trust the item sum, or was an item not updated but the total was, and thus the total is really correct
Dec 04, 2010 at 01:06 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

Any chance you can tell us what the problem is please? What happens with this and what do you actually want to happen? Have you tried the trigger for just one of update, insert delete, just to see if that does what you want in one case? Have you read BOL for creating triggers? [http://msdn.microsoft.com/en-us/library/ms189799.aspx][1]

[1]: http://msdn.microsoft.com/en-us/library/ms189799.aspx
more ▼

answered Dec 04, 2010 at 05:17 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

+1 because you're encouraging the user to RTFM and it's a homework question.
Dec 06, 2010 at 05:46 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x121
x77

asked: Dec 04, 2010 at 05:06 AM

Seen: 1912 times

Last Updated: Dec 04, 2010 at 05:15 AM