February 2, 2012 at 2:00 pm
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);
-
February 2, 2012 at 2:11 pm
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
February 2, 2012 at 2:24 pm
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
February 2, 2012 at 8:10 pm
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
-
February 2, 2012 at 9:34 pm
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
February 3, 2012 at 12:34 am
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
-
February 3, 2012 at 4:48 am
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
February 3, 2012 at 12:56 pm
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:
-
February 3, 2012 at 2:57 pm
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.
February 3, 2012 at 7:24 pm
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?
-
February 4, 2012 at 4:41 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply