SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Before Insert or Update Trigger


Before Insert or Update Trigger

Author
Message
JayK
JayK
SSC Eights!
SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)

Group: General Forum Members
Points: 811 Visits: 1133
Comments posted to this topic are about the item Before Insert or Update Trigger
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62390 Visits: 13298
A nice and easy one.
Thanks!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 1248
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
DugyC
DugyC
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1694 Visits: 779
Good question, but another lost point for me Sad

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.

;-)

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2238 Visits: 1300
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?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62390 Visits: 13298
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2238 Visits: 1300
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?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62390 Visits: 13298
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
ronmoses
ronmoses
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1684 Visits: 1011
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. :-D

Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

mtassin
mtassin
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7254 Visits: 72521
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.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search