January 8, 2011 at 6:30 pm
Comments posted to this topic are about the item Before Insert or Update Trigger
January 9, 2011 at 6:13 am
A nice and easy one.
Thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 10, 2011 at 1:19 am
I missed this one but nice question thanks.
I was thinking that the row can be updated since on insert operation with trigger on it:
first inserts the row into the table, then the row is added into the INSERTED table, then the trigger actions are executed and finally commit or rollback.
But I missed the "updating the row in the INSERTED table" :angry: which is not possible.
Anyway nice exercise, thanks.
Regards,
Iulian
January 10, 2011 at 3:10 am
Good question, but another lost point for me 🙁
Didn't read the question properly and misunderstood the "INSERTED table" reference. Once I understood that, the answer was easy to find. Bummer.
Question was worded well enough, I was just stupid.
😉
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
January 10, 2011 at 3:29 am
Got this one wrong because I didn't understand the wording of the question.
Is it possible, using a trigger, to update a data row before commiting the insert to a table by first updating the row in the INSERTED table ?
What row in the Inserted table? It didn't specify the one that was just inserted. Also, what kind of trigger? Can't you use an update trigger that would update a row before any inserts?
January 10, 2011 at 3:50 am
cengland0 (1/10/2011)
Got this one wrong because I didn't understand the wording of the question.Is it possible, using a trigger, to update a data row before commiting the insert to a table by first updating the row in the INSERTED table ?
What row in the Inserted table? It didn't specify the one that was just inserted. Also, what kind of trigger? Can't you use an update trigger that would update a row before any inserts?
To quote BOL:
DML triggers use the deleted and inserted logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action
So the INSERTED table is the temporary table maintained by the system.
What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 10, 2011 at 4:08 am
Koen (da-zero) (1/10/2011)
What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.
I understand that you cannot update data in the TEMPORARY INSERTED table. I thought that question asked about Updating any row in the table was that that you inserted the record in -- not the temporary one. I was confused on the definition of "INSERTED table" thinking that could mean the table you just inserted the record in. Understand the issue now?
January 10, 2011 at 5:25 am
cengland0 (1/10/2011)
Koen (da-zero) (1/10/2011)
What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.I understand that you cannot update data in the TEMPORARY INSERTED table. I thought that question asked about Updating any row in the table was that that you inserted the record in -- not the temporary one. I was confused on the definition of "INSERTED table" thinking that could mean the table you just inserted the record in. Understand the issue now?
I surely understand the issue. I had to read the question a few times in order to get it right.
The thing that finally made the trick for me was that INSERTED was in upper case, so I thought it had to be the special temp table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 10, 2011 at 5:52 am
I understood the question, and I thought it was a good one. I had honestly never considered whether or not the INSERTED table could be updated. I thought it would be pretty cool if it could be, so I answered Yes. Oh well, that's what wishful thinking gets me. 😀
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
January 10, 2011 at 7:01 am
Koen (da-zero) (1/10/2011)
cengland0 (1/10/2011)
Koen (da-zero) (1/10/2011)
What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.I understand that you cannot update data in the TEMPORARY INSERTED table. I thought that question asked about Updating any row in the table was that that you inserted the record in -- not the temporary one. I was confused on the definition of "INSERTED table" thinking that could mean the table you just inserted the record in. Understand the issue now?
I surely understand the issue. I had to read the question a few times in order to get it right.
The thing that finally made the trick for me was that INSERTED was in upper case, so I thought it had to be the special temp table.
Yes and I read it as can you manipulate the inserted data. i.e. if I use an INSTEAD of INSERT or INSTEAD of UPDATE trigger and do something like
CREATE TRIGGER T1 ON Table1
INSTEAD OF INSERT
AS
BEGIN
INSERT Table1(columns)
SELECT
some values, some with formulae
FROM
INSERTED
END
In that case you can manipulate the INSERTED or DELETED data, but yes you can't actually manipulate the data in INSERTED or DELETED.
No biggie, though the only take away I got from this was to learn to read the questions more carefully and try to make sure I'm thinking like the question writer better.
January 10, 2011 at 7:47 am
It seems logical that you can't update a record that isn't physically written, yet.
Thanks for the question.
January 10, 2011 at 8:14 am
Nice question. I also did not quite understand if the question meant could this be done with a trigger, or could you actually update the inserted table to change the inserted data.
January 10, 2011 at 8:44 am
Nice straight forward question.
It seems that once again there are people who will complain about the wording of the question no matter how straight forward it is. For those of you who complain about the wording. Stop splitting hairs, answer the question in the very clear and obvious context it was intended. Then write your own "perfect" question that will be 100% clear and unambiguous to every person on the planet regardless of language or cultural differences.
_______________________________________________________________
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/
January 10, 2011 at 1:01 pm
Koen (da-zero) (1/9/2011)
A nice and easy one.Thanks!
I though so too when I read it; and then saw that more than half the people so far had got it wrong, and found myself seriously wondering how they could get this one wrong
Sean Lange (1/10/2011)
Nice straight forward question.It seems that once again there are people who will complain about the wording of the question no matter how straight forward it is.
That certainly appears to be true
For those of you who complain about the wording. Stop splitting hairs, answer the question in the very clear and obvious context it was intended. Then write your own "perfect" question that will be 100% clear and unambiguous to every person on the planet regardless of language or cultural differences.
My third QoTD is due tomorrow. I expect to be told it's unclear/ambiguous, although I don't think it is. It just goes with writing questions, I guess. 😉
Edit: and I have to point out that what is and what isn't splitting hairs isn't something on which everyone will agree. Here I agree with you. In the context of a trigger "the inserted table" is a thoroughly well-defined and unambiguous term - or more accuratelu you and I believe that, while others clearly don't.
Tom
January 10, 2011 at 1:15 pm
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply