January 10, 2011 at 1:23 pm
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.
January 10, 2011 at 1:48 pm
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 | AFTERAFTER 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.
January 10, 2011 at 3:30 pm
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:
January 10, 2011 at 4:25 pm
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:
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 10, 2011 at 5:32 pm
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 triggersNo 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.
January 10, 2011 at 6:14 pm
Hugo,
I totally agree. A trigger is really nothing more than an event attached to a table. A before trigger would be much the same thing as a Form_PreLoad event on a Windows form or a Page_PreInit event for a webpage. I have wished a for a before trigger on many occasions over the years.
_______________________________________________________________
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 10:20 pm
Nice question
Thanks
January 18, 2011 at 5:59 am
Nice one. I thought it right you can't update logical table.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply