Dear SQLers:

CREATE TRIGGER ... {FOR | AFTER | INSTEAD OF} ...
Do FOR and AFTER mean the same thing here? In particular, I am
wondering whether a FOR trigger executes before or after the
underlying data is modified. My text says before on one page and
after on the next, and MSDN is not clear either.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
FOR and AFTER are indeeed equivalent. Originally FOR was the only keyword.
AFTER was added when they introduced INSTEAD OF.

Notice that there are no BEFORE triggers in SQL Server, only INSTEAD OF.
(In a real BEFORE trigger, you don't need to redo the command.)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Wed, 2 Mar 2011 07:51:01 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Thank you. And what about when the underlying data gets
modified?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
An INSTEAD OF triggers fires instead of the INSERT/UPDATE/DELETE/MERGE
statment that fired the trigger, and you need to resubmit the statement
if you want to carry out the modification.

FOR and AFTER triggers fires after the modification has taken place, and
constraints have been validated. By default, if there is an error in the
trigger, this rolls back the entire statement, even if there was no explicit
transaction.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Thu, 3 Mar 2011 02:27:37 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

If I understand you correctly, then this covers my main concern:
atomicity of the whole operation.

Thank you.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Yes, that is what you get.

If you for some reason don't want the atomicity, there are ways to cheat.
And, yes, I've seen people to ask for that.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Thu, 3 Mar 2011 15:22:10 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Good. Thank you.

Do they talk about their need to be flexible? Even brag about it
a bit?

I am near the end of my beginner's text. I keep hearing about
isolation levels, expect the cheating involves them, and rather doubt
my text covers it.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
I don't knwo exactly what they are up to. But it seems in some cases
they are poking in third-party applications, and don't want to disrupt
the flow, since they are doing something their vendor does not support.

I have also seen things about this in conjunction with replication. You
may have a trigger on the subscriber for whatever reason, but you don't
want this trigger to stop the entire replication going.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx