Trigger Help

[CFB]

This is my first time creating a trigger, so please bear with me. I've looked through SQL Server Books Online, Google groups, and these forums, but it's still not clicking. Here's what I'm trying to do.

I've got a table which I'd like to create a trigger for. When a row gets inserted, I want the trigger to fire. When the trigger fires I want it to analyze data in two of the columns of the newly inserted row and perform a calculation. Then, I want it to update a third column in the newly inserted row with the value from that calculation.

I'm also looking to create another trigger (or modify the trigger above) so that when either of the two columns mentioned above are updated, the trigger will fire. It will run the same anaylsis/calculation on those two columns and update the third column of the updated row with the value from that calculation.

I'm using a GUID in the table that I want to create the trigger for. To determine what the GUID so I can perform the necessary SELECT and UPDATE commands, I need to be able to determine the GUID of the newly inserted or updated rows. I understand there is an INSERTED and DELETED table that I can reference, but I'm not clear on how those work. I know those tables can be used to see what data was inserted or deleted, but are the tables unique to each INSERT, UPDATE, or DELETE command that is run? Or, does it house the data from all INSERT, UPDATE, and DELETE commands that have been run?

I'm sure once I get the trigger in place, I'll be able to understand what's going on a little better. Any suggestions?

 

[SQLSister]

OK, let's start with the easy question. The inserted and deleted tables refer only to the data in the action that triggered the trigger. So if you want to know what GUID were updated, Select GUID from inserted would tell you.

To update information you would join the results of the inserted table to the main table as part of the update.
Something like:
UPDATE SalesRecords
SET TotalSalePrice = inserted.SalePrice * inserted.Qty
FROM SalesRecords, inserted
WHERE SalesRecords.GUID = inserted.GUID
and (Inserted.Qty <>SalesRecords.Qty Or Inserted.SalePrice<>SalesRecords.SalePrice)

Hope this helps.

 

[CFB]

Thank you sista! That's exactly what I needed to know.

Just one other question. Can I use IF statements and other PL SQL language in triggers or would I need to move that sort of stuff into a stored procedure? For instance, if I had some criteria in there to give a discount for purchases where the QTY was greater than 5, how would I do that?

Thanks again, sometimes I just need someone to clarify a few things for me and everything else begins to fall in place.

 

[SQLSister]

I would put it in the where clause of the update or insert statement. If I can do something in one set-based statement I will use that in preference to using procedural language like IF. But yes, you can use IF. Other PL/SQL statements, I can't say as not all of them work in T-SQL anyway.