Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Before Insert or Update Trigger Expand / Collapse
Author
Message
Posted Saturday, January 8, 2011 6:30 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:36 PM
Points: 401, Visits: 928
Comments posted to this topic are about the item Before Insert or Update Trigger
Post #1044930
Posted Sunday, January 9, 2011 6:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1044973
Posted Monday, January 10, 2011 1:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:13 AM
Points: 995, Visits: 981
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" which is not possible.
Anyway nice exercise, thanks.

Regards,
Iulian
Post #1045146
Posted Monday, January 10, 2011 3:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
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.



_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1045187
Posted Monday, January 10, 2011 3:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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?
Post #1045194
Posted Monday, January 10, 2011 3:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1045201
Posted Monday, January 10, 2011 4:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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?
Post #1045204
Posted Monday, January 10, 2011 5:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1045224
Posted Monday, January 10, 2011 5:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 873, Visits: 872
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
Post #1045241
Posted Monday, January 10, 2011 7:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #1045272
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse