See in-line:

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


[quoted text, click to view]

No but there are after and instead of triggers.

[quoted text, click to view]

If you can't operte in a SET based fashion you need to use a cursor.


[quoted text, click to view]

You use the Inserted and deleted tables. See these links for some details on
triggers in SS.
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
http://www.sql-server-performance.com/articles/dev/triggers_2000_p1.aspx
http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/
http://sqlserver2000.databases.aspfaq.com/how-do-i-audit-changes-to-sql-server-data.html

Ok, I'm new to MSSQL but have been using Sybase forever :)

I'm finding that moving my knowledge of ASA Triggers to MSSQL 2005 is
useless ... maybe ... I have been doing some reading but want to make sure.

Here are some questions:

1) Can I execute a trigger BEFORE a record is insert/delete/update? my guess
is that no ... but want to make sure.

2) How to I execute a trigger on the row that was just process? ASA uses
"for each row"

3) How do I refer to the old version of the record and the new one? here is
a ASA sample ...
ALTER TRIGGER "A:CommACT_I"."A:CommACT_I" after insert order 1 on
MRC.CommAct
referencing old as old_CommACT new as new_CommACT
for each row
begin
code goes here
end

Thanks ...

[quoted text, click to view]

Lamont Cranston:
"Who knows what evil lurks in the hearts of men? The Shadow knows!"

Steve Dassin:
"Who knows what evil lurks behind operating in a SET fashion....!"

Taken from:
http://beyondsql.blogspot.com/2007/10/sql-undefined-trigger.html
with permission from the author.

Ask the question: does Sql Server have a row level trigger.
Answer: it depends. From the point of view of application
development how in the world can the answer possibly be
ambiguous? Well lets back up and look at the Update statement.
From Sql Server 2005 bol:
Using UPDATE with the FROM Clause
'The results of an UPDATE statement are undefined if the
statement includes a FROM clause that is not specified in
such a way that only one value is available for each column
occurrence that is updated, that is if the UPDATE statement
is not deterministic'.
Ok what is really the rational for even keeping this
'proprietary' syntax? Since it is inherently non-deterministic
why even offer it to developers most of whom do not understand
but the simplest of sql. After all Sql Server is explicitly
relinquishing responsibility for the integrity of the Update and
making the user responsible. Shouldn't the idea of a database be
the other way around? Ok now lets move to the trigger. From bol
under the topic: Multirow Considerations for DML Triggers:
'When you write the code for a DML trigger, consider that the
statement that causes the trigger to fire can be a single statement
that affects multiple rows of data, instead of a single row.'
Does this sound familiar? Of course it does. The same non-determinism
of the FROM clause in Update now reappears in the insert table
of the trigger. But of course it is not presented as a 'trigger
is inherently undefined'. No, now the undefined nature of the
trigger is called 'a Multirow consideration'. And again the server
reliquishes responsibility of integrity and puts it in the
hands of the user. Well lets get real. There is no such thing as
a multirow trigger. From an applications standpoint the largest
scope of a trigger is a row. The so called multirow trigger is an
example of what happens when users don't scrutinize what nonsense
is thrown their way. Instead of getting rid of a stupid idea
in Update using a FROM it was instead extended to a trigger.
It was simply an easy and expedient thing to do. But it was
shameful and lazy too. Lack of integrity is based on lack of
character. But it is equally disappointing that more users do
not complain and demand the integrity that a database should offer.
Sql Server can do much, much better. But, like E.T., users must
phone home.

www.beyondsql.blogspot.com