March 7, 2012 at 10:12 am
Can someone explain me how this trigger functionality works. I am just getting confused of how it works.
CREATE TRIGGER abc_InsertUpdate
ON XYZ
FOR INSERT, UPDATE AS
IF @@ROWCOUNT = 0
RETURN
IF UPDATE(xyzID)
BEGIN
INSERT INTO EFGH
( ID, BalanceID, SID, PTrID,
StatusTrID, LoanDate, CreateTime )
SELECT lab.ID, i.BalanceID, i.RID, i.BID,
i.statusTrID, i.StatusDateTime, GETDATE()
FROM inserted i INNER JOIN
Loan lab
ON lab.LtrID = i.LTrID
ORDER BY i.LTrID ASC
END
March 7, 2012 at 10:30 am
the UPDATE() function is really misleading;
With "UPDATE(xyzID) " for example,
a lot of people interpret that to mean "If Column (xyzID) changed".
actually SQL interprets it as "If the column xyzID was explicitly named in the list of columns.
a column might be set to teh same value, so it did not change, but it was mentioned in the query.
CREATE TRIGGER abc_InsertUpdate
ON XYZ
FOR INSERT, UPDATE AS
--if there were no rows affected, just bailout.
IF @@ROWCOUNT = 0
RETURN
--if the column xyzId was explicitly mentioned in the INSERT/UPDATE statement
IF UPDATE(xyzID)
BEGIN
--insert a row in an audit table that INNER joins to another table.
--important because for each row where INSERTED.LTrID is null, no rows will be inserted/audited.
INSERT INTO EFGH
( ID, BalanceID, SID, PTrID,
StatusTrID, LoanDate, CreateTime )
SELECT lab.ID, i.BalanceID, i.RID, i.BID,
i.statusTrID, i.StatusDateTime, GETDATE()
FROM inserted i INNER JOIN
Loan lab
ON lab.LtrID = i.LTrID
ORDER BY i.LTrID ASC
END
Lowell
March 7, 2012 at 12:07 pm
I am still not so clear with your explanation. Can you please, explain which statement executes first in a step by step method. Thank you
March 7, 2012 at 12:19 pm
DBA_SQL (3/7/2012)
I am still not so clear with your explanation. Can you please, explain which statement executes first in a step by step method. Thank you
Lowell did a good job of commenting the trigger. If you are still unable to understand that you should probably start at the beginning. http://msdn.microsoft.com/en-us/library/ms189799.aspx
If there are specific lines you don't understand please ask, but nobody wants take your code and explain it line by line for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 12:19 pm
The Trigger fires AFTER any insert or update to table XYZ. The trigger code executes in order it is written in. So your example the order would be:
1. IF @@ROWCOUNT = 0 which checks to see if any rows were actually affected by the parent statement.
2. IF 1 is true then the RETURN executes. RETURN ends the trigger.
3. IF 1 is false, then the IF UPDATE(column_name) is evaluated.
4. If 3 is true then the inserted or updated row is inserted into EFGH, if there is a matching row in Loan.
5. If 3 is false then nothing happens and the trigger exits.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 7, 2012 at 12:48 pm
Excellent. Thanks for clear explanation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply