statement level vs. row level triggers

[Allan Hart]

Hi..

I'd very much appreciate it if someone would tell me how to translate
a statement level trigger written in Oracle to its equivalent (if there is
one)
in MS SQL Server. Ditto for a row level trigger.

If this is an old topic, I apologize. I'm very much a newbie to SQL Server.

Regards,
Allan M. Hart

[David Portas]

In SQLServer, triggers are invoked once per statement. There are no
"row-level" triggers, as such. However, because you have access within the
trigger to the INSERTED and DELETED virtual tables (which contain the
changed rows) it should be possible to implement any logic from your Oracle
row-level triggers using a SQLServer trigger.

The basic syntax for an AFTER trigger is:

CREATE TRIGGER trg_SomeTable ON SomeTable
FOR UPDATE
AS
...

See Books Online for details.

--
David Portas
------------
Please reply only to the newsgroup
--

[Daniel Morgan]

Allan Hart wrote:

Hi..

I'd very much appreciate it if someone would tell me how to translate
a statement level trigger written in Oracle to its equivalent (if there is
one)
in MS SQL Server. Ditto for a row level trigger.

If this is an old topic, I apologize. I'm very much a newbie to SQL Server.

Regards,
Allan M. Hart

There is also, in SQL Server no equivalent to a BEFORE trigger, DDL
triggers, system triggers, synonyms, and packages. Trying to move an
application built in Oracle to SQL Server is not an easy task unless the
application is very simplistic in its design. A complete rewrite is
often required. Also keep in mind that SQL Server does not have
multiversioning so reads will block writes, writes will block reads and
transactions are not atomic so if a trigger fires you will need to
specifically roll back its actions. Be sure to invest a lot of time in
learning about the many other differences so that your efforts are
successful.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)