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 Monday, January 10, 2011 7:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 2,581, Visits: 3,883
It seems logical that you can't update a record that isn't physically written, yet.

Thanks for the question.
Post #1045311
Posted Monday, January 10, 2011 8:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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.
Post #1045340
Posted Monday, January 10, 2011 8:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1045369
Posted Monday, January 10, 2011 1:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
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
Post #1045530
Posted Monday, January 10, 2011 1:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 17,729, Visits: 15,594
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1045541
Posted Monday, January 10, 2011 1:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 1,897, Visits: 2,046
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.
Post #1045548
Posted Monday, January 10, 2011 1:48 PM


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
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
Post #1045570
Posted Monday, January 10, 2011 3: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
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.
Post #1045610
Posted Monday, January 10, 2011 4:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,384, Visits: 7,458
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.




- 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
Post #1045627
Posted Monday, January 10, 2011 5:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 PM
Points: 6,021, Visits: 8,290
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
Post #1045640
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse