TRIGGER question 1

[MelissaKT]

Ok, I admit it - I'm a complete newbie to Triggers. I've been trying to do this all day. I've checked through this site as well as others but can't seem to figure it out!! Ok, I have created (using the word loosely!) a trigger on tblQuanityDetail. When a user updates the field "QuanityReceived", I'd like for the trigger to update tblQuanity with the SUM(quanityReceived)where the QtyID = the inserted qtyID. How exactly do I go about this? So far the only thing I have that I think is correct is:

IF UPDATE(QuanityReceived)
BEGIN
UPDATE tblQuanity
SET QtyReceived =

I would truly appreciate any help! Thank you in advance!!

 

[Qik3Coder]

Things to keep in mind:

read up on the INSERTED and DELETED tables. These will tell you how to get the data that you will need to update your "summary" table.

When you are in a trigger and updating the table that the trigger is on, the trigger won't fire again.

When you are in a trigger and updating a different table, the appropriate trigger will fire on that table.

You may need to check for a row in your summary table, in case this is the first order.

Shoot back, if you need an actual example.



Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[MelissaKT]

Qik3Coder -
Thanks for responding!!! I feel like a proud mama right now. I actually have it working. I used:

IF UPDATE (QuanityReceived)
BEGIN
UPDATE tblQuanity
Set QtyReceived = (SELECT Sum(QuanityReceived) FROM tblQuanityDetail WHERE tblQuanity.QuanityID = tblQuanityDetail.QuanityID)
WHERE tblQuanity.QuanityID IN (SELECT QuanityID FROM inserted)
END

Ok, I'm feeling pretty spunky right now since that part is done. How would I go about adding another IF statement for a different column? Is it just "elseif"? I have another question, but I'll wait for to ask....

Thank you soooo much!!

 

[Qik3Coder]

Sweet...

Works like a standard update statement. You can even use joins in your table, but you should usually test these by using SELECTs first.

You have to be careful about using:
"...IN (SELECT QuanityID FROM inserted)"
you may end up updating more than you want to, on the off chance you do a multi row update.

Here is a generalized update i have for tracing updates:

--if row is changed or deleted, needs
--to be logged in the transaction table
IF EXISTS (SELECT * FROM inserted) --1825/365 = 5 years. for accounting purposes
  BEGIN
   UPDATE t --myTable
   set t.last_updated = getDate() 
   FROM inserted i, myTable t 
   WHERE i.rec_id = t.rec_id
 END
IF EXISTS (SELECT * FROM deleted WHERE insertTime > getDate()-1825) --1825/365 = 5 years. for accounting purposes
 BEGIN
  insert into myTable_trans
  select * from deleted where last_updated > getDate()-1825
 END

Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[MelissaKT]

Happy Monday - Hope you had a good weekend. First of all, thanks a bunch for the impromptu lesson on triggers. I changed the Update to

IF UPDATE (QuanityReceived)
	BEGIN
		UPDATE tblQuanity
		Set QtyReceived = (SELECT Sum(QuanityReceived) FROM tblQuanityDetail WHERE tblQuanity.QuanityID = tblQuanityDetail.QuanityID)
		FROM tblQuanity INNER JOIN  Inserted ON tblQuanity.QuanityID = Inserted.QuanityID
		WHERE tblQuanity.QuanityID = Inserted.QuanityID
	END

I hope that's what you meant? Anyway, my next question - Let's say that I want to make sure that the Total Quanity Received (in tblQuanity) never goes above the Quanity Ordered (in tblQuanity). How would I cancel the update and undo the record that has been entered into my tblQuanityDetail?

 

[Qik3Coder]

Someone else may have a cleaner way, but removed the row from the table that was just INSERTED and add/put back the row that was just DELETED.

btw, you can use ALIASES in your code to make it easier to type:

IF UPDATE (QuanityReceived)
   BEGIN
      UPDATE Q
      Set Q.QtyReceived = (
         SELECT Sum(QuanityReceived) 
         FROM tblQuanityDetail QD 
         WHERE Q.QuanityID = QD.QuanityID)
      FROM tblQuanity Q 
         INNER JOIN Inserted I ON 
            Q.QuanityID = I.QuanityID
      WHERE Q.QuanityID = I.QuanityID
   END

By the way, this is overkill. You could remove either one and get the same result.

      FROM tblQuanity Q 
         INNER JOIN Inserted I ON 
            Q.QuanityID = I.QuanityID
      WHERE Q.QuanityID = I.QuanityID

its the same as:

      FROM tableQ Q 
         INNER JOIN tableI I ON 
            1 = 1
      WHERE 1 = 1

Normally i remove the where clause, it really messes with the other developers when they see a SQL statement without a where clause, that retrieves exactly what it's supposed to.


Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[MelissaKT]

You're a really good instructor and I appreciate that! I started using the aliases on the stuff that I was working on - lots easier!

It just dawned on me that I don't have to do the validation in a trigger - I'm using classes to insert the values into a table - I can use those for the validation.

Thank you, Thank you, Thank you for all of your help!!