Can triggers make use of parameters? 1

[tebathe3]

I am looking for a solution to maintain a "quantities remaining" type of column in my database. I have constructed a solution in my application code, but I thought perhaps a trigger would be more appropriate. I need to basically do the following:

UPDATE SubCat05
SET QuanRemain = (SELECT quantity FROM SubCat05 WHERE SubCatID = @SubCatID) - (SELECT SUM(quan) FROM POSubCat WHERE SubCatID = @SubCatID)

The @SubCatID parameter is the value of the primary key of the SubCat05 table. Can I accomplish this using a trigger?

 

[vongrunt]

This requires two triggers:

#1 FOR INSERT, UPDATE on table SubCat05
#2 FOR INSERT, UPDATE, DELETE on table POSubCat

#2 is here in case someone changes Quantity field.

Personally I wouldn't do that. Better option IMO: instead of QuanRemain, create column SumQuan or something, update it with trigger #2. Calculate QuanRemain as quantity-SumQuan. One trigger less, no chaining possible.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tebathe3]

I understand, that does sound like a better option. However, I'm still confused as to how to specify to the trigger what the value of subcatid is.

 

[vongrunt]

Thru pseudotables inserted and deleted.

This looks like good by-the-book example for "denormalized" trigger so... what is the primary key in table POSubCat?

Btw. I made a typo - should be "#1 is here in case someone changes Quantity field.".


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tebathe3]

the posubcat table is a junction table, so I have poid and subcatid as the primary key for that table. another table, pos05 has the primary key poid. basically, the subcat05 table holds all of the budget items. pos05 holds po numbers (order date, notes etc), and the posubcat table links budget items up with the pos and also specifies the quantity of each budget item purchased on that PO. So the quantity field in the subcat05 table is the "budgeted quantity" field. the quan field in the posubcat table is the "actual quantity" field.

If the quan field gets updated in the posubcat table, i need the sumquan field to be updated in the subcat05 table.

So you do think though that this is an appropriate time to use a trigger? It's my first time actually using one.

 

[vongrunt]

> So you do think though that this is an appropriate time to use a trigger?

If all inserts/updates/deletes can be "channeled" through stored procedures then triggers are not necessary.

If not - because of lots of client-side dynamic SQL or occasional imports from other sources (DTS) etc - then trigger will make sure QuanRemain is always up-to-date.

There are some performance concerns regarding triggers, and there are at least half dozen coding pitfalls to encounter... performance penalties are often dictated by DB/application design, pitfalls are mild annoyance if you are experienced enough.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tebathe3]

so if someone does update the quan field in the posubcat table, which pseudocode table do i use? it doesn't seem like deleted or inserted would be the right one?

 

[vongrunt]

INSERT - inserted
UPDATE - both inserted and deleted
DELETE - deleted

IMO this scenario is worth a FAQ so... If you really want a trigger, I'll have some free time tomorrow to write it by-the-book.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tebathe3]

That would be great. I did check the faqs first and was hoping something was there. Thank you!

 

[pmegan]

The inserted table is used for updates as well as inserts, so "select quan from inserted" will give you the new value.

Also, try to keep the subqueries to a minimum inside the trigger or you'll run into one of the performance issues vongrunt was talking about real fast.

 

[vongrunt]

OK, here is lab scenario - orders and products (yep, blah). I simplified table structure down to relevant stuff only.

create table Orders 
(	OrderID int primary key,
	TotalPrice money default 0 not null
)

create table OrderDetails
(	OrderID int,
	ProductID int,
	Price money not null
	primary key (OrderID, ProductID)
)

insert into Orders(OrderID) values (1)
insert into OrderDetails (OrderID, ProductID, Price) values (1, 10, 25.0)
insert into OrderDetails (OrderID, ProductID, Price) values (1, 12, 32.0)
insert into Orders(OrderID) values (2)
insert into OrderDetails (OrderID, ProductID, Price) values (2, 12, 32.0)

Before everything, make code that fills totals with up-to-date values. In this case:

update Orders
set TotalPrice = (select sum(Price) from OrderDetails where OrderID= Orders.OrderID)

Here is trigger code

create trigger trOrderDetailQty on OrderDetails
for insert, update, delete
as

update O
set TotalPrice = TotalPrice + Diffs.PriceDiff
from Orders O
inner join
(	select isnull(I.OrderID, D.OrderID) as OrderID,
		sum(isnull(I.Price, 0) - isnull(D.Price, 0)) as PriceDiff
	from inserted I
	full outer join deleted D on I.OrderID=D.OrderID and I.ProductID=D.ProductID
	group by isnull(I.OrderID, D.OrderID)
) Diffs on Diffs.OrderID = O.OrderID
where Diffs.PriceDiff <> 0

go

It handles all 3 types of changes (IUD) as well as multirow changes.

In most of situations it is better to separate at least INSERTs/UPDATEs from DELETEs (either one trigger with IF UPDATE() check or two triggers - one for IU, another for D), but in this simple case all-in-one code is probably good enough. Full outer join should not be performance killer because number of affected rows (inserted/deleted tables) is usually small.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tebathe3]

this works wonderfully. i just have one question though. in the trigger script, what is the "O"? where it says update "O".

 

[gmmastros]

O is the table alias

> from Orders O

It can also be re-written as
> from Orders As O

The 'As' is optional. I think it makes the code clearer, so I use it. O really refers to the Orders table. You can replace O with Orders and everything will work out fine.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Alias for used table (see line containing "from Orders O"). Makes code shorter. Very handy if you do a lot of manual coding.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tebathe3]

I figured it was some sort of alias but I was confused since it didn't say "As O". I didn't know you could do it without the As. Thanks!!