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
OCTom
OCTom
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3173 Visits: 4152
It seems logical that you can't update a record that isn't physically written, yet.

Thanks for the question.
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1602 Visits: 1046
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

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

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14718 Visits: 12238
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

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33536 Visits: 18560
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

Chris Harshman
Chris Harshman
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5112 Visits: 4010
In my previous life as an Oracle DBA / Developer, I think I understand this question better than people with just a SQL Server background. Other database systems have the concept of a "BEFORE" trigger that occurrs before the actual statement being executed applies changes to the table. SQL Server only has AFTER triggers

Other database systems also allow row level triggers instead of just statement level triggers like SQL Server does, and a row level trigger typically gives you OLD and NEW context variables for each column in the table the trigger is on. Using those context variables in a BEFORE trigger you could actually change the values of data using the trigger. A common use of this would be in a table that has audit columns in it such as LastModifiedDateTime. Setting the NEW context variable for LastModifiedDateTime in a BEFORE trigger would keep the value the way you want it but with only 1 modification of the row in the database. A table with a SQL Server AFTER trigger will actually cause the row to be modified twice, and in SSMS you actually see the message (1 row(s) affected) twice.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4898 Visits: 72519
Chris Harshman (1/10/2011)
In my previous life as an Oracle DBA / Developer, I think I understand this question better than people with just a SQL Server background. Other database systems have the concept of a "BEFORE" trigger that occurrs before the actual statement being executed applies changes to the table. SQL Server only has AFTER triggers


No SQL Server has AFTER, FOR and INSTEAD OF triggers. Instead of is your BEFORE trigger which is a silly concept with the term BEFORE. Before I insert to a table or update a table, I haven't done anything. INSTEAD of makes more sense. INSTEAD of INSERTING or UPDATING a table, do xyz, which likely includes an INSERT or an UPDATE statement.

Books Online see "CREATE TRIGGER"

FOR | AFTER
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

AFTER is the default when FOR is the only keyword specified.

AFTER triggers cannot be defined on views.

INSTEAD OF
Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.





--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
JayK
JayK
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 1133
Just arrived in work and surprised by the reaction to this one! Apologies if the wording was confusing to some. I tried to make the question has clear as possible but sometimes what can make sense in your head doesnt make sense to everyone. Ermm
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8875 Visits: 7660
Not all of us that got it wrong misunderstood the question. Some of us learned/were reminded of something today... like why I always seem to use INSTEAD OF triggers.

Blush


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11330 Visits: 12006
mtassin (1/10/2011)
Chris Harshman (1/10/2011)
In my previous life as an Oracle DBA / Developer, I think I understand this question better than people with just a SQL Server background. Other database systems have the concept of a "BEFORE" trigger that occurrs before the actual statement being executed applies changes to the table. SQL Server only has AFTER triggers


No SQL Server has AFTER, FOR and INSTEAD OF triggers. Instead of is your BEFORE trigger which is a silly concept with the term BEFORE. Before I insert to a table or update a table, I haven't done anything. INSTEAD of makes more sense. INSTEAD of INSERTING or UPDATING a table, do xyz, which likely includes an INSERT or an UPDATE statement.

The term BEFORE trigger is, in my opinion, not silly. ANd it is defintely not the same as an INSTEAD OF trigger.

A BEFORE trigger (which does not exist in SQL Server, but is implemented in severl other DBMS's) will fire after the rows to be updated (or inserted or deleted) are determined, but before the modification is actually carried out. In a BEFORE trigger, the data in the inserted pseudo-table can be changed. After the BEFORE trigger has executed, the database will carry out the changes that were determined before the trigger was fired - but it will also respect modification made by the trigger to the changed data.

An INSTEAD OF trigger also fires after determining the rows to be affected and before actually affecting them, but this type of trigger runs (as the name implies) instead of actually changing the data. If the INSTEAD OF trigger does nothing, then the changes will just be silently ignored.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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