beginner requesting help with trigger that updates a table when an insert occurs in another

  • First, I read Jack Corbett's intro to triggers and am attempting to stick with set based sql.

    The purpose of my trigger is to update the customer balance column of the customer table whenever a new invoice record is inserted in the invoice table (for that same cust_num).

    Based on the error I'm getting, I think the FIRST problem with using this set based solution, is that an 'inserted' table does not exist. However, from what I have read on line, this inserted table is like a system table that is generated automatically,not one I should be creating. I have no idea where it resides, and I am not able to select against it.

    ---My trigger is as follows:

    create trigger trg_UpdateCustBalance

    on dbo.invoice

    for insert

    as

    set nocount on

    begin

    update dbo.customer

    set dbo.customer.cust_balance =

    dbo.customer.cust_balance + (select dbo.invoice.inv_amount

    from dbo.invoice

    join dbo.customer

    on dbo.INVOICE.CUST_NUM = dbo.CUSTOMER.CUST_NUM

    where dbo.customer.cust_num = dbo.invoice.cust_num)

    where inserted.cust_num = cust_num

    end

    ---gives this error

    Msg 4104, Level 16, State 1, Procedure trg_UpdateCustBalance, Line 15

    The multi-part identifier "inserted.cust_num" could not be bound.

    ---DDL to recreate environment

    CREATE TABLE [dbo].[CUSTOMER](

    [CUST_NUM] [int] NOT NULL,

    [CUST_LNAME] [varchar](20) NULL,

    [CUST_FNAME] [varchar](20) NULL,

    [CUST_BALANCE] [int] NULL,

    [CUST_DOB] [date] NULL,

    [CUST_AGE] AS (datepart(year,getdate())-datepart(year,[CUST_DOB])));

    INSERT INTO dbo.CUSTOMER (CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE, CUST_DOB)

    values

    (1000, 'Smith', 'Jeanne', '289', '1979-03-15'),

    (1001, 'Ortega', 'Juan', '208','1988-12-22'),

    (1002, 'Clistenova', 'Olena', '0', '1970-04-07'),

    (1003, 'Strauss', 'Richard', '0','12-15-1785')

    CREATE TABLE [dbo].[INVOICE](

    [INV_NUM] [int] NOT NULL,

    [CUST_NUM] [int] NULL,

    [INV_DATE] [datetime] NULL,

    [INV_AMOUNT] [numeric](5, 2) NULL);

    INSERT INTO dbo.INVOICE (INV_NUM, CUST_NUM, INV_DATE, INV_AMOUNT)

    VALUES

    (8000, 1000, '2008-04-23 00:00:00.000', 235.89),

    (8001, 1001, '2008-03-23 00:00:00.000', 312.82),

    (8002, 1001, '2008-03-30 00:00:00.000', 528.10),

    (8003, 1000, '2008-04-12 00:00:00.000', 194.78),

    (8004, 1000, '2008-04-23 00:00:00.000', 619.44),

    (8005, 1002, '2008-05-10 00:00:00.000', 478.00);

    -

  • two tables, the INSERTED and DELETED tables are virtual tables that exist inside the trigger.

    they are the data being inserted/updated/deleted into the table the trigger exists on...in your case,dbo.invoice.

    they exist inside every trigger (or OUTPUT clause,a s you'll trip over it later), so that's where it is best practice to always refer to them as the set based operations you need existi inside them

    ---My trigger is as follows:

    create trigger trg_UpdateCustBalance

    on dbo.invoice

    for insert

    as

    set nocount on

    begin

    update dbo.customer

    set dbo.customer.cust_balance =

    dbo.customer.cust_balance + MyAlias.inv_amounts

    FROM (select

    CUST_NUM ,

    SUM(dbo.invoice.inv_amount) AS inv_amounts

    from INSERTED --this is the data being inserted into the table that the trigger exists on in this case the dbo.invoice

    group by CUST_NUM ) MyAlias

    INNER JOIN dbo.customer

    on MyAlias.CUST_NUM = dbo.CUSTOMER.CUST_NUM

    where dbo.customer.cust_num = MyAlias.cust_num

    end --trigger

    i didn't test the trigger, but it passes syntax inspection.

    now to the issue at hand:

    i'd say it's best practice to never store a rollup of information in another table.

    so if you need a total in another table, that table should actually be a VIEW...really a VIEW that joins customers and invoices together to calculate the total, instead of storing the value as a static field value.

    the view would calculate the correct amount, every time, on demand. the static value field, which depends on a trigger to execute, could get out of whack if the trigger is poorly written, or fails just once, or is not triggered at all due to, say a bulk insert (which does not trip triggers) or explicit code which disables the trigger.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I actually think it is a little easier than that but please ensure that what I am doing is what you need.

    alter trigger trg_UpdateCustBalance

    on dbo.invoice

    for insert

    as

    set nocount on

    begin

    update dbo.customer

    set dbo.customer.cust_balance =

    c.cust_balance + i.inv_amount

    from dbo.customer c inner join inserted i

    on c.cust_num = i.CUST_NUM

    end

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • I took David's answer because it used my existing column names and was less confusing to me.

    That trigger works....but has a bug. It updates both 1002 and 1003. Makes no sense to me why it is touching 1002 at all.

    ---invoice table before

    CUST_NUMCUST_BALANCE

    1000289

    1001433

    1002478

    1003300

    ---insert

    insert into dbo.invoice (inv_num, cust_num, inv_date, inv_amount)

    values

    (8013, 1003, '27-apr-08', 300)

    --invoice table after

    CUST_NUMCUST_BALANCE

    1000289

    1001433

    1002956

    1003600

    -

  • Actually, the trigger that I wrote was wrong for another reason, i.e. I wasn't summing the values for the multiple invoice entries per customer but it wouldn't have (and didn't - I tested 🙂 ) updated another cust_num as you described.

    The version that Lowell wrote is actually correct. I fixed one syntax error in that and am including that below.

    alter trigger trg_UpdateCustBalance

    on dbo.invoice

    for insert

    as

    set nocount on

    begin

    update dbo.customer

    set dbo.customer.cust_balance =

    dbo.customer.cust_balance + MyAlias.inv_amounts

    FROM (select

    CUST_NUM ,

    SUM(inv_amount) AS inv_amounts

    from INSERTED --this is the data being inserted into the table that the trigger exists on in this case the dbo.invoice

    group by CUST_NUM ) MyAlias

    INNER JOIN dbo.customer

    on MyAlias.CUST_NUM = dbo.CUSTOMER.CUST_NUM

    where dbo.customer.cust_num = MyAlias.cust_num

    end --trigger

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • So, I tried Lowell's script (where a table called MyAlias is pulled 'voila' out of thin air. Unsure :unsure:) never seen before:w00t:)

    Bug is still there. Both 1002 and 1003 have new balance (300 added to both).

    ---insert

    insert into dbo.invoice (inv_num, cust_num, inv_date, inv_amount)

    values

    (8020, 1003, '27-apr-08', 300)

    ---now

    CUST_NUMCUST_BALANCE

    1000289

    1001433

    10021434

    1003900

    -

  • the trigger, corrected for the syntax issue, seems to work fine for me; could it be you didn't reset the data back to teh original state when you were testing the other trigger?

    either way, it goes back to what i stated after my example...storing summary data that is derived from another table is prone to errors.

    when you need the data with the invoice totals, you should either join the two tables together, or create a view that does it for you, so you can just query the view.

    something like this would not need your trigger, and would always be accurate:

    CREATE VW_Customer

    AS

    SELECT

    Customer.CUST_NUM,

    Customer.CUST_LNAME,

    Customer.CUST_FNAME,

    InvoiceSummary.CUST_BALANCE,

    Customer.CUST_DOB

    FROM dbo.Customer

    LEFT OUTER JOIN (SELECT

    CUST_NUM ,

    SUM(inv_amount) AS CUST_BALANCE

    FROM dbo.invoice

    GROUP BY

    CUST_NUM

    ) InvoiceSummary

    ON Customer.CUST_NUM = InvoiceSummary.CUST_NUM

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Well, thank you for that. This is a text book example. I am learning triggers and it is Peter Rob and Carlos Coronells sample databases and questions I am installing and practicing answering . . .

    I appreciate the real world explanation from you....I would have expected real world examples from a 2009 edition of Database Systems.:ermm:

    -

  • Lowell (2/2/2012)


    now to the issue at hand:

    i'd say it's best practice to never store a rollup of information in another table.

    so if you need a total in another table, that table should actually be a VIEW...really a VIEW that joins customers and invoices together to calculate the total, instead of storing the value as a static field value.

    I agree with this. Too many times I've had to fix balance columns in another table because some use of a function in an app somewhere didn't correct the balance correctly.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Yes, Lowell, I really do see your point. A view like this also spares one the need to create a second trigger for when an invoice is deleted, which is another exercise in the book.

    Given the two tables, can you think of a useful trigger that I could practice and get feedback on?

    -

  • learning how triggers work is absolutely valuable, so continue what you are doing.

    you really need to grasp the use of the INSERTED and DELETED tables , and how you have to think of the data as multiple rows at all times.

    the examples they are using are kind of understandable...they want something you can intuitively understand,and not too abstract.

    Just keep in mind that the learning example is not always right for the real world application, and you are fine.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply